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

ACL 权限缩写

权限	缩写	适用对象类型
`SELECT`	`r` (“读”)	`LARGE OBJECT` , `SEQUENCE` , `TABLE` (and table-like objects), table column
`INSERT`	`a` (“增补”)	`TABLE` , table column
`UPDATE`	`w` (“写”)	`LARGE OBJECT` , `SEQUENCE` , `TABLE` , table column
`DELETE`	`d`	`TABLE`
`TRUNCATE`	`D`	`TABLE`
`REFERENCES`	`x`	`TABLE` , table column
`TRIGGER`	`t`	`TABLE`
`CREATE`	`C`	`DATABASE` , `SCHEMA` , `TABLESPACE`
`CONNECT`	`c`	`DATABASE`
`TEMPORARY`	`T`	`DATABASE`
`EXECUTE`	`X`	`FUNCTION` , `PROCEDURE`
`USAGE`	`U`	`DOMAIN` , `FOREIGN DATA WRAPPER` , `FOREIGN SERVER` , `LANGUAGE` , `SCHEMA` , `SEQUENCE` , `TYPE`

访问权限摘要

对象类型	所有权限	默认 `PUBLIC` 权限	psql 命令
`DATABASE`	`CTc`	`Tc`	`\l`
`DOMAIN`	`U`	`U`	`\dD+`
`FUNCTION` or `PROCEDURE`	`X`	`X`	`\df+`
`FOREIGN DATA WRAPPER`	`U`	none	`\dew+`
`FOREIGN SERVER`	`U`	none	`\des+`
`LANGUAGE`	`U`	`U`	`\dL+`
`LARGE OBJECT`	`rw`	none
`SCHEMA`	`UC`	none	`\dn+`
`SEQUENCE`	`rwU`	none	`\dp`
`TABLE` (and table-like objects)	`arwdDxt`	none	`\dp`
Table column	`arwx`	none	`\dp`
`TABLESPACE`	`C`	none	`\db+`
`TYPE`	`U`	`U`	`\dT+`

场景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

场景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                |

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

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

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

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

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

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

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

场景7：禁用用户

继续阅读

#缅北#在某合网上，碰到了缅北做勒索诈骗的，用备用手机戏耍的骗子，手机里放了30多个空号，分别备注上“总经理赵XX”、“

【zookeeper】zookeeper基础知识《ACL》

postgreSQL 用户、角色、权限、数据库的简单使用用户和角色数据库授权GRANT相关参考资料

Linux系统之高级用户组和权限管理一、用户的密码策略设置二、监控系统用户登录三、用户的提权四、ACL权限配置五、文件与目录的特殊权限六、默认权限与反掩码

笔记－CCNA与网络安全第8章网络安全网络安全包括的层面网络层安全常见的安全威胁路由器上的安全

ansible利用循环创建用户

为什么同样是安卓系统，国内的就是不如国外流畅？首先，国外大部分都是原生安卓系统，功能精简，占用内存空间也少，比如米UI1

#操作系统#Android等系统的根目录下文件夹太多了，感觉有点乱（图二、图三）、对权限管理模块也很不满意。这方面还是M

什么？Zookeeper | ACL权限控制

清晰直观！12张日历网页WEB设计日历网页界面设计的特点包括直观性、交互性、响应式设计、可定制性以及互动性和动画效果。它

IntelliJ IDEA的svn如何创建用户,如何配置用户权限,配置用户组,svn怎么拒绝访问,帐号密码错误

dataGridView无法加载oracle system用户创建的表

数据库数据(SQLSERVER/ACCESS/EXCEL)导入导出转换

RabbitMQ学习-服务端学习

5·RHCSA用户权限、共享目录、附加权限

S5700的ACL访问控制列表及实验