PostgreSQL 用户管理

PostgreSQL使用角色的概念管理数据库访问权限。角色是一系列相关权限的集合。为了管理方便，通常把一系列相关的数据库权限赋给一个角色，如果哪个用户需要这些权限，就把角色赋给相应的用户。可以简单理解为用户组和用户的关系。在PostgreSQL中，角色与用户是没有区别的，一个用户也是角色。

用户和角色在整个数据库实例中是全局的，在同一个实例中的不同数据库中，看到的用户都是相同的。

安装初始化的时候有一个预定义的超级用户，这个用户的名称与初始化该数据库的操作系统用户名相同。一般都叫“postgres”。

创建用户

# 创建角色
CREATE ROLE name [ [WITH] option [...] ]

# 创建用户
CREATE USER name [ [WITH] option [...] ]

# "CREATE USER" 、 "CREATE ROLE"，这两个命令几乎是完全相同的，唯一的区别是"CREATE USER" 命令创建的用户默认带有LOGIN属性，而"CREATE ROLE" 命令创建的用户默认不带LOGIN属性

option选项

• SUPERUSER |NOSUPERUSER：创建出来的用户是否为超级用户
• CREATEDB |NOCREATEDB：指定创建出来的用户是否有创建数据库的权限
• CREATEROLE |NOCREATEROLE：指定创建出来的用户是否有创建其他角色权限
• CREATEUSER |NOCREATEUSER：指定创建出来的用户是否有创建其它用户的权限
• INHERIT | NOINHERIT：创建的用户拥有某个或几个角色的权限
• LOGIN | NOLOGIN：创建出来的用户是否有连接数据库的权限
• CONNECTION LIMIT connlimit：用户可以使用的并发连接的数量，默认为 “-1”，表示没有限制
• [ENCRYPTED | UNENCRYPTED] PASSWORD ‘password’：存储的用户口令是否加密
• VALID UNTIL ‘timestamp’：密码失效时间，不指定的话永久有效
• IN ROLE role_name […]：指定用户成为哪些角色的成员
• IN GROUP role_name […]：等同于IN ROLE rome_name
• ROLE role_name […]：role_name 将成为这个新建的角色的成员
• ADMIN role_name […]：role_name 将有这个新建角色 WITH ADMIN OPTION权限
• USER role_name：与ROLE相同
• SYSID uid：用于SQL兼容

# 创建用户并设置密码
postgres=# create user test with password 'test';
CREATE ROLE

# 创建用户、设置密码，2023年前有效
postgres=# create user admin with password 'admin' valid until '2023-01-01';
CREATE ROLE

查看用户

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 admin     | Password valid until 2023-01-01 00:00:00+08                | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test      |                                                            | {}

postgres=# \dg
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 admin     | Password valid until 2023-01-01 00:00:00+08                | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test      |                                                            | {}

查看用户权限

-- 查看指定用户的系统权限
-- 可以从pg_roles系统表查询当前数据库服务器中所有角色

postgres=# select * from pg_roles where rolname='admin';
 rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword |     rolvaliduntil      | rolbypassrls | rolconfig |  oid
---------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+------------------------+--------------+-----------+-------
 admin   | f        | t          | f             | f           | t           | f              |           -1 | ********    | 2023-01-01 00:00:00+08 | f            |           | 16385
(1 row)

-- 查看指定用户的表权限

postgres=# select * from information_schema.table_privileges where grantee='admin';
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
(0 rows)

postgres=# select * from information_schema.table_privileges where grantee='user1';
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
(0 rows)

-- 查看用户的USAGE权限

postgres=# select * from information_schema.usage_privileges where grantee='user1';
 grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable
---------+---------+----------------+---------------+-------------+-------------+----------------+--------------
(0 rows)

删除用户

postgres=# drop user admin;
DROP ROLE

修改用户密码

postgres=# alter user user1 with password 'testuser';
ALTER ROLE