PostgreSQL中的权限: 一个有关schema、角色的问题及思考

  • 数据库杂记

    2023-08-11 06:10:00
  • 数据库开发技术
  • 原创


1.背景

一个同学在热火朝天的group里问到了这样的实际题目：

PostgreSQL是怎么把schema下所有表的增删改查权限授予给一个目标用户？

接着他还贴出了自己的实验的图：

并称执行上边的授权过过程还是失败。

粗看一眼，估计我们都认为上边的过程应该是正确的。schema的usage，然后所有的表的权限什么的也都给了。为什么会失败呢？

2.分析与实作

我们先还是使用PostgreSQL 14.8作为例子，一步步实验并尝试进行分析。

我们先看看，保留默认的public role 以及public schema下的所有权限，看看上述思路是什么效果？为了让实验跟题目接近，我们把schema名和用户名都弄成一样。建一个数据库mydb，建一个schema logmgt，并且使用用户mydb在logmgt下边创建一系列表。然后尝试把logmgt下边的所有表的相关访问权限都赋给DCS用户，对未来新建的表也如此。

2.1 保留public的role、schema

1、顺序演进

1、建库并建user: mydb
\c postgres
create database mydb;
postgres=# create user mydb with password 'test123';
CREATE ROLE

2、进入mydb, 使用superuser建schema: logmgt，并建一些表
mydb=> \c mydb postgres
You are now connected to database "mydb" as user "postgres".
mydb=# create schema logmgt;
CREATE SCHEMA
mydb=# create table logmgt.t1(id int);
CREATE TABLE
mydb=# create table logmgt.t2(id int);
CREATE TABLE
mydb=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 logmgt | postgres
 public | postgres
(2 rows)
-- 这个时候我们看到logmgt的属主是postgres。不是mydb。

3. 创建用户DCS，并授权访问试一下：
mydb=# create user "DCS" with password 'test123';
CREATE ROLE
mydb=# \c - "DCS"
You are now connected to database "mydb" as user "DCS".
mydb=> select * from logmgt.t1;
ERROR:  permission denied for schema logmgt
LINE 1: select * from logmgt.t1;    --这是没有任何授权的结果
                      ^
-- 授权看下：
mydb=> \c - postgres
You are now connected to database "mydb" as user "postgres".
grant usage on schema logmgt to "DCS";  --这个时候能列出表
-- 下边两句，基本上就把logmgt下表和序列的权限都给了DCS了。除了不能drop table (必须是owner才能)
grant all privileges on all tables in schema logmgt to "DCS";
grant all privileges on all sequences in schema logmgt to "DCS";

-- 至于这句：grant select, insert, update, delete on all tables in schema logmgt to "DCS"; 就有点重复了哈

-- 验证下已有的表：
mydb=> \c mydb "DCS"
You are now connected to database "mydb" as user "DCS".
mydb=> insert into logmgt.t2 values(1), (5);
INSERT 0 2
mydb=> select * from logmgt.t2;
 id 
----
  1
  5
(2 rows)

mydb=> create table logmgt.t0(id int);
ERROR:  permission denied for schema logmgt
LINE 1: create table logmgt.t0(id int);
                     ^

新建的表又如何？按预期，新建的表，是没有授权的，应该是访问不了。请看：

mydb=> \c - postgres
You are now connected to database "mydb" as user "postgres".
mydb=# create table logmgt.t3_new(id int);
CREATE TABLE
mydb=# \c - DCS
You are now connected to database "mydb" as user "DCS".
mydb=> select * from logmgt.t3_new;
ERROR:  permission denied for table t3_new

至此，我们调用最后一句，alter default privileges来改变未来表对象的访问权限：

mydb=> \c - postgres
You are now connected to database "mydb" as user "postgres".

alter default privileges in schema logmgt grant select, insert, update, delete on tables to "DCS";
ALTER DEFAULT PRIVILEGES

mydb=# drop table logmgt.t3_new;
DROP TABLE
mydb=# create table logmgt.t3_new(id int);
CREATE TABLE
mydb=# \c - "DCS"
You are now connected to database "mydb" as user "DCS".
mydb=> select * from logmgt.t3_new;
mydb=> select * from logmgt.t3_new;
 id 
----
(0 rows)

我们看看：这一句：

alter default privileges in schema logmgt grant select, insert, update, delete on tables to "DCS";

相同的语句，有时候它并不一定好使? 以防万一，总是好使的应该是：

alter default privileges for role postgres in schema logmgt grant select, insert, update, delete on tables to "DCS";
-- 加了一个for role postgres是关键。这个role必须是schema的owner或者对schema拥有绝对的权限。

