PostgreSQL · 特性分析 · 邏輯結構和權限體系 · 數據庫內核月報

本文旨在幫助用戶理解PostgreSQL的邏輯結構和權限體系，幫助用戶快速的理解和管理數據庫的權限。 ## 邏輯結構最上層是實例，實例中允許創建多個數據庫，每個數據庫中可以創建多個schema，每個schema下面可以創建多個對象。對象包括表、物化視圖、操作符、索引、視圖、序列、函數、… 等等。 ![](https://box.kancloud.cn/2016-06-02_574fe05aedc81.png) 在數據庫中所有的權限都和角色（用戶）掛鉤，public是一個特殊角色，代表所有人。超級用戶是有允許任意操作對象的，普通用戶只能操作自己創建的對象。另外有一些對象是有賦予給public角色默認權限的，所以建好之后，所以人都有這些默認權限。 ## 權限體系 ![](https://box.kancloud.cn/2016-06-02_574fe05b1033f.png) 實例級別的權限由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下面，那很危險。本文后面的例子中會提及。對象級別的權限，每種類型的對象權限屬性都不一樣，[具體請參考這里](http://www.postgresql.org/docs/9.5/static/sql-grant.html)。以表為例，可以有SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER這些權限。 ~~~ 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還支持凌駕于基本權限體系之上的安全策略，這些安全策略一般在企業級的商業數據庫中才有。 ### 行安全策略 [行安全策略](https://yq.aliyun.com/articles/4271) ### SELinux-PostgreSQL ## 權限規劃例子1 schema和database owner的安全思考以下是創建PostgreSQL schema的[語法說明頁](http://www.postgresql.org/docs/9.5/static/sql-createschema.html)的一個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 邏輯結構和權限體系介紹](https://yq.aliyun.com/articles/41210) [PostgreSQL 批量權限管理方法](https://yq.aliyun.com/articles/41512) [PostgreSQL schema,database owner 的高危注意事項](https://yq.aliyun.com/articles/41514) 建議用戶使用超級用戶創建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用戶有所幫助。