共享数据库托管

本页面的目的是提供一个地方来交流关于如何在共享托管环境中运行 PostgreSQL 的最佳方法的想法，如这里所建议。如果您有兴趣讨论在类似前提条件下运行 PostgreSQL 的内在因素，请直接通过电子邮件与我联系：jacob at internet24 dot de，因为我很少查看此网站，而且更改通知系统对我来说似乎不起作用。

定义共享托管

出于本文档的目的，共享数据库托管是指由 DBMS 管理机构（以下称为托管提供商）为第三方（以下称为客户）运行 PostgreSQL 集群的过程。客户之间没有组织关系，但每个客户都与托管提供商有关系。客户通常需要完全控制少量数据库，但不想管理自己的 PostgreSQL 集群。在众多客户之间共享单个集群系统的资源在经济上是很有吸引力的。

共享托管设置的目标

完全隔离客户彼此之间的访问
创建和删除数据库和用户的标准化流程

方法

方法 1

仅通过访问超级用户帐户来管理集群
- 不需要对 pg_hba.conf 等进行永久修改
- 中央管理系统可以管理多个集群，而无需额外的控制通道
客户不能直接创建新的角色和数据库
每个客户数据库都分配了一个关联的主要用户
- 充当数据库本地超级用户
- 通常创建并拥有所有对象
每个数据库可以有多个额外用户
- 这些用户的权限由主要用户授予

pg_hba.conf

我们允许使用本地托管网络上的 md5 身份验证，通过 TCP/IP 访问集群中的所有数据库。一个角色只有在它是与数据库同名的角色的成员时才能登录。

#host samerole all hosting_network md5
host samerole all 192.168.0.0/24 md5

template1

我们修改 template1 以撤销 "PUBLIC" 对公共模式的所有权限，以防止其他客户访问各个客户数据库的公共模式。我们还添加了对 PL/PGSQL 的支持。

psql -U postgres template1 -f - << EOT

REVOKE ALL ON DATABASE template1 FROM public;
REVOKE ALL ON SCHEMA public FROM public;
GRANT ALL ON SCHEMA public TO postgres;
CREATE LANGUAGE plpgsql;

EOT

如果您认为打破某些 PostgreSQL 管理应用程序（pgadmin、phppgadmin、psql 部分功能）是可以接受的，那么您也可以撤销 pg_catalog 对 "PUBLIC" 的一些权限来“解决”本文档最后部分中的问题 1

psql -U postgres template1 -f - << EOT

REVOKE ALL ON pg_user FROM public;
REVOKE ALL ON pg_roles FROM public;
REVOKE ALL ON pg_group FROM public;
REVOKE ALL ON pg_authid FROM public;
REVOKE ALL ON pg_auth_members FROM public;

REVOKE ALL ON pg_database FROM public;
REVOKE ALL ON pg_tablespace FROM public;
REVOKE ALL ON pg_settings FROM public;

EOT

创建新的数据库 + 主要用户

我们创建一个角色，该角色没有特殊访问权限，其名称与我们计划的数据库名称相同，以及一个用于我们的主要用户的登录角色，该角色成为前者的成员。然后，我们使用主要用户作为所有者创建数据库。最后，我们将新数据库中公共模式的所有权限授予主要用户

psql -U postgres template1 -f - <<EOT

CREATE ROLE <DBNAME> NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOLOGIN;
CREATE ROLE <DBMAINUSER> NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ENCRYPTED PASSWORD '<DBMAINUSERPASS>';
GRANT <DBNAME> TO <DBMAINUSER>;
CREATE DATABASE <DBNAME> WITH OWNER=<DBMAINUSER>;
REVOKE ALL ON DATABASE <DBNAME> FROM public;

EOT

psql -U postgres <DBNAME> -f - <<EOT

GRANT ALL ON SCHEMA public TO <DBMAINUSER> WITH GRANT OPTION;

EOT

创建额外的用户

psql -U postgres <DBNAME> -f - <<EOT

CREATE ROLE <DBEXTRAUSER> NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ENCRYPTED PASSWORD '<DBEXTRAUSERPASS>';
GRANT USAGE ON SCHEMA public TO <DBEXTRAUSER>;
GRANT CONNECT,TEMPORARY ON DATABASE <DBNAME> TO <DBEXTRAUSER>;
GRANT <DBNAME> TO <DBEXTRAUSER>;

EOT

移除额外的用户

psql -U postgres <DBNAME> -f - <<EOT

-- REMOVE ALL PERMISSIONS FROM ALL OBJECTS OWNED BY DBEXTRAUSER
-- TERMINATE CONNECTIONS OF DBEXTRAUSER
REASSIGN OWNED BY <DBEXTRAUSER> TO <DBMAINUSER>;
DROP ROLE <DBEXTRAUSER>

EOT

移除数据库 + 主要用户

psql -U postgres template1 -f - <<EOT

-- TERMINATE CONNECTIONS OF ALL USERS CONNECTED TO <DBNAME>
DROP DATABASE <DBNAME>;
DROP ROLE <DBMAINUSER>
DROP ROLE <DBNAME>

EOT

影响共享托管的 PostgreSQL 问题

这应该是一个问题领域的列表，在这些领域中，当前的 PostgreSQL 版本在共享托管需求方面存在一些不足。

pg_catalog 允许每个人访问数据库和用户名称，但是禁用对该模式的访问会导致基本功能无法使用
不支持限制数据库大小
数据库本地用户名尚未完全支持（md5 身份验证）
必须对每个单个对象（没有“REASSIGN”等效项）撤销授予用户的全部权限（删除该用户之前的先决条件）
没有内置支持来设置用户、查询或数据库优先级