数据库内核月报

本文旨在帮助用户理解PostgreSQL的逻辑结构和权限体系，帮助用户快速的理解和管理数据库的权限。

逻辑结构

最上层是实例，实例中允许创建多个数据库，每个数据库中可以创建多个schema，每个schema下面可以创建多个对象。

对象包括表、物化视图、操作符、索引、视图、序列、函数、… 等等。

在数据库中所有的权限都和角色（用户）挂钩，public是一个特殊角色，代表所有人。

超级用户是有允许任意操作对象的，普通用户只能操作自己创建的对象。

另外有一些对象是有赋予给public角色默认权限的，所以建好之后，所以人都有这些默认权限。

权限体系

实例级别的权限由pg_hba.conf来控制，例如：

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host all postgres 0.0.0.0/0 reject
host all all 0.0.0.0/0 md5

以上配置的解释：允许任何本地用户无密码连接任何数据库；不允许postgres用户从任何外部地址连接任何数据库；允许其他任何用户从外部地址通过密码连接任何数据库。

数据库级别的权限，包括允许连接数据库，允许在数据库中创建schema。默认情况下，数据库在创建后，允许public角色连接，即允许任何人连接。默认情况下，数据库在创建后，不允许除了超级用户和owner之外的任何人在数据库中创建schema。默认情况下，数据库在创建后，会自动创建名为public 的schema，这个schema的all权限已经赋予给public角色，即允许任何人在里面创建对象。

schema级别的权限，包括允许查看schema中的对象，允许在schema中创建对象。默认情况下新建的schema的权限不会赋予给public角色，因此除了超级用户和owner，任何人都没有权限查看schema中的对象或者在schema中新建对象。

schema使用 , 特别注意

According to the SQL standard, the owner of a schema always owns all objects within it. PostgreSQL allows schemas to contain objects owned by users other than the schema owner. This can happen only if the schema owner grants the CREATE privilege on his schema to someone else, or a superuser chooses to create objects in it.

千万不要把自己的对象创建到别人的schema下面，那很危险。本文后面的例子中会提及。

对象级别的权限，每种类型的对象权限属性都不一样，具体请参考这里。

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN domain_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdw_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER server_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]
         | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE lang_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
    ON LARGE OBJECT loid [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
    ON TABLESPACE tablespace_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

where role_specification can be:

    [ GROUP ] role_name
  | PUBLIC
  | CURRENT_USER
  | SESSION_USER

GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]

简单介绍一下grant的一些通用选项

WITH ADMIN OPTION表示被赋予权限的用户，拿到对应的权限后，还能将对应的权限赋予给其他人，否则只能自己有这个权限，但是不能再赋予给其他人。

用户

用户，角色在PostgreSQL是一个概念。

public 角色

public角色，代表所有人的意思。

如何查看和解读一个对象的当前权限状态

以表为例：

select relname,relacl from pg_class where relkind='r';

或者执行

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' 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 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')
  AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1, 2;

权限说明如下

 Schema |      Name       |   Type   |       Access privileges        | Column privileges | Policies
--------+-----------------+----------+--------------------------------+-------------------+----------
 public | sbtest1         | table    | postgres=arwdDxt/postgres     +|                   |
        |                 |          | digoal=a*r*w*d*D*x*t*/postgres |                   |
 public | sbtest10        | table    | postgres=arwdDxt/postgres      |                   |
 public | sbtest10_id_seq | sequence |                                |                   |
 public | sbtest11        | table    | postgres=arwdDxt/postgres      |                   |
 public | sbtest11_id_seq | sequence |                                |                   |
 public | sbtest12        | table    | postgres=arwdDxt/postgres      |                   |
 public | sbtest12_id_seq | sequence |                                |                   |

解释一下 Access privileges

rolename=xxx 其中rolename就是被赋予权限的用户名，即权限被赋予给谁了?

=xxx 表示这个权限赋予给了public角色，即所有人

/yyyy 表示是谁赋予的这个权限?

权限的含义如下

rolename=xxxx -- privileges granted to a role
        =xxxx -- privileges granted to PUBLIC

            r -- SELECT ("read")
            w -- UPDATE ("write")
            a -- INSERT ("append")
            d -- DELETE
            D -- TRUNCATE
            x -- REFERENCES
            t -- TRIGGER
            X -- EXECUTE
            U -- USAGE
            C -- CREATE
            c -- CONNECT
            T -- TEMPORARY
      arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
            * -- grant option for preceding privilege

        /yyyy -- role that granted this privilege

例子

赋予权限的人是postgres用户， sbtest2表的select权限被赋予给了digoal用户。

postgres=# grant select on sbtest2 to digoal;
GRANT
postgres=# \dp+ sbtest2
                                  Access privileges
 Schema |  Name   | Type  |     Access privileges     | Column privileges | Policies
--------+---------+-------+---------------------------+-------------------+----------
 public | sbtest2 | table | postgres=arwdDxt/postgres+|                   |
        |         |       | digoal=r/postgres         |                   |
(1 row)

