PostgreSQL数据库用户权限管理ACL访问控制示例

ACL 权限缩写

访问权限摘要

场景1：用户u2对u1所有的u1db数据库有connect、create、delete、update表权限

pg数据库对于新建的用户默认有public权限。也就是用户对已有数据库和表有SELECT，UPDATE,DELETE,CONNECT,USAGE权限。

新建用户u1和u2，u1有createdb创建数据库权限。

postgres=# create user u1 password '1qaz@WSX' valid until '2022-11-10';
CREATE ROLE
postgres=# create user u2 password '1qaz@WSX' valid until '2022-11-10';
CREATE ROLE

postgres=# alter user u1 createdb login;
ALTER ROLE
postgres=# \dg
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 appuser   | Create DB                                                 +| {}
           | Password valid until 2023-03-01 00:00:00+08                | 
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 readonly  |                                                            | {}
 u1        | Create DB                                                 +| {}
           | Password valid until 2022-11-10 00:00:00+08                | 
 u2        | Password valid until 2022-11-10 00:00:00+08                | {
[pg14@cdh01 ~]$ psql -Uu1 -p5666 postgres
Password for user u1: 
psql (14.5)
Type "help" for help.

postgres=> create database u1db;
CREATE DATABASE
postgres=> \l+
                                                                     List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |   Size    | Tablespace |                Description                 
-----------+----------+----------+-------------+-------------+-----------------------+-----------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8633 kB   | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8385 kB   | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |           |            | 
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8537 kB   | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |           |            | 
 u1db      | u1       | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8537 kB   | pg_default | 
(5 rows)

u2可以登录u1db数据库 并且有connect、create、delete、update表权限。因为默认u1db数据库有public权限。

[pg14@cdh01 ~]$ psql -Uu2 -p5666 u1db
Password for user u2: 
psql (14.5)
Type "help" for help.

u1db=> create table t2(id int);
CREATE TABLE

场景2：u1用户回收u1db数据库PUBLIC权限，u2用户不能连接u1db数据库

u1用户回收u1db的connect权限

[pg14@cdh01 ~]$ psql -Uu1 -p5666 u1db
Password for user u1: 
psql (14.5)
Type "help" for help.

u1db=> revoke CONNECT on DATABASE u1db from PUBLIC ;
REVOKE

u2用户登录u1db失败

[pg14@cdh01 ~]$ psql -Uu2 -p5666 u1db
Password for user u2: 
psql: error: connection to server on socket "/tmp/.s.PGSQL.5666" failed: FATAL:  permission denied for database "u1db"
DETAIL:  User does not have CONNECT privilege.

u1用户授权u1db数据库connect权限给u2用户，u2用户可以登录u1db，并可以在u1db数据库public模式下创建表。

[pg14@cdh01 ~]$ psql -Uu1 -p5666 u1db
Password for user u1: 
psql (14.5)
Type "help" for help.

u1db=> grant CONNECT on DATABASE u1db to u2;

[pg14@cdh01 ~]$ psql -Uu2 -p5666 u1db
Password for user u2: 
psql (14.5)
Type "help" for help.

u1db=> u1db=> create table t5(id int);
CREATE TABLE

场景3：u1用户新建u1模式，对u2用户授权usage访问u1模式。

u1用户新建u1模式，默认u2无权限访问u1模式及模式里的表。

[pg14@cdh01 ~]$ psql -Uu1 -p5666 u1db
Password for user u1: 
psql (14.5)
Type "help" for help.

u1db=> create schema u1;
CREATE SCHEMA
u1db=> create table u1.t1(id int);
CREATE TABLE
u1db=> \dp t1
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies 
--------+------+-------+-------------------+-------------------+----------
 u1     | t1   | table |                   |                   | 
(1 row)

[pg14@cdh01 ~]$ psql -Uu2 -p5666 u1db
Password for user u2: 
psql (14.5)
Type "help" for help.

u1db=> select * from u1.t1 ;
ERROR:  permission denied for schema u1
LINE 1: select * from u1.t1 ;

授予u2用户访问u1模式时，注意需要先对u2用户授权usage访问u1模式，否则报权限不足。

[pg14@cdh01 ~]$ psql -Uu1 -p5666 u1db
Password for user u1: 
psql (14.5)
Type "help" for help.

u1db=> grant SELECT on ALL tables in schema u1 to u2;
GRANT

[pg14@cdh01 ~]$ psql -Uu2 -p5666 u1db
Password for user u2: 
psql (14.5)
Type "help" for help.

u1db=> select * from u1.t1 ;
ERROR:  permission denied for schema u1
LINE 1: select * from u1.t1 ;

正确操作方式为：

[pg14@cdh01 ~]$ psql -Uu1 -p5666 u1db
Password for user u1: 
psql (14.5)
Type "help" for help.
u1db=> grant USAGE ON schema u1 to u2;
GRANT
u1db=> grant SELECT on ALL tables in schema u1 to u2;
GRANT
u1db=> \dp u1.t1 
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies 
--------+------+-------+-------------------+-------------------+----------
 u1     | t1   | table | u1=arwdDxt/u1    +|                   | 
        |      |       | u2=r/u1           |  

[pg14@cdh01 ~]$ psql -Uu2 -p5666 u1db
Password for user u2: 
psql (14.5)
Type "help" for help.

u1db=> select * from u1.t1 ;
 id 
----
(0 rows)

对于后续u1用户在u1模式下新建的表u2用户无权限访问。如果想对该schema下新建的表也获得权限，需要对该schema的owner授权给用户。

[pg14@cdh01 ~]$ psql -Uu1 -p5666 u1db
Password for user u1: 
psql (14.5)
Type "help" for help.
 u1db=> alter default privileges for ROLE u1 in schema u1 grant select on tables to u2;
ALTER DEFAULT PRIVILEGES
u1db=> create table u1.t2(id int);
CREATE TABLE

[pg14@cdh01 ~]$ psql -Uu2 -p5666 u1db
Password for user u2: 
psql (14.5)
Type "help" for help.

u1db=> select * from u1.t2 ;
 id 
----
(0 rows)

场景4：对于u1模式其它用户创建的表，授权u2权限访问。

对于u1模式其它用户创建的表，u2无权限访问。

[pg14@cdh01 ~]$ psql -Upostgres -p5666 u1db
Password for user postgres: 
psql (14.5)
Type "help" for help.
u1db=# set search_path ='u1';
SET
u1db=# \dt
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 u1     | t1   | table | u1
 u1     | t2   | table | u1
(2 rows)
u1db=# create table u1.pg_t(id int);
CREATE TABLE
u1db=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 u1     | pg_t | table | postgres
 u1     | t1   | table | u1
 u1     | t2   | table | u1
 u1db=# \dp
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies 
--------+------+-------+-------------------+-------------------+----------
 u1     | pg_t | table |                   |                   | 
 u1     | t1   | table | u1=arwdDxt/u1    +|                   | 
        |      |       | u2=r/u1           |                   | 
 u1     | t2   | table | u1=arwdDxt/u1    +|                   | 
        |      |       | u2=r/u1           |                   | 

[pg14@cdh01 ~]$ psql -Uu2 -p5666 u1db
Password for user u2: 
psql (14.5)
Type "help" for help.
u1db=> select * from u1.pg_t ;
ERROR:  permission denied for table pg_t

如果u2用户需要访问u1模式下其他用户创建的表，需要如下授权。

新建用户u3，授权u3用户在u1模式connect、select和create权限

[pg14@cdh01 ~]$ psql -Upostgres -p5666 u1db
Password for user postgres: 
psql (14.5)
Type "help" for help

u1db=# create user u3 password '1qaz@WSX' valid until '2022-11-11';

u1db=# grant CONNECT on DATABASE u1db to u3 ; #授权连接数据库权限
GRANT

u1db=# grant USAGE on SCHEMA u1 to u3; #授权查询权限前需要授权模式的usage权限
GRANT

u1db=# grant SELECT on ALL tables in  SCHEMA u1 to u3; #授权u3用户查询u1模式权限
GRANT

u1db=# grant CREATE on SCHEMA u1 to u3; #授权u3用户在u1模式创建表权限
GRANT

u3用户在u1模式创建表t3.

[pg14@cdh01 ~]$ psql -Uu3 -p5666 u1db
Password for user u3: 
psql (14.5)
Type "help" for help.

u1db=> set search_path ='u1';
SET
u1db=> \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 u1     | t1   | table | u1
 u1     | t2   | table | u1

u1db=> select * from u1.pg_t ;

 id 
----

(0 rows)

u1db=> create table u1.t3(id int);
CREATE TABLE
u1db=> \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 u1     | t1   | table | u1
 u1     | t2   | table | u1
 u1     | t3   | table | u3

u2访问u3用户表，提示无权限。

[pg14@cdh01 ~]$ psql -Uu2 -p5666 u1db
Password for user u2: 
psql (14.5)
Type "help" for help

u1db=> select * from u1.t3;
ERROR:  permission denied for table t3

授权u2用户访问u1模式下u3用户创建的表

[pg14@cdh01 ~]$ psql -Upostgres -p5666 u1db
Password for user postgres: 
psql (14.5)
Type "help" for help

u1db=# \dp
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies 
--------+------+-------+-------------------+-------------------+----------
 u1     | t1   | table | u1=arwdDxt/u1    +|                   | 
        |      |       | u3=r/u1           |                   | 
 u1     | t2   | table | u1=arwdDxt/u1    +|                   | 
        |      |       | u3=r/u1           |                   | 
 u1     | t3   | table | u3=arwdDxt/u3     |                   | 
(3 rows)

u1db=# grant SELECT on  ALL tables in schema u1 to u2;
GRANT
u1db=# alter default privileges for role u1,u3 in schema u1 grant select on tables to u2;
ALTER DEFAULT PRIVILEGES
u1db=# alter default privileges in schema u1 grant select on tables TO u2;
ALTER DEFAULT PRIVILEGES
u1db=# \dp
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies 
--------+------+-------+-------------------+-------------------+----------
 u1     | t1   | table | u1=arwdDxt/u1    +|                   | 
        |      |       | u3=r/u1          +|                   | 
        |      |       | u2=r/u1           |                   | 
 u1     | t2   | table | u1=arwdDxt/u1    +|                   | 
        |      |       | u3=r/u1          +|                   | 
        |      |       | u2=r/u1           |                   | 
 u1     | t3   | table | u3=arwdDxt/u3    +|                   | 
        |      |       | u2=r/u3           |                   | 
(3 rows)

[pg14@cdh01 ~]$ psql -Uu2 -p5666 u1db
Password for user u2: 
psql (14.5)
Type "help" for help.

u1db=> select * from u1.t3;

 id 
----

(0 rows)
u1db=> create table test3(id int);
CREATE TABLE

u3新建的表u2也能查

[pg14@cdh01 ~]$ psql -Uu2 -p5666 u1db
Password for user u2: 
psql (14.5)
Type "help" for help.

u1db=> select * from u1.test3 ;

 id 
----

(0 rows)

场景5：删除用户报错，需要删除用户依赖项。

注意事项：删除用户报错，需要删除用户依赖项。需要回收用户系统权限和赋权的对象权限后，才能删除用户

[pg14@cdh01 ~]$ psql -Upostgres -p5666 u1db
Password for user postgres: 
psql (14.5)
Type "help" for help
u1db=# drop user u3;
ERROR:  role "u3" cannot be dropped because some objects depend on it
DETAIL:  privileges for database u1db
privileges for schema u1
privileges for table t1
privileges for table t2
owner of table t3
owner of default privileges on new relations belonging to role u3 in schema u1
owner of table test3

u1db=# select * from information_schema.table_privileges where grantee='u3';
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
 u3      | u3      | u1db          | u1           | t3         | INSERT         | YES          | NO
 u3      | u3      | u1db          | u1           | t3         | SELECT         | YES          | YES
 u3      | u3      | u1db          | u1           | t3         | UPDATE         | YES          | NO
 u3      | u3      | u1db          | u1           | t3         | DELETE         | YES          | NO
 u3      | u3      | u1db          | u1           | t3         | TRUNCATE       | YES          | NO
 u3      | u3      | u1db          | u1           | t3         | REFERENCES     | YES          | NO
 u3      | u3      | u1db          | u1           | t3         | TRIGGER        | YES          | NO
 u3      | u3      | u1db          | u1           | test3      | INSERT         | YES          | NO
 u3      | u3      | u1db          | u1           | test3      | SELECT         | YES          | YES
 u3      | u3      | u1db          | u1           | test3      | UPDATE         | YES          | NO
 u3      | u3      | u1db          | u1           | test3      | DELETE         | YES          | NO
 u3      | u3      | u1db          | u1           | test3      | TRUNCATE       | YES          | NO
 u3      | u3      | u1db          | u1           | test3      | REFERENCES     | YES          | NO
 u3      | u3      | u1db          | u1           | test3      | TRIGGER        | YES          | NO
 u1      | u3      | u1db          | u1           | t1         | SELECT         | NO           | YES
 u1      | u3      | u1db          | u1           | t2         | SELECT         | NO           | YES
(16 rows)

u1db=# select relname,relacl from pg_class where relacl::text like '%u3%';
 relname |             relacl              
---------+---------------------------------
 t3      | {u3=arwdDxt/u3,u2=r/u3}
 test3   | {u2=r/u3,u3=arwdDxt/u3}
 t1      | {u1=arwdDxt/u1,u3=r/u1,u2=r/u1}
 t2      | {u1=arwdDxt/u1,u3=r/u1,u2=r/u1}
(4 rows)
由上可以看到u1db数据库u1模式下的表t3，test3，t1，t2依赖u3用户。

[pg14@cdh01 ~]$ psql -Uu3 -p5666 u1db
Password for user u3: 
psql (14.5)
Type "help" for help.

u1db=> drop table t3;
DROP TABLE
u1db=> drop table test3;
DROP TABLE

[pg14@cdh01 ~]$ psql -Upostgres -p5666 u1db
Password for user postgres: 
psql (14.5)
Type "help" for help
u1db=# select relname,relacl from pg_class where relacl::text like '%u3%';
 relname |             relacl              
---------+---------------------------------
 t1      | {u1=arwdDxt/u1,u3=r/u1,u2=r/u1}
 t2      | {u1=arwdDxt/u1,u3=r/u1,u2=r/u1}
(2 rows)
u1db=# revoke SELECT on ALL tables in schema u1 from u3;
REVOKE
u1db=# \dp
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies 
--------+------+-------+-------------------+-------------------+----------
 u1     | t1   | table | u1=arwdDxt/u1    +|                   | 
        |      |       | u2=r/u1           |                   | 
 u1     | t2   | table | u1=arwdDxt/u1    +|                   | 
        |      |       | u2=r/u1           |                   | 
(2 rows)
u1db=# select * from information_schema.table_privileges where grantee='u3';
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
(0 rows)
u1db=# drop user u3; #查询表对象权限已删除，删除用户还存在用户对象权限依赖
ERROR:  role "u3" cannot be dropped because some objects depend on it
DETAIL:  privileges for database u1db
privileges for schema u1
owner of default privileges on new relations belonging to role u3 in schema u1
u1db=# revoke CREATE on SCHEMA u1 from u3;
REVOKE
u1db=# revoke USAGE on SCHEMA u1 from u3;
REVOKE
u1db=# revoke CONNECT on DATABASE u1db from u3;
REVOKE
u1db=# alter default privileges for ROLE u3 in schema u1 revoke select on tables FROM u2;
ALTER DEFAULT PRIVILEGES
u1db=# drop user u3;                                                                     
DROP ROLE

场景6：用户权限转移给另外用户，然后删除用户。

[pg14@cdh01 ~]$ psql -Upostgres -p5666 u1db
Password for user postgres: 
psql (14.5)
Type "help" for help
postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres=# select schemaname,tablename,tableowner from pg_tables where tablename='t03';
 schemaname | tablename | tableowner
------------+-----------+------------
 public     | t03       | r03
postgres=# drop user r03;
ERROR:  role "r03" cannot be dropped because some objects depend on it
DETAIL:  privileges for column id of table t
owner of table t03
postgres=# REASSIGN OWNED BY r03 TO r02;
REASSIGN OWNED
postgres=# select * from pg_tables where tablename='t03';
 schemaname | tablename | tableowner
------------+-----------+------------
 public     | t03       | r02
postgres=# DROP OWNED BY r03;
DROP OWNED
postgres=# DROP ROLE r03;
DROP ROLE

REASSIGN OWNED命令可以被用来把要被删除的角色所拥有的所有对象的拥有关系转移给另一个角色。

一旦任何有价值的对象已经被转移给新的拥有者，任何由被删除角色拥有的剩余对象 就可以用DROP OWNED命令删除。

场景7：禁用用户

[pg14@cdh01 ~]$ psql -Upostgres -p5666 u1db
Password for user postgres: 
psql (14.5)
Type "help" for help

u1db=# alter user u2 nologin ; #禁止登录
ALTER ROLE
u1db=# comment on role u2 is 'locked by dba'; #标记用户被锁定  
COMMENT
u1db=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 appuser   | Create DB                                                 +| {}
           | Password valid until 2023-03-01 00:00:00+08                | 
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 readonly  |                                                            | {}
 u1        | Create DB                                                 +| {}
           | Password valid until 2022-11-10 00:00:00+08                | 
 u2        | Cannot login                                              +| {}
           | Password valid until 2022-11-10 00:00:00+08                |