558
DNA遺傳哲學? - 數據庫裏schema應該屬於誰?
標簽
PostgreSQL , schema , template
背景
混沌初開
安裝好PostgreSQL軟件後，需要調用initdb,或者pg_ctl初始化數據庫實例，初始化數據庫實例時，通過bki接口（腳本），建立數據庫元數據。
初始化後，數據庫有了數據文件、日誌文件、控製文件、CLOG、WAL等一係列數據庫的文件。
同時會創建模板庫template0, template1, 以及數據庫postgres。
模板庫內，默認會有一個public schema，owner是誰？是初始化時數據庫的超級用戶。例如初始化數據庫集群時，超級用戶為postgres，那麼模板庫屬於postgres，模板庫裏的schema(public)也屬於postgres。
基因傳遞
接下來，用戶可以使用PostgreSQL數據庫從模板庫創建更多數據塊。
通過模板庫創建的新的數據庫，裏麵也會帶有模板庫所有的一切，包括schmea，這些對象屬於誰呢？
owner是postgres還是新建數據庫的用戶（或者指定的數據庫owner呢？）
目前，不管你用什麼用戶新建數據庫，新數據庫的權限，OWNER都會和模板庫保持原樣。
例如，template1的public屬於postgres用戶
template1=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
使用另一個用戶，創建一個數據庫，並將新的數據庫的owner設置為新用戶。但是這個新數據庫內的public schema owner依舊是postgres。
template1=# create role digoal superuser login;
CREATE ROLE
template1=# \c postgres
You are now connected to database "postgres" as user "test".
postgres=# create database db1 with template template1 owner digoal;
CREATE DATABASE
postgres=# \c db1
You are now connected to database "db1" as user "test".
db1=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
為什麼要基因？
假設不繼承模板庫的權限，而是將schema或者對象的權限或者OWNER直接轉嫁給數據庫的owner，會有什麼風險呢？
風險如下：
一個有create database權限的普通用戶，它可以利用這種方法，窺探其他數據庫的它看不到的內容。
創建一個普通用戶，帶有create database權限
db1=# create role r1 createdb login;
CREATE ROLE
連接到template1庫，使用超級用戶postgres創建一個測試表和測試數據
template1=> \c template1 postgres
You are now connected to database "template1" as user "postgres".
template1=# create table test(id int);
CREATE TABLE
template1=# insert into test values (1);
INSERT 0 1
普通用戶無法訪問這張測試表
template1=# \c template1 r1
You are now connected to database "template1" as user "r1".
template1=> select * from test;
ERROR: permission denied for relation test
以template1為模板，新建一個數據庫，OWNER為自己。
template1=> \c postgres r1
You are now connected to database "postgres" as user "r1".
postgres=> create database db3 with template template1 owner r1;
CREATE DATABASE
由於權限繼承，所以新建的數據庫，OWENR依然沒有權限查詢這張表
postgres=> \c db3 r1
You are now connected to database "db3" as user "r1".
db3=> \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
db3=> select * from test;
ERROR: permission denied for relation test
PostgreSQL 權限繼承的方法充分考慮了這一點，避免了安全風險問題。
有什麼更好的方法呢？
實際上用戶的想法和PostgreSQL實施的安全防護不一致，例如用戶創建了一個模板庫，並希望其他人通過這個模板庫創建的數據庫，裏麵的對象OWNER也改成建庫的OWNER。
create database db1 with template template1 owner newrole;
用戶也許希望db1裏麵包含的模板庫中的schema public也轉給newrole
如何改變現狀呢？
1. 既然owner改變不了，那麼可以使用權限來控製，模板建好後，把模板中的對象權限賦予給public角色。
\c template1 postgres
grant all on schema public to public;
2. 還有更人性化的方法，例如新增SQL語法：
給schema,table,view等對象增加一個權限選項(允許用戶選擇)，作為模板創建時，OWNER是否跟隨數據庫的owner。
3. 當以template0為模板創建數據庫時，建議public的owner可以改為數據庫的owner，同樣需要社區代碼層麵的支持。
4. 當用戶新建了數據庫後，再使用超級用戶，將新庫的public schema賦予給新庫的owner.
\c new_db superuser
alter schema public owner to db_user;
注意，schema owner有這個schema的絕對管理權限，包括刪除其他人在這個schema創建的對象。
所以千萬不要把自己的對象創建到別人的schema下麵，那很危險。
參考
https://www.postgresql.org/docs/9.6/static/sql-createdatabase.html
最後更新：2017-05-20 01:01:28