PostgreSQL：用户角色管理
创建用户/角色
创建好用户（角色）之后需要连接的话，还需要修改 2 个权限控制的配置文件（pg_hba.conf、pg_ident.conf）。并且创建用户（user）和创建角色（role）一样，唯一的区别是用户默认可以登录，而创建的角色默认不能登录。创建用户和角色的各个参数选项是一样的。
语法
CREATE USER/ROLE name [ [ WITH ] option [ ... ] ] : 关键词 USER,ROLE； name 用户或角色名； where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION | NOREPLICATION | BYPASSRLS | NOBYPASSRLS | CONNECTION LIMIT connlimit | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...] | IN GROUP role_name [, ...] | ROLE role_name [, ...] | ADMIN role_name [, ...] | USER role_name [, ...] | SYSID uid
-
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 [, ...]：与 IN ROLE 相同，是已过时的语法。
-
ROLE role_name [, ...]：ROLE 子句列出一个或多个现有角色，这些角色自动添加为新角色的成员。 （这实际上使新角色成为“组”）。
-
ADMIN role_name [, ...]：与 ROLE 类似，但命名角色将添加到新角色 WITH ADMIN OPTION，使他们有权将此角色的成员资格授予其他人。
-
USER role_name [, ...]：与 ROLE 子句相同，是已过时的语法。
-
SYSID uid：被忽略，但是为向后兼容性而存在。
示例
-
创建不需要密码登陆的用户zjy：
postgres=# CREATE ROLE zjy LOGIN; CREATE ROLE
创建该用户后，还不能直接登录。需要修改 pg_hba.conf 文件（后面会对该文件进行说明），加入：
①：本地登陆：local all all trust
②：远程登陆：host all all 192.168.163.132/32 trust
-
创建需要密码登陆的用户zjy1：
postgres=# CREATE USER zjy1 WITH PASSWORD 'zjy1'; CREATE ROLE
和ROLE的区别是：USER带LOGIN属性。也需要修改 pg_hba.conf 文件，加入：
host all all 192.168.163.132/32 md5
-
创建有时间限制的用户 zjy2：
postgres=# CREATE ROLE zjy2 WITH LOGIN PASSWORD 'zjy2' VALID UNTIL '2019-05-30'; CREATE ROLE
和 2 的处理方法一样，修改 pg_hba.conf 文件，该用户会的密码在给定的时间之后过期不可用。
-
创建有创建数据库和管理角色权限的用户 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;
修改用户属性
语法
ALTER USER role_specification [ WITH ] option [ ... ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
ALTER USER name RENAME TO new_name
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL
where role_specification can be:
role_name
| CURRENT_USER
| SESSION_USER
示例
option 选项里的用户都可以通过 alter role 进行修改
-
修改用户为超级/非超级用户
alter role caocao with superuser/nosuperuser;
-
修改用户为可/不可登陆用户
alter role caocao with nologin/login;
-
修改用户名
alter role caocao rename to youxing;
-
修改用户密码，移除密码用 NULL
alter role youxing with password 'youxing';
-
修改用户参数，该用户登陆后的以该参数为准
alter role zjy in database zjy SET geqo to 0/default;
查看用户属性
-
查看当前用户
zjy=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- admin | Superuser, Cannot login | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} zjy | | {} zjy=# select * from pg_roles; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid ----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+------- pg_signal_backend | f | t | f | f | f | f | -1 | ******** | | f | | 4200 postgres | t | t | t | t | t | t | -1 | ******** | | t | | 10 admin | t | t | f | f | f | f | -1 | ******** | | f | | 16456 pg_read_all_stats | f | t | f | f | f | f | -1 | ******** | | f | | 3375 zjy | f | t | f | f | t | f | -1 | ******** | | f | | 16729 pg_monitor | f | t | f | f | f | f | -1 | ******** | | f | | 3373 pg_read_all_settings | f | t | f | f | f | f | -1 | ******** | | f | | 3374 pg_stat_scan_tables | f | t | f | f | f | f | -1 | ******** | | f | | 3377 (8 rows) -
查看用户权限
zjy=# select * from information_schema.table_privileges where grantee='zjy'; grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy ----------+---------+---------------+--------------+------------+----------------+--------------+---------------- postgres | zjy | zjy | zjy | zjy | INSERT | NO | NO postgres | zjy | zjy | zjy | zjy | SELECT | NO | YES postgres | zjy | zjy | zjy | zjy | UPDATE | NO | NO postgres | zjy | zjy | zjy | zjy | DELETE | NO | NO postgres | zjy | zjy | zjy | zjy1 | INSERT | NO | NO postgres | zjy | zjy | zjy | zjy1 | SELECT | NO | YES postgres | zjy | zjy | zjy | zjy1 | UPDATE | NO | NO postgres | zjy | zjy | zjy | zjy1 | DELETE | NO | NO postgres | zjy | zjy | zjy | zjy2 | INSERT | NO | NO postgres | zjy | zjy | zjy | zjy2 | SELECT | NO | YES postgres | zjy | zjy | zjy | zjy2 | UPDATE | NO | NO postgres | zjy | zjy | zjy | zjy2 | DELETE | NO | NO postgres | zjy | zjy | zjy | zjy3 | INSERT | NO | NO postgres | zjy | zjy | zjy | zjy3 | SELECT | NO | YES postgres | zjy | zjy | zjy | zjy3 | UPDATE | NO | NO postgres | zjy | zjy | zjy | zjy3 | DELETE | NO | NO
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31490526/viewspace-2742181/，如需转载，请注明出处，否则将追究法律责任。