回收权限一定要针对已有的权限来，如果你发现这里的权限还在，那照着权限回收即可。

例如

revoke select on sbtest2 from digoal;

更高基本的安全控制

PostgreSQL还支持凌驾于基本权限体系之上的安全策略，这些安全策略一般在企业级的商业数据库中才有。

行安全策略

SELinux-PostgreSQL

权限规划例子1 schema和database owner的安全思考

以下是创建PostgreSQL schema的语法说明页的一个note:

According to the SQL standard, the owner of a schema always owns all objects within it. PostgreSQL allows schemas to contain objects owned by users other than the schema owner. This can happen only if the schema owner grants the CREATE privilege on his schema to someone else, or a superuser chooses to create objects in it.

schema的owner默认是该schema下的所有对象的owner。同时PostgreSQL还允许用户在别人的schema下创建对象，所以一个对象可能属于”两个”owner。更”糟糕”的是schema 的owner有 drop该schema下面的所有对象的权限。

所以千万不要把自己的对象创建到别人的schema下面，那很危险。看个例子： r1创建了一个schema r1, 并把这个schema的写权限给了r2；然后r2和超级用户postgres分别在r1这个schema下面创建了一个表；然后r1可以把r2和postgres在r1 schema下创建的表删掉，然后就没有然后了。

postgres=# create role r1 login;
CREATE ROLE
postgres=# create role r2 login;
CREATE ROLE

postgres=# grant all on database postgres to r1;
GRANT
postgres=# grant all on database postgres to r2;
GRANT

postgres=# \c postgres r1;
postgres=> create schema r1;
CREATE SCHEMA
postgres=> grant all on schema r1 to r2;
GRANT

postgres=> \c postgres r2;
postgres=> create table r1.t(id int);
CREATE TABLE

postgres=> \c postgres postgres
postgres=# create table r1.t1(id int);
CREATE TABLE

postgres=# \c postgres r1
postgres=> drop table r1.t;
DROP TABLE
postgres=> drop table r1.t1;
DROP TABLE

或者直接drop schema cascade来删除整个schema.

对于database的owner也存在这个问题，它同样具有删除database中任何其他用户创建的对象的权力。

例子：

普通用户r1创建的数据库
postgres=> \c postgres r1
You are now connected to database "postgres" as user "r1".
postgres=> create database db1;
CREATE DATABASE
postgres=> grant all on database db1 to r2;
GRANT

其他用户在这个数据库中创建对象
postgres=> \c db1 r2
You are now connected to database "db1" as user "r2".
db1=> create schema r2;
CREATE SCHEMA
db1=> create table r2.t(id int);
CREATE TABLE
db1=> insert into t select generate_series(1,100);
INSERT 0 100

db1=> \c db1 postgres
You are now connected to database "db1" as user "postgres".
db1=# create table t(id int);
CREATE TABLE
db1=# insert into t select generate_series(1,100);
INSERT 0 100

数据库的OWNER不能直接删数据库中的对象
postgres=> \c db1 r1
You are now connected to database "db1" as user "r1".
db1=> drop table r2.t ;
ERROR:  permission denied for schema r2
db1=> drop table public.t ;
ERROR:  must be owner of relation t
db1=> drop schema r2;
ERROR:  must be owner of schema r2
db1=> drop schema public;
ERROR:  must be owner of schema public
db1=> \c postgres r1
You are now connected to database "postgres" as user "r1".
postgres=> drop database r1;
ERROR:  database "r1" does not exist

但是可以直接删库
postgres=> drop database db1;
DROP DATABASE

database , schema 权限规划安全建议

介于此，我建议用户使用超级用户创建schema和database，然后再把schema和database的读写权限给普通用户，这样就不怕被误删了。因为超级用户本来就有所有权限。

还有一种方法是创建事件触发器，当执行drop 命令时，只有owner和超级用户能删对应的对象。

权限规划例子2, 只读用户的设计

在一些企业里面，通常会在数据库中创建一些只读用户，这些只读用户可以查看某些用户的对象，但是不能修改或删除这些对象的数据。

这种用户通常可以给开发人员，运营人员使用，或者数据分析师等角色的用户使用。

因为他们可能关注的是数据本身，并且为了防止他们误操作修改或删除线上的数据，所以限制他们的用户只有只读的权限。

MySQL这块的管理应该非常方便。

其实PostgreSQL管理起来也很方便。

用户可以先参考我前面写的两篇文章

PostgreSQL 逻辑结构和权限体系介绍

PostgreSQL 批量权限管理方法

PostgreSQL schema,database owner 的高危注意事项

建议用户使用超级用户创建schema和database，然后再把schema和database的读写权限给普通用户，这样就不怕被误删了。因为超级用户本来就有所有权限。

为了满足本文的需求, 创建读写用户的只读影子用户

1. 使用超级用户创建读写账号，创建数据库, 创建schema

postgres=# create role appuser login;
CREATE ROLE

postgres=# create database appuser;

postgres=# \c appuser postgres
appuser=# create schema appuser;  -- 使用超级用户创建schema

赋权
appuser=# grant connect on database to appuser;  -- 只赋予连接权限
appuser=# grant all on schema appuser to appuser;  -- 值赋予读和写权限

2. 假设该读写账号已经创建了一些对象

\c appuser appuser
appuser=> create table tbl1(id int);
CREATE TABLE
appuser=> create table tbl2(id int);
CREATE TABLE
appuser=> create table tbl3(id int);
CREATE TABLE

3. 创建只读影子账号

postgres=# create role ro login;
CREATE ROLE

postgres=# \c appuser postgres
appuser=# grant connect on database appuser to ro;
appuser=# grant usage on schema appuser to ro;

4. 创建隐藏敏感信息的视图

假设tbl2是敏感信息表，需要加密后给只读用户看

\c appuser appuser
appuser=> create view v as select md5(id::text) from tbl2;
CREATE VIEW

5. 修改已有权限

创建权限管理函数
\c appuser appuser
appuser=> create or replace function g_or_v
(
  g_or_v text,   -- 输入 grant or revoke 表示赋予或回收
  own name,      -- 指定用户 owner
  target name,   -- 赋予给哪个目标用户 grant privilege to who?
  objtyp text,   --  对象类别: 表, 物化视图, 视图 object type 'r', 'v' or 'm', means table,view,materialized view
  exp text[],    --  排除哪些对象, 用数组表示, excluded objects
  priv text      --  权限列表, privileges, ,splits, like 'select,insert,update'
) returns void as $$
declare
  nsp name;
  rel name;
  sql text;
  tmp_nsp name := '';
begin
  for nsp,rel in select t2.nspname,t1.relname from pg_class t1,pg_namespace t2 where t1.relkind=objtyp and t1.relnamespace=t2.oid and t1.relowner=(select oid from pg_roles where rolname=own)
  loop
    if (tmp_nsp = '' or tmp_nsp <> nsp) and lower(g_or_v)='grant' then
      -- auto grant schema to target user
      sql := 'GRANT usage on schema "'||nsp||'" to '||target;
      execute sql;
      raise notice '%', sql;
    end if;

    tmp_nsp := nsp;

    if (exp is not null and nsp||'.'||rel = any (exp)) then
      raise notice '% excluded % .', g_or_v, nsp||'.'||rel;
    else
      if lower(g_or_v) = 'grant' then
        sql := g_or_v||' '||priv||' on "'||nsp||'"."'||rel||'" to '||target ;
      elsif lower(g_or_v) = 'revoke' then
        sql := g_or_v||' '||priv||' on "'||nsp||'"."'||rel||'" from '||target ;
      else
        raise notice 'you must enter grant or revoke';
      end if;
      raise notice '%', sql;
      execute sql;
    end if;
  end loop;
end;
$$ language plpgsql;

appuser=> select g_or_v('grant', 'appuser', 'ro', 'r', array['public.tbl2'], 'select');
WARNING:  no privileges were granted for "public"
CONTEXT:  SQL statement "GRANT usage on schema "public" to ro"
PL/pgSQL function g_or_v(text,name,name,text,text[],text) line 13 at EXECUTE
NOTICE:  GRANT usage on schema "public" to ro
NOTICE:  grant select on "public"."tbl1" to ro
NOTICE:  grant excluded public.tbl2 .
NOTICE:  grant select on "public"."tbl3" to ro
 g_or_v
--------

(1 row)

另外还提供了一种方法，但是一定要指定schema，所以用户自己要注意，如果要对所有schema操作，需要把所有的schema都写进去。

grant select on all tables in schema public,schema1,schema2,schema3 to ro;

并且这种方法还有一个弊端，如果这些schema下面有其他用户创建的对象，也会被赋予，如果赋权的账号没有权限，则会报错。
所以还是建议使用我提供的函数来操作

6. 回收敏感表的权限

因为前面已经排除赋予了，所以不需要回收

7. 修改新建对象的默认权限

appuser=> alter default privileges for role appuser grant select on tables to ro;
ALTER DEFAULT PRIVILEGES
appuser=> \ddp+
               Default access privileges
  Owner   | Schema | Type  |     Access privileges
----------+--------+-------+---------------------------
 appuser  |        | table | appuser=arwdDxt/appuser  +
          |        |       | ro=r/appuser

8. 未来如果有新增的敏感表，先创建视图，同时回收表的权限

appuser=> create table tbl4(id int);
CREATE TABLE
appuser=> create view v2 as select md5(id::text) from tbl4;
CREATE VIEW
appuser=> revoke select on tbl4 from ro;
REVOKE

权限检查

appuser=> \dp+ v2
                               Access privileges
 Schema | Name | Type |    Access privileges    | Column privileges | Policies
--------+------+------+-------------------------+-------------------+----------
 public | v2   | view | appuser=arwdDxt/appuser+|                   |
        |      |      | ro=r/appuser            |                   |
(1 row)

希望本文对PostgreSQL用户有所帮助。

数据库内核月报－ 2016 / 05

PostgreSQL · 特性分析 · 逻辑结构和权限体系