作者
digoal
日期
2017-05-15
标签
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
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
