PostgreSQL 用户管理

本文学习PostgreSQL角色，以及如何使用CREATE ROLE创建新的角色。

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

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

1. CREATE ROLE

创建角色使用CREATE ROLE语句：

CREATE ROLE role_name;

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

CREATE ROLE bob;

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

SELECT rolname FROM pg_roles;

输出：

rolname
pg_monitor
pg_read_all_settings
pg_read_all_stats
pg_stat_scan_tables
pg_signal_backend
postgres

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

postgres=# \du
                             角色列表
 角色名称 |                    属性                    | 成员属于
----------+--------------------------------------------+----------
 bob      | 无法登录                                   | {}
 postgres | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}

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

2. 角色属性及示例

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

CREATE ROLE name WITH option;

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

2.1 登录角色

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

CREATE ROLE alice 
LOGIN 
PASSWORD 'password123';

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

psql -U alice -W postgres

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

2.2 超级用户角色

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

CREATE ROLE john 
SUPERUSER 
LOGIN 
PASSWORD 'securePass1';

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

2.3 创建数据库角色

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

CREATE ROLE dba 
CREATEDB 
LOGIN 
PASSWORD 'Abcd1234';

2.4 有效期角色

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

VALID UNTIL 'timestamp'

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

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

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

2.5 连接限制角色

指定角色能使用的并发连接数，使用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 | {}

3. 总结

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