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下面，那很危險。

《PostgreSQL 邏輯結構 和 許可權體系 介紹》

參考

https://www.postgresql.org/docs/9.6/static/sql-createdatabase.html