如果当前连接用户本身就是postgres，那么for role postgres应该是可以省略的，两者意义相同。

看看实际结果：

mydb=# alter default privileges for role postgres in schema logmgt grant select, insert, update, delete on tables to "DCS";
ALTER DEFAULT PRIVILEGES
mydb=# drop table logmgt.t4_new;
DROP TABLE
mydb=# create table logmgt.t5_new(id int);
CREATE TABLE
mydb=# create table logmgt.t4_new(id int);
CREATE TABLE
mydb=# \c - "DCS"
You are now connected to database "mydb" as user "DCS".
mydb=> select * from logmgt.t5_new;
 id 
----
(0 rows)
mydb=> select * from logmgt.t4_new;
 id 
----
(0 rows)

这样处理起来，基本上满足上边提问 的要求。

汇总起来，核心的就是下边几个语句：[for role postgres是关键]

grant usage on schema logmgt to "DCS"; 
grant all privileges on all tables in schema logmgt to "DCS";
grant all privileges on all sequences in schema logmgt to "DCS";
alter default privileges for role postgres in schema logmgt grant select, insert, update, delete on tables to "DCS";

再看看如果语句：alter default privileges in schema logmgt grant select, insert, update, delete on tables to "DCS"; 不带for role，在DCS用户下执行，是什么样子的：

mydb=> \c - postgres
You are now connected to database "mydb" as user "postgres".
mydb=# alter default privileges in schema logmgt revoke select, insert, update, delete on tables from "DCS";
ALTER DEFAULT PRIVILEGES
mydb=# \c - "DCS"
You are now connected to database "mydb" as user "DCS".
mydb=> alter default privileges in schema logmgt grant select, insert, update, delete on tables to "DCS";
ALTER DEFAULT PRIVILEGES

mydb=> \c - postgres
You are now connected to database "mydb" as user "postgres".
mydb=# create table logmgt.t4(id int);
CREATE TABLE
mydb=# \c - "DCS"
You are now connected to database "mydb" as user "DCS".
mydb=> select * from logmgt.t4;
ERROR:  permission denied for table t4

发现没有，新建的表权限更改不起作用。我们可以用drop user "DCS"，看到它被授予了哪些相关权限：

mydb=> \c - postgres
You are now connected to database "mydb" as user "postgres".

mydb=# drop user "DCS";
ERROR:  role "DCS" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema logmgt
privileges for table logmgt.t1
privileges for table logmgt.t2
privileges for table logmgt.t3_new
owner of default privileges on new relations belonging to role DCS in schema logmgt

最末一句：owner of default privileges on new relations belonging to role DCS in schema logmgt。故名思义，新建的表要属于DCS的那些默认权限。显然不合要求。

2、反向推演

这时，如果我们倒推一下：

mydb=> \c - postgres
You are now connected to database "mydb" as user "postgres".
mydb=# drop user "DCS";
ERROR:  role "DCS" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema logmgt
privileges for table logmgt.t1
privileges for table logmgt.t2
owner of default privileges on new relations belonging to role DCS in schema logmgt
privileges for default privileges on new relations belonging to role postgres in schema logmgt
privileges for table logmgt.t5_new
privileges for table logmgt.t4_new

根据drop user "DCS"的细节提示， 可以看到它完整的权限授权信息。基本上可以看到：

"owner of default privileges on new relations belonging to role DCS in schema logmgt"这一权限起不到啥作用。它应该就是语句：alter default privileges in schema logmgt grant select, insert, update, delete on tables to "DCS";在用户
DCS"下执行以后导致的结果。

要求是新建的属于DCS用户的表的默认权限。它既然是属主，授不授权没啥区别。我们用下边的两个语句结合drop table反推下:

mydb=# alter default privileges for role postgres in schema logmgt revoke select, insert, update, delete on tables from "DCS";ALTER DEFAULT PRIVILEGES
mydb=# alter default privileges in schema logmgt revoke select, insert, update, delete on tables from "DCS";
ALTER DEFAULT PRIVILEGES
mydb=# drop user "DCS";
ERROR:  role "DCS" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema logmgt
privileges for table logmgt.t1
privileges for table logmgt.t2
privileges for table logmgt.t5_new
privileges for table logmgt.t4_new
privileges for table logmgt.t6

-- 重新清理掉
mydb=# drop schema logmgt cascade;
NOTICE:  drop cascades to 6 other objects
DETAIL:  drop cascades to table logmgt.t1
drop cascades to table logmgt.t2
drop cascades to table logmgt.t5_new
drop cascades to table logmgt.t4_new
drop cascades to table logmgt.t6
drop cascades to table logmgt.t7
DROP SCHEMA
mydb=# drop user "DCS";
DROP ROLE

mydb=# create schema logmgt;
CREATE SCHEMA
mydb=# create user "DCS" with password 'test123';
CREATE ROLE
mydb=# grant usage on schema logmgt to "DCS";
GRANT

mydb=# create table logmgt.t1(id int);
CREATE TABLE

mydb=# grant all privileges on all tables in schema logmgt to "DCS";
GRANT
mydb=# grant all privileges on all sequences in schema logmgt to "DCS";
GRANT
mydb=# \c - "DCS"
You are now connected to database "mydb" as user "DCS".
mydb=> select * from logmgt.t1;
 id 
----
(0 rows)

-- 验证相关权限：
mydb=> \c - postgres
You are now connected to database "mydb" as user "postgres".
mydb=# drop user "DCS";
ERROR:  role "DCS" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema logmgt
privileges for table logmgt.t1

mydb=# alter default privileges in schema logmgt grant select, insert, update, delete on tables to "DCS";
ALTER DEFAULT PRIVILEGES
mydb=# create table logmgt.t2(id int);
CREATE TABLE
mydb=# drop user "DCS";
ERROR:  role "DCS" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema logmgt
privileges for table logmgt.t1
privileges for default privileges on new relations belonging to role postgres in schema logmgt
privileges for table logmgt.t2

mydb=# drop user "DCS";
ERROR:  role "DCS" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema logmgt
privileges for table logmgt.t1
privileges for default privileges on new relations belonging to role postgres in schema logmgt
privileges for table logmgt.t2
privileges for table logmgt.t3

2.2、禁用public的schema, role

如果禁用public的role和schema，那就比较接近生产环境中的配置了。而且PG15里头默认就是这样的配置。

这时我们看看上边是如何授权的。

postgres=# create database mydb;
CREATE DATABASE
postgres=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE
postgres=# REVOKE ALL ON DATABASE  mydb FROM PUBLIC;
REVOKE

-- 建user, schema
postgres=# create user mydb with password 'test123';
CREATE ROLE
postgres=# create user "DCS" with password 'test123';
CREATE ROLE
postgres=# grant connect on database mydb to mydb;
GRANT
postgres=# grant connect on database mydb to "DCS";
GRANT

postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=# create schema logmgt;
CREATE SCHEMA
mydb=# alter schema logmgt owner to mydb;
ALTER SCHEMA
-- 或者create schema logmgt authorization mydb; 一样的效果
mydb=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 logmgt | mydb     |                      | 
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         | 
(2 rows)

-- 建两张表
mydb=# \c mydb mydb
You are now connected to database "mydb" as user "mydb".
mydb=> create table logmgt.t1(id int);
CREATE TABLE
mydb=> create table logmgt.t2(id int);
CREATE TABLE

-- 授权
mydb=> \c - postgres
You are now connected to database "mydb" as user "postgres".
mydb=# grant usage on schema logmgt to "DCS";
GRANT
mydb=# grant all privileges on all tables in schema logmgt to "DCS";
GRANT
mydb=# grant all privileges on all sequences in schema logmgt to "DCS";
GRANT

mydb=# alter default privileges for role mydb in schema logmgt grant select, insert, update, delete on tables to "DCS";
ALTER DEFAULT PRIVILEGES

--验证：
    -- 旧表的访问
mydb=# \c - "DCS"
You are now connected to database "mydb" as user "DCS".
mydb=> select * from logmgt.t1;
 id 
----
(0 rows)

    -- 新建表的访问
mydb=> \c - mydb
You are now connected to database "mydb" as user "mydb".
mydb=> create table logmgt.t3(id int);
CREATE TABLE
mydb=> \c - "DCS"
You are now connected to database "mydb" as user "DCS".
mydb=> select * from logmgt.t3;
 id 
----
(0 rows)
    -- 如果是用户postgres创建的表呢？
mydb=> \c - postgres
You are now connected to database "mydb" as user "postgres".
mydb=# create table logmgt.t4(id int);
CREATE TABLE
mydb=# \c - DCS
You are now connected to database "mydb" as user "DCS".
mydb=> select * from logmgt.t4;
ERROR:  permission denied for table t4

我们看到了，如果不是用户mydb创建的表，即算schema是logmgt, 授权后，仍然不能访问。原因：(我们只是for role mydb)

alter default privileges for role mydb in schema logmgt grant select, insert, update, delete on tables to "DCS";

我们再补上for role postgres，应该就能起作用了。

mydb=> \c - postgres
You are now connected to database "mydb" as user "postgres".
mydb=# alter default privileges for role postgres in schema logmgt grant select, insert, update, delete on tables to "DCS";
ALTER DEFAULT PRIVILEGES

-- 再次验证
mydb=# drop table logmgt.t4;
DROP TABLE
mydb=# create table logmgt.t4(id int);
CREATE TABLE
mydb=# \c - DCS
You are now connected to database "mydb" as user "DCS".
mydb=> select * from logmgt.t4;
 id 
----
(0 rows)

mydb=> \set ECHO_HIDDEN on

mydb=> \dp logmgt.t4
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' END as "Type",
  pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",
  pg_catalog.array_to_string(ARRAY(
    SELECT attname || E':\n  ' || pg_catalog.array_to_string(attacl, E'\n  ')
    FROM pg_catalog.pg_attribute a
    WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
  ), E'\n') AS "Column privileges",
  pg_catalog.array_to_string(ARRAY(
    SELECT polname
    || CASE WHEN NOT polpermissive THEN
       E' (RESTRICTIVE)'
       ELSE '' END
    || CASE WHEN polcmd != '*' THEN
           E' (' || polcmd || E'):'
       ELSE E':'
       END
    || CASE WHEN polqual IS NOT NULL THEN
           E'\n  (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)
       ELSE E''
       END
    || CASE WHEN polwithcheck IS NOT NULL THEN
           E'\n  (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)
       ELSE E''
       END    || CASE WHEN polroles <> '{0}' THEN
           E'\n  to: ' || pg_catalog.array_to_string(
               ARRAY(
                   SELECT rolname
                   FROM pg_catalog.pg_roles
                   WHERE oid = ANY (polroles)
                   ORDER BY 1
               ), E', ')
       ELSE E''
       END
    FROM pg_catalog.pg_policy pol
    WHERE polrelid = c.oid), E'\n')
    AS "Policies"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','m','S','f','p')
  AND c.relname OPERATOR(pg_catalog.~) '^(t4)$' COLLATE pg_catalog.default
  AND n.nspname OPERATOR(pg_catalog.~) '^(logmgt)$' COLLATE pg_catalog.default
ORDER BY 1, 2;
**************************

                                Access privileges
 Schema | Name | Type  |     Access privileges     | Column privileges | Policies 
--------+------+-------+---------------------------+-------------------+----------
 logmgt | t4   | table | postgres=arwdDxt/postgres+|                   | 
        |      |       | DCS=arwd/postgres         |                   | 
(1 row)
上边相关授权的情况一目了然.

mydb=> \set ECHO_HIDDEN off
mydb=> \dp+ logmgt.t3
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies 
--------+------+-------+-------------------+-------------------+----------
 logmgt | t3   | table | mydb=arwdDxt/mydb+|                   | 
        |      |       | DCS=arwd/mydb     |                   | 
(1 row)

(arwd指的就是insert, select, update, delete)
(Dxt分别指的就是：Truncate, Reference, Trigger)

其实上边的for role postgres应该可以省掉，因为执行授权的当前的用户(role)就是postgres。

写到这儿，想必大家也就明白了，那位同学为何说他执行的那个东东就是不太好使了。

3.总结

对某一特定schema下的表或其它对象进行授权，授给其它用户，包含将来创建的对象，使用以下脚本：

grant usage on schema logmgt to "DCS"; 
grant all privileges on all tables in schema logmgt to "DCS";
grant all privileges on all sequences in schema logmgt to "DCS";
alter default privileges [for role ] in schema logmgt grant select, insert, update, delete on tables to "DCS";

它有时候不起作用，主要原因：

没有for role时，相当于使用的是for role 。说句幸运的话，如果那个schema: logmgt下的表全都是postgres这个授权连接用户创建的，那么刚好for role用的也默认是它，那将完美工作。没任何问题。

结合到实际应用当中，人家应用创建的表在schema: logmgt下边，大概率是使用单独的用户，如mydb创建出来的。这个时候，你用默认的不带for role，那它就只对postgres创建的表logmgt.，起作用了。于是那位同学就有些着急的说：“执行了，不起作用了。"

要想少犯错误，那么： [for role] 始终把它带上，应该是能很好的避免一些无谓的错误的。

在PG中，schema可以视为一种数据库对象，它也有自己的 owner (某个user/role)。然后schema下边可以有别的基本对象。说白了，有点像在文件夹的顶层配了一级特殊的文件夹似的。默认的文件夹是PUBLIC。

关于权限的文章，我在前边也整理了几篇，可以一并参考：

带你绕过权限的坑：PostgreSQL插件(2): pg_repack 回收膨胀的空间占用

PostgreSQL权限的使用，小心再被拿捏

聊聊数据库中的用户安全策略

PostgreSQL中配置单双向SSL连接详解



请使用浏览器的分享功能分享到微信等