创建用户/角色
ORDER BY
CASE
WHEN FileType='00' THEN 1
WHEN FileType='07' THEN 2
WHEN FileType='02' THEN 3
WHEN FileType='03' THEN 4
ELSE 5
END
等价于
order by field("FileType",'00','07','02','03')
COALESCE函数等价于IFNull
CREATE USER/ROLE name [ [ WITH ] option [ ... ] ] : 关键词 USER,ROLE； name 用户或角色名；
where option can be:
SUPERUSER | NOSUPERUSER :超级权限，拥有所有权限，默认nosuperuser。
| CREATEDB | NOCREATEDB :建库权限，默认nocreatedb。
| CREATEROLE | NOCREATEROLE :建角色权限，拥有创建、修改、删除角色，默认nocreaterole。
| INHERIT | NOINHERIT :继承权限，可以把除superuser权限继承给其他用户/角色，默认inherit。
| LOGIN | NOLOGIN :登录权限，作为连接的用户，默认nologin，除非是create user（默认登录）。
| REPLICATION | NOREPLICATION :复制权限，用于物理或则逻辑复制（复制和删除slots），默认是noreplication。
| BYPASSRLS | NOBYPASSRLS :安全策略RLS权限，默认nobypassrls。
| CONNECTION LIMIT connlimit :限制用户并发数，默认-1，不限制。正常连接会受限制，后台连接和prepared事务不受限制。
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL :设置密码，密码仅用于有login属性的用户，不使用密码身份验证，则可以省略此选项。可以选择将空密码显式写为PASSWORD NULL。
加密方法由配置参数password_encryption确定，密码始终以加密方式存储在系统目录中。
| VALID UNTIL 'timestamp' :密码有效期时间，不设置则用不失效。
| IN ROLE role_name [, ...] :新角色将立即添加为新成员。
| IN GROUP role_name [, ...] :同上
| ROLE role_name [, ...] :ROLE子句列出一个或多个现有角色，这些角色自动添加为新角色的成员。 （这实际上使新角色成为“组”）。
| ADMIN role_name [, ...] :与ROLE类似，但命名角色将添加到新角色WITH ADMIN OPTION，使他们有权将此角色的成员资格授予其他人。
| USER role_name [, ...] :同上
| SYSID uid :被忽略，但是为向后兼容性而存在。
示例：
创建不需要密码登陆的用户test：
postgres=# CREATE ROLE test LOGIN;
CREATE ROLE
创建需要密码登陆的用户test1：
postgres=# CREATE USER test1 WITH PASSWORD 'test1';
CREATE ROLE
和ROLE的区别是：USER带LOGIN属性。
创建有时间限制的用户test2：
postgres=# CREATE ROLE test2 WITH LOGIN PASSWORD 'test2' VALID UNTIL '2020-06-30';
CREATE ROLE
创建有创建数据库和管理角色权限的用户admin：
postgres=# CREATE ROLE admin WITH CREATEDB CREATEROLE;
CREATE ROLE
注意：拥有创建数据库，角色的用户，也可以删除和修改这些对象。
创建具有超级权限的用户：admin
postgres=# CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD 'admin';
CREATE ROLE
创建复制账号：repl
postgres=# CREATE USER repl REPLICATION LOGIN ENCRYPTED PASSWORD 'repl';
CREATE ROLE
其他说明
创建复制用户
CREATE USER abc REPLICATION LOGIN ENCRYPTED PASSWORD '';
CREATE USER abc REPLICATION LOGIN ENCRYPTED PASSWORD 'abc';
ALTER USER work WITH ENCRYPTED password '';
创建scheme 角色
CREATE ROLE abc;
CREATE DATABASE abc WITH OWNER abc ENCODING UTF8 TEMPLATE template0;
c abc
创建schema
CREATE SCHEMA abc;
ALTER SCHEMA abc OWNER to abc;
revoke create on schema public from public;
创建用户
create user abc with ENCRYPTED password '';
GRANT abc to abc;
ALTER ROLE abc WITH abc;
##创建读写账号
CREATE ROLE abc_rw;
CREATE ROLE abc_rr;
##赋予访问数据库权限，schema权限
grant connect ON DATABASE abc to abc_rw;
GRANT USAGE ON SCHEMA abc TO abc_rw;
##赋予读写权限
grant select,insert,update,delete ON ALL TABLES IN SCHEMA abc to abc;
赋予序列权限
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA abc to abc;
赋予默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA abc GRANT select,insert,update,delete ON TABLES TO abc;
赋予序列权限
ALTER DEFAULT PRIVILEGES IN SCHEMA abc GRANT ALL PRIVILEGES ON SEQUENCES TO abc;
#用户对db要有连接权限
grant connect ON DATABASE abc to abc;
#用户要对schema usage 权限，不然要select * from schema_name.table ,不能用搜索路径
GRANT USAGE ON SCHEMA abc TO abc;
grant select ON ALL TABLES IN SCHEMA abc to abc;
ALTER DEFAULT PRIVILEGES IN SCHEMA abc GRANT select ON TABLES TO abc;
create user abc_w with ENCRYPTED password '';
create user abc_r with ENCRYPTED password '';
GRANT abc_rw to abc_w；
GRANT abc_rr to abc_r;
授权，定义访问权限
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
##单表授权：授权test账号可以访问schema为test的t1表
grant select,insert,update,delete on test.t1 to test;
##所有表授权：
grant select,insert,update,delete on all tables in schema test to test;
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##列授权，授权指定列(test schema下的t1表的name列)的更新权限给test用户
grant update (name) on test.t1 to test;
##指定列授不同权限，test schema下的t1表，查看更新name、id字段，插入name字段
grant select (name,id),update (name,id),insert(name) on test.t1 to test;
GRANT { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
##序列（自增键）属性授权，指定test schema下的seq_id_seq 给test用户
grant select,update on sequence test.seq_id_seq to test;
##序列（自增键）属性授权，给用户test授权test schema下的所有序列
grant select,update on all sequences in schema test to test;
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##连接数据库权限，授权test用户连接数据库testdb
grant connect on database test to testdb;
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
##
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##
GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]##
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##连接schema权限，授权demo访问test schema权限
grant usage on schema test to demo;
GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
where role_specification can be:
[ GROUP ] role_name
| PUBLIC
| CURRENT_USER
| SESSION_USER
GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]
##把test用户的权限授予用户demo。
grant test to demo;
权限说明：
SELECT：允许从指定表，视图或序列的任何列或列出的特定列进行SELECT。也允许使用COPY TO。在UPDATE或DELETE中引用现有列值也需要此权限。对于序列，此权限还允许使用currval函数。对于大对象，此权限允许读取对象。
INSERT：允许将新行INSERT到指定的表中。如果列出了特定列，则只能在INSERT命令中为这些列分配（因此其他列将接收默认值）。也允许COPY FROM。
UPDATE：允许更新指定表的任何列或列出的特定列，需要SELECT权限。
DELETE：允许删除指定表中的行，需要SELECT权限。
TRUNCATE：允许在指定的表上创建触发器。
REFERENCES：允许创建引用指定表或表的指定列的外键约束。
TRIGGER：允许在指定的表上创建触发器。
CREATE：对于数据库，允许在数据库中创建新的schema、table、index。
CONNECT：允许用户连接到指定的数据库。在连接启动时检查此权限。
TEMPORARY、TEMP：允许在使用指定数据库时创建临时表。
EXECUTE：允许使用指定的函数或过程以及在函数。
USAGE：对于schema，允许访问指定模式中包含的对象；对于sequence，允许使用currval和nextval函数。对于类型和域，允许在创建表，函数和其他模式对象时使用类型或域。
ALL PRIVILEGES：一次授予所有可用权限。
用户授权官方英文文档地址 https://www.postgresql.org/docs/12/sql-grant.html
用户授权官方中文文档地址 http://www.postgres.cn/docs/11/sql-grant.html
撤销权限
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##移除用户test在schema test上所有表的select权限
revoke select on all tables in schema test from test;
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##移除用户test在test schema的t1表的id列的查询权限
revoke select (id) on test.t1 from test;
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##序列
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##库
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT]
##
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT]
##
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT]
##
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE } function_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##schema权限
REVOKE [ GRANT OPTION FOR ]
{ CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##
REVOKE [ ADMIN OPTION FOR ]
role_name [, ...] FROM role_name [, ...]
[ CASCADE | RESTRICT ]
注意：任何用户对public的schema都有all的权限，为了安全可以禁止用户对public schema 的create权限。
##移除所有用户（public），superuser除外，对指定DB下的public schema的create 权限。
testdb=# revoke create on schema public from public;