PostgreSQL用户角色和权限管理

一.用户角色管理

1.创建用户角色

create user cjr LOGIN;create role cjr1 CREATEDB;create role cjr2 superuser;
create user cjr3 encrypted password '123456' valid until '2022-10-28';
create user admin  with SUPERUSER  password '123';

注：

• 属性LOGIN、SUPERUSER和CREATEROLE被视为特殊权限，它们不会像其它数据库对象的普通权限那样被继承。加了WITH ADMIN OPTION 则允许被授予的用户继续将权限授予给其他人。
• user\role区别：user拥有login登陆数据库权限的role。

2.修改用户

postgres=# alter user admin with password '1234';通过 \help alter user 查看帮助文档

3.删除用户

drop user username ;drop role rolename;DROP ROLE IF EXISTS role_name;

注意事项：

1、只用超级用户能够删除超级用户。

2、只有具有createrole权限的用户能删除非超级用户。

3、删除用户前，需要先删除依赖该用户的对象、权限等信息。

4、任何属于该组角色的对象都必须先被删除或者将对象的所有者赋予其它角色，任何赋予该组角色的权限也都必须被撤消。

5、删除组role只会删除组的role本身，组的成员并不会被删除。

二.权限管理

每个数据库对象都有一个所有者，默认情况下，所有者拥有该对象的所有权限。

在数据库中所有的权限都和角色挂钩，PostgreSQL权限分为两部分：

• “系统权限”或者数据库用户的属性
• 数据库对象上的操作权限(内置权限)

对超级用户Postgres不做权限检查，其它用户走ACL(Access ControL List)。

对于数据库对象，开始只有所有者和超级用户可以做任何操作，其它走ACL。

可通过以下方式禁止用户登录：

ALTER ROLE c WITH NOLOGIN;

INHERIT该属性使组成员拥有组的所有权限：

ALTER ROLE c NOINHERIT;

1.实例权限

主要由pg_hba.conf来控制，例如 ：

TYPE DATABASE USER ADDRESS METHODhost all all 127.0.0.1/32 trusthost all postgres 0.0.0.0/0 rejecthost all all 0.0.0.0/0 md5

以上配置的解释：

• 允许任何本地用户无密码连接任何数据库。
• 不允许Postgres用户从任何外部地址连接任何数据库。
• 允许其他任何用户从外部地址通过密码连接任何数据库。

2.库级别权限

包括允许连接数据库，允许在数据库中创建schema。

默认情况下，数据库在创建后：

• 允许public角色连接，即允许任何人连接。
• 不允许除了超级用户和owner之外的任何人在数据库中创建schema。
• 会自动创建名为public的schema，这个schema的all权限已经赋予给public角色，即允许任何人在里面创建对象。

3.shema级别权限

包括允许查看schema中的对象，允许在schema中创建对象。

默认情况下新建的schema的权限不会赋予给public角色。

除了超级用户和owner，任何人都没有权限查看schema中的对象或者在schema中新建对象。

4.赋予权限

GRANT示例:

GRANT示例:GRANT ALL ON database dbname TO rolename;  GRANT UPDATE ON tabname TO demo_role; GRANT SELECT ON ALL TABLES IN SCHEMA schemaname to rolename; GRANT ALL ON tabname TO rolename;  GRANT ROLE1 TO USER1; (注：角色的属性不会授予用户)GRANT SELECT, UPDATE, INSERT ON mytable TO admin;GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;  #列授权
特殊符号:ALL代表所访问权限，PUBLIC代表所有用户

5.查看权限

显示角色属性（包含系统权限）：

\du 或\du+ [username] 

查看系统表：

select * from pg_roles|pg_user;

查看某用户或角色的权限：

select * from information_schema.table_privileges where grantee='repuser';

显示对象的访问权限列表：

\z或\dp [tablename]

6.回收权限：

REVOKE 语法格式如下:

REVOKE permission_type ON table_name FROM user_name;

其中permission_type和table_name含义与GRANT指令中相同：

\h revoke

示例：

REVOKE ALL ON accounts FROM PUBLIC;

其含义为：对所有角色(PUBLIC)撤销在accounts对象上的所有权限(ALL)。

7.权限实验

appadmin下app1无法使用appadmin用户创建的表：

set role appadmin;后可以使用了：

8.创建用户赋权

墨天轮原文链接：https://www.modb.pro/db/150315?sjhy

关于作者

陈家睿，云和恩墨MySQL技术顾问,拥有MySQL OCP、PGCE、OBCA、SCDP证书，长期服务于电信行业。现负责公司MySQL数据库、分布式数据库运维方面的技术工作；热衷于运维故障处理、备份恢复、升级迁移、性能优化的学习与分享。