PostgreSQL 用户管理

创建role 创建新的角色

参考文档: https://blog.csdn.net/neweastsun/article/details/112407192

创建角色: 登录和创建表:

create role imdb WITH CREATEDB LOGIN PASSWORD '123';

PostgreSQL使用角色表示用户账号，并不像其他数据库使用用户概念。一般角色能登录称为登录角色，它与其他数据库系统用户的概念一样。当角色包含其他角色称为组角色。

PostgreSQL 从8.1版本开始合并用户和角色概念。

CREATE ROLE

创建角色使用CREATE ROLE语句：

CREATE ROLE role_name;

当创建了角色，它在数据库服务器(或集群)的所有数据库中都有效。下面语句创建角色bob：

CREATE ROLE bob;

可以从pg_roles系统表查询当前数据库服务器中所有角色：

SELECT rolname FROM pg_roles;

注意，以pg开头角色是系统角色。如果使用psql工具，你能使用\du命令列出当前数据所有存在角色.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 imdb      | Create DB                                                  | {}
 bob       | 无法登录                                                    | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

看到bob不能登录。为了让bob能够登录数据库服务，需要增加login属性。下面通过示例解释角色属性。

角色属性及示例

角色属性定义角色能包括的权限，LOGIN , SUPERUSER , CREATEDB , CREATEROLE , REPLICATION 等，语法如下：

CREATE ROLE name WITH option;

上面语句中WITH option是可选的，option可以是一个或多个属性，可以为LOGIN , SUPERUSER , CREATEDB , CREATEROLE , REPLICATION等。下面看几个示例。

登录角色

下面语句创建alice角色，它可以登录，包括初始密码：

CREATE ROLE alice 
LOGIN 
PASSWORD 'password123';

注意密码必须单引号内。现在使用alice角色登录数据库：

psql -U alice -W postgres

提示输入密码，你需要输入创建角色时的密码登录数据库。

超级用户角色

下面语句创建超级用户john角色，

CREATE ROLE john 
SUPERUSER 
LOGIN 
PASSWORD 'securePass1';

超级用户拥有数据库所有权限，因此只有必要时才创建。注意你必须是超级用户才能创建另一个超级用户角色。

创建数据库角色

创建角色，能够创建数据库，使用CREATEDB属性：

CREATE ROLE dba 
CREATEDB 
LOGIN 
PASSWORD 'Abcd1234';

有效期角色

可以设置角色密码在一定时间之后不再有效，可以使用下面属性：

VALID UNTIL 'timestamp'

下面是创建dev_api角色，2029年之前有效

CREATE ROLE dev_api WITH
LOGIN
PASSWORD 'securePass1'
VALID UNTIL '2030-01-01';

2030年的一秒滴答之后，dev_api的密码将不再有效。

连接限制角色

指定角色能使用的并发连接数，使用CONNECTION LIMIT 属性：

CONNECTION LIMIT connection_count

下面语句创建新的角色，最多有1000个并发连接：

CREATE ROLE api
LOGIN
PASSWORD 'securePass1'
CONNECTION LIMIT 1000;

最后在psql中查看我们创建的角色：

postgres=# \du
											 角色列表
角色名称 |                    属性                    | 成员属于
----------+--------------------------------------------+----------
alice    |                                            | {}
api      | 1000个连接                                 | {}
bob      | 无法登录                                   | {}
dba      | 建立 DB                                    | {}
dev_api  | 密码有效直至2030-01-01 00:00:00+08         | {}
postgres | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}

总结

PostgreSQL 使用角色表示用户账号，登录角色与其他数据库的用户概念一致。角色属性指定角色权限，如LOGIN 表示可以登录数据库；CREATEDB 可以创建数据库；SUPERUSER 拥有所有权限。

回收权限及删除角色

参考文档: https://www.cnblogs.com/zhangfx01/p/14367594.html#:~:text=%E8%A6%81%E5%88%A0%E9%99%A4%E4%B8%80%E4%B8%AA%E7%BB%84%E8%A7%92%E8%89%B2,%E4%B9%9F%E9%83%BD%E5%BF%85%E9%A1%BB%E8%A2%AB%E6%92%A4%E6%B6%88%E3%80%82

revoke回收权限

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 ]

`drop role <rolename>` 删除用户

--创建表并赋权限
postgres=#  create schema schema1;
CREATE SCHEMA
postgres=#  set search_path=schema1;
SET
postgres=# create table schema1.test(id int);
CREATE TABLE
postgres=# insert into schema1.test select generate_series(1,10);
INSERT 0 10
postgres=#  create role role_a  with password '123456' login;
CREATE ROLE
postgres=# grant all on database postgres to role_a;
GRANT
postgres=#  grant select on all tables in schema schema1 to role_a;
GRANT
postgres=#  grant all on schema schema1 to role_a;
GRANT

--将前面对象赋权时创建的role_a删除
postgres=# drop role role_a;
ERROR:  role "role_a" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema schema1
privileges for table test
privileges for database postgres

--删除role失败
postgres=# drop role role_a;
ERROR:  role "role_a" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema schema1
privileges for table test
privileges for database postgres

--要想删除用户必须回收所有权限
postgres=# revoke all on schema schema1 from role_a;
REVOKE
postgres=# drop role role_a;
ERROR:  role "role_a" cannot be dropped because some objects depend on it
DETAIL:  privileges for table test
privileges for database postgres

postgres=# revoke all on all tables in schema schema1 from role_a;
REVOKE
postgres=# drop role role_a;
ERROR:  role "role_a" cannot be dropped because some objects depend on it
DETAIL:  privileges for database postgres
postgres=# 

postgres=# revoke all on database postgres  from role_a;
REVOKE
postgres=# drop role role_a;
DROP ROLE

删除用户前，需要回收权限

回收权限

回收template0的连接权限

postgres=# revoke connect on database template1 from role_a;
REVOKE
postgres=# \c template1 role_a
psql (9.6.4, server 9.5.3)
You are now connected to database "template1" as user "role_a".

回收template1的连接权限并不生效，控制template1的连接，可以在pg_hba.conf配置，参考前面pg_hba.conf的配置

要删除一个组角色，执行DROP ROLE group_role命令即可。然而在删除该组角色之后，它与其成员角色之间的关系将被立即撤销(成员角色本身不会受影响)。不过需要注意的是，在删除之前，任何属于该组角色的对象都必须先被删除或者将对象的所有者赋予其它角色，与此同时，任何赋予该组角色的权限也都必须被撤消。