PostgreSQL用户角色权限管理

PostgreSQL, 数据库 / 作者：

PostgreSQL使用角色来管理用户权限，角色是一系列相关权限的集合，如果哪个用户需要这些权限，就可以把该角色赋予用户，实际上在PostgreSQL内部实现中，角色和用户没有任何区别，只是逻辑上分为角色和用户，下文的描述中，角色与用户等同。

1. 角色创建与删除

创建角色：
create role myrole;

删除角色：
drop role myrole;

除了SQL语句之外，PostgreSQL还提供了包装的命令createuser和dropuser来创建和删除角色。

createuser myrole
dropuser myrole

查看当前已创建的角色：
SELECT rolname FROM pg_roles;
或者
\du

2. 角色的属性

角色可以拥有一些属性或者叫权限，比如登录数据库，需要角色拥有LOGIN属性，这类权限在创建角色的时候指定，或者通过alter role来修改。

示例：
create role myrole with login;

create role myrole with login SUPERUSER INHERIT CREATEDB CREATEROLE CONNECTION LIMIT 50 REPLICATION PASSWORD ‘123456’ VALID UNTIL ‘2021-01-01’;

修改属性：
alter role myrole nologin;

常用属性：

  • SUPERUSER/NOSUPERUSER，创建出来的用户是否为超级用户，只有超级用户才能创建超级用户
  • LOGIN/NOLOGIN，指定创建的用户是否有连接数据库的权限
  • INHERIT/NOINHERIT，指定创建的用户是否继承某些角色的权限
  • CREATEDB/NOCREATEDB，指定创建出来的用户是否有权限创建数据库
  • CREATEROLE/NOCREATEROLE，指定创建出来的用户是否有创建其他角色的权限
  • CONNECTION LIMIT connlimit，指定用户能够使用的最大并发连接数量，默认-1，表示没有限制
  • REPLICATION，指定复制权限
  • PASSWORD password，指定密码
  • VALID UNTIL ‘timestamp’，指定密码失效时间，如果不指定，永远有效
  • IN ROLE role_name，指定成为哪些角色的成员
  • ROLE role_name，role_name将成为这个新建角色的成员

角色(role)和用户(user)可以等同使用，创建用户时，默认就已经带上了LOGIN属性，而角色默认没有带任何属性。

create user myuser;

myuser默认已经带了login属性。

3. 角色的权限：

角色的权限主要是对数据库对象(表，schema，trigger等）的操作权限，与角色的属性略有不同。权限使用grant或revoke进行管理。

示例1：将所有表的增删改查权限赋予角色
grant select,insert,update,delete on all tables in schema schema_name to role_name;

示例2：将某个表的增删改查权限赋予角色
grant select,insert,update,delete on table schema_name.tb to role_name;

示例3：移除权限：
revoke delete on all tables in schema schema_name from role_name;

常用权限汇总：

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • TRUNCATE
  • REFERENCES
  • TRIGGER
  • CREATE
  • CONNECT
  • TEMPORARY
  • EXECUTE
  • USAGE
  • ALL PRIVILEGES

发表评论