PostgreSQL教程（7）用户权限与角色管理

一、PostgreSQL 用户与权限介绍

· 用户：用于访问和管理 PostgreSQL 数据库中的各种对象。按照数据库使用规范，建议为每一个数据库用户分配合适的权限，避免因权限过大而产生数据泄露、丢失等问题。用户分为管理员用户和普通用户，在数据库后期使用中创建的用户通常都为普通用户；而管理用户在实例初始化时会自动完成创建，这个用户的名称与初始化该数据库的操作系统用户名相同。比如使用的是操作系统中的postgres用户进行的数据库初始化，那么数据库超级用户的名称也为postgres。

· 角色：角色是一系列权限的集合，使用角色便于对用户进行统一的权限管理。不过在PostgreSQL中，其实并没严格区分用户和角色的概念，甚至可以把二者等同看待，唯一不同的是角色在创建完成后默认没有login权限。PostgresQL在初始化时总是包含一个预定义的超级用户角色。默认情况下该角色将与初始化数据库集群的操作系统用户同名（通常为postgres）。

二、创建用户\角色选项介绍

PostgreSQL在创建用户的时候可以通过不同的选项来设置用户的默认权限：

· SUPERUSER | NOSUPERUSER：创建的用户是否为超级用户，在初始化数据库时会创建一个与执行初始化数据库的系统用户同名的超级用户

· CREATEDB | NOCREATEDB：创建的用户是否具有CREATE DATABASE的权限，默认无此权限

· CREATEROLE | NOCREATEROLE：创建出来的用户是否具有创建角色的权限，默认无此权限

· INHERIT | NOINHERIT：如果创建的用户拥有某个或某几个角色， 这时若指定INHERIT表示用户自动拥有相应角色的权限， 否则该用户没有相应角色的权限

· LOGIN | NOLOGIN：用户是否具有LOGIN权限

· REPLICATION | NOREPLICATION：用户是否具有流复制权限

· CONNECTION LIMIT：用户最大并发连接数， 默认“-1”表示没有限制

· [ENCRYPTED|UNENCRYPTED]PASSWORD 'password'：用户密码

· VALID UNTIL 'timestamp'：密码失效时间，该参数不指定的话默认永久有效。有时候账号登录不上的时候不一定是密码错，而是密码超过有效期了

· IN ROLE role_name [,...]：指定该用户成为哪些角色的成员

· ROLE role_name [,...]：成为role_name所指定的新角色成员

· ADMIN role_name [,...]：拥有新建角色的WITH ADMIN OPTION权限

#创建没有密码的用户
CREATE USER tanglu;

#创建用户的同时配置密码
CREATE USER tanglu WITH PASSWORD '123456';
 
#创建用户的同时配置账号有效时间，写--infinity代表永久有效，也可以忽略VALID UNTIL选项不写
CREATE USER tanglu WITH PASSWORD 'postgres' VALID UNTIL '2024-1-1 00:00:00';
CREATE USER tanglu WITH PASSWORD 'postgres' VALID UNTIL 'infinity';
 
#创建具有 创建数据库和管理角色权限 的用户
CREATE USER tanglu WITH PASSWORD 'postgres' CREATEDB CREATEROLE;
 
#创建具有超级权限的用户
CREATE USER tanglu WITH PASSWORD 'postgres' SUPERUSER;
 
#创建复制账号
create user repl REPLICATION PASSWORD 'repl';
 
#修改用户密码
alter user user1 password 'xxx';
 
#移除用户密码
alter user user1 password null;
 
#更改密码失效日期
alter user user4 VALID UNTIL '2024-1-1 00:00:00';
 
#让密码永久生效
alter user user2  VALID UNTIL 'infinity';
 
#修改用户系统权限，让其拥有createdb和createrole的权限
alter user user1 createdb;
 
#删除用户
drop user user1;


#创建的用户和角色都可以直接登录数据库
CREATE USER user1;
CREATE ROLE role1 LOGIN;

#创建角色role1，并且具有创建数据库和用户\角色的权限
CREATE ROLE role1 login CREATEDB CREATEROLE;
 
#将角色授权给用户
grant all on t1 to role1
GRANT role1 TO user1;
GRANT role1 TO user2;
 
#修改角色权限：
ALTER ROLE role1 SUPERUSER;
 
#删除角色
drop role role1;
#授予预定义角色给用户
GRANT pg_read_all_data TO user1;

三、PostgreSQL 权限管理

在创建用户时所指定的权限属于数据库本身权限，这部分权限在后续可以通过ALTER USER/ROLE 命令进行修改。而对于数据本身的增删改查等权限则是通过 GRANT 和 REVOKE 命令进行修改。另每个对象都有一个owner，owner默认拥有该对象的所有权限，无需再进行授权，而还有一个 plubic 代表所有用户公共权限

1、PostgreSQL 权限查看

在PostgreSQL 中，\dp 命令用于显示数据库中的对象（如表、视图、序列）的权限信息。包含每个对象的所有者（Owner）、授权用户（Grantee）、权限类型（SELECT、INSERT、UPDATE、DELETE 等）以及授予的权限级别（例如，是授予了 SELECT 权限还是授予了 ALL 权限）

=> \dp
                        Access privileges
 Schema |  Name   | Type  |   Access privileges   |   Column privileges   | Policies
--------+---------+-------+-----------------------+-----------------------+----------
 public | t1      | table | user1=arwdDxt/user1 +| name:                +|
        |         |       | =r/user1            +|        user2=r/user1 +|
        |         |       | user2=arw/user1      | age:                 +|
        |         |       |                      |         user2=w/user1 |
(1 row)

· 通过系统库查看给定表的权限，包括指定表的所有者、授权用户、授予的权限类型和权限级别等信息

postgres=# select * from information_schema.table_privileges where table_name='t1';
 grantor  | grantee  | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
----------+----------+---------------+--------------+------------+----------------+--------------+----------------
 postgres | postgres | postgres      | public       | t1         | INSERT         | YES          | NO
 postgres | postgres | postgres      | public       | t1         | SELECT         | YES          | YES

· 通过pg_user表查看用户和权限

postgres=# select usename,usecreatedb,usesuper,userepl,usebypassrls,valuntil from pg_user;
 usename  | usecreatedb | usesuper | userepl | usebypassrls | valuntil 
----------+-------------+----------+---------+--------------+----------
 postgres | t           | t        | t       | t            | 
 user1    | f           | f        | f       | f            | 
 user2    | t           | f        | f       | f            | 
(3 rows)

2、PostgreSQL授权

#授权user2用户拥有t1的DML权限
GRANT SELECT, UPDATE, INSERT,DELETE ON t1 TO user2;

#授权user2对name和age列设置了不同权限
GRANT SELECT (name), UPDATE (age) ON t1 TO user2;

三、pg_permissions权限插件

PostgreSQL的权限查看相比MySQL来说是不太好用的，权限的可视化不直观，不方便查询，而通过pg_permissions插件可以解决这些问题。pg_permissions插件可以查询用户的所有对象权限，包括库权限、表权限、视图权限、字段权限、函数权限等。

· 安装pg_permissions插件

插件地址：https://github.com/cybertec-postgresql/pg_permissions

git clone https://github.com/cybertec-postgresql/pg_permission.git
cd pg_permission  
export PATH=/usr/local/pgsql/bin:$PATH
USE_PGXS=1 make pg_config=/usr/local/pgsql/bin/pg_config
USE_PGXS=1 make install
 
#登录数据库创建pg_permissions扩展
postgres=# CREATE EXTENSION pg_permissions; 
CREATE EXTENSION

· pg_permissions插件安装好以后会创建8个视图，1张表，一个函数

这些视图可以检查当前授予的对象的权限，从名字可以看出来具体对应的权限维度。日常使用较多的就是all_permissions视图。需要注意的是超级用户不会显示在这些视图中，因为默认拥有所有权限。

#pg_permissions视图
postgres=# \dv *permissions
                List of relations
 Schema |         Name         | Type |  Owner
--------+----------------------+------+----------
 public | all_permissions      | view | postgres
 public | column_permissions   | view | postgres
 public | database_permissions | view | postgres
 public | function_permissions | view | postgres
 public | schema_permissions   | view | postgres
 public | sequence_permissions | view | postgres
 public | table_permissions    | view | postgres
 public | view_permissions     | view | postgres
(8 rows)

#pg_permissions表
postgres=# \dt *permission*
               List of relations
 Schema |       Name        | Type  |  Owner
--------+-------------------+-------+----------
 public | permission_target | table | postgres
(1 row)

数据库postgresqlpgsql