PostgreSQL 权限管理（案例）

案例1

用户user2对user1所有的user1db数据库有connect、create、delete、update表权限

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

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

postgres=# create user user1 password 'user1' valid until '2023-01-03';
CREATE ROLE
postgres=# create user user2 password 'user2' valid until '2023-01-03';
CREATE ROLE
postgres=# alter user user1 createdb login;
ALTER ROLE
postgres=# \dg
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 user1     | Create DB                                                 +| {}
           | Password valid until 2023-01-03 00:00:00+08                |
 user2     | Password valid until 2023-01-03 00:00:00+08                | {}
postgres=> \q
[postgres@192 ~]$ psql -U user1 -p 5432 postgres -W
Password:
psql (14.6)
Type "help" for help.

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

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

[postgres@192 ~]$ psql -U user2 -p 5432 user1db
psql (14.6)
Type "help" for help.

user1db=> create table test2(id int);
CREATE TABLE

案例2

user1用户回收user1db数据库PUBLIC权限，user2用户不能连接user1db数据库

-- user1用户回收user1db的connect权限

[postgres@192 ~]$ psql -U user1 -p 5432 user1db
psql (14.6)
Type "help" for help.

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

user1db=> \q

-- user2用户登录user1db失败
[postgres@192 ~]$ psql -U user2 -p 5432 user1db
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  permission denied for database "user1db"
DETAIL:  User does not have CONNECT privilege.

-- user1用户授权user1db数据库connect权限给user2用户，user2用户可以登录user1db，并可以在user1db数据库public模式下创建表。

[postgres@192 ~]$ psql -U user1 -p 5432 user1db
psql (14.6)
Type "help" for help.

user1db=> grant CONNECT on DATABASE user1db to user2;
GRANT
user1db=> \q
[postgres@192 ~]$ psql -U user2 -p 5432 user1db
psql (14.6)
Type "help" for help.

user1db=> create table test5(id int);
CREATE TABLE

案例3

user1用户新建user1模式，对user2用户授权usage访问user1模式。

-- user1用户新建user1模式，默认user2无权限访问user1模式及模式里的表。

[postgres@192 ~]$  psql -U user1 -p 5432 user1db
psql (14.6)
Type "help" for help.

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

user1db=> \q

[postgres@192 ~]$ psql -U user2 -p 5432 user1db
psql (14.6)
Type "help" for help.

user1db=> select * from user1.test1;
ERROR:  permission denied for schema user1
LINE 1: select * from user1.test1;

user1db=> \q

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

[postgres@192 ~]$ psql -U user1 -p 5432 user1db
psql (14.6)
Type "help" for help.

user1db=> grant SELECT on ALL tables in schema user1 to user2;
GRANT
user1db=> \q

[postgres@192 ~]$ psql -U user2 -p 5432 user1db
psql (14.6)
Type "help" for help.

user1db=> select * from user1.tser1 ;
ERROR:  permission denied for schema user1
LINE 1: select * from user1.tser1 ;

user1db=> \q

-- 正确操作方式

[postgres@192 ~]$ psql -U user1 -p 5432 user1db
psql (14.6)
Type "help" for help.

user1db=> grant USAGE ON schema user1 to user2;
GRANT
user1db=> grant SELECT on ALL tables in schema user1 to user2;
GRANT

user1db=> \dp user1.test1
                              Access privileges
 Schema | Name  | Type  |  Access privileges  | Column privileges | Policies
--------+-------+-------+---------------------+-------------------+----------
 user1  | test1 | table | user1=arwdDxt/user1+|                   |
        |       |       | user2=r/user1       |                   |
(1 row)

user1db=> \q

[postgres@192 ~]$ psql -U user2 -p 5432 user1db
psql (14.6)
Type "help" for help.

user1db=> select * from user1.test1 ;
 id
----
(0 rows)

user1db=> \q

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

[postgres@192 ~]$ psql -U user1 -p 5432 user1db
psql (14.6)
Type "help" for help.

user1db=> alter default privileges for ROLE user1 in schema user1 grant select on tables to user2;
ALTER DEFAULT PRIVILEGES
user1db=> create table user1.test2(id int);
CREATE TABLE

user1db=> \q

[postgres@192 ~]$ psql -U user2 -p 5432 user1db
psql (14.6)
Type "help" for help.

user1db=> select * from user1.test2 ;
 id
----
(0 rows)

user1db=> \q