PostgreSQL：使模式受限制/不可更改？

Question

我们喜欢我们的生产环境具有限制/不可更改的模式 - 开发方可以由开发人员拥有并随意更改 - 我们希望在升级时审核更改。

我不知道是否这可能是决策的解决方案发生：

postgres% create proddb with owner=postgres; 

unixside% pg_restore --dbname=devdb [--schema-only] --no-owner proddb 
/* grants to users on schema objects appear to remain intact */ 

/* here's the magic, I hope... */ 
postgres% revoke create on schema public from public; 
postgres% grant usage on schema public to produser(s); 

一些测试，似乎表明，在这个新的PRODDB用户可以使用表正常交互（在适当的补助），并不能修改模式（更改表，创建表，删除表等）。但我是偏执狂，对Postgres来说很新，所以...

问：这是正确的吗？

问：我错过了什么吗？

非常感谢。

Answer 1

是的，这是正确的。唯一的补充是的拥有者可以随时删除或修改它。因此，如果模式中有个现有的表，它可能不起作用。

Answer 2

发现缺失元素：序列。

用户在他的脚本中发现错误;类似的错误出现在日志：

ERROR: permission denied for sequence <sequence> 

生产模式显示，虽然序列创建的，他们被Postgres的拥有，没有明确的赠款，提供给用户。根据GRANT文档：

在表上授予权限不会自动将权限扩展到表使用的任何序列，包括绑定到SERIAL列的序列。序列权限必须单独设置。

我们修复（详细此演示）是找到所有序列：

unixside% pg_dump --schema-only proddb > proddb.schema 
unixside% grep -i 'create sequence' proddb.schema 

...并应用适当的补助（选择以防止表扫描，更新，以避免上述错误）：

postgres% grant select,update on <sequence> to produser(s); 

到目前为止，用户说，它的工作和错误日志已经停止......

Answer 3

我忘了是哪个版本的宝stgreSQL增加了语法，但在PostgreSQL中管理权限的最简单方法之一是通过“GRANT foo，priv ON ALL IN IN SCHEMA”语法。

BEGIN; 
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA my_schema TO my_role; 
GRANT USAGE ON ALL SEQUENCES IN SCHEMA my_schema TO my_role; 
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA my_schema TO my_role; 
COMMIT; 

确保权限始终设置正确非常方便。

功能的EXECUTE可能看起来很吓人，但不应该是除非你的功能是用SECURITY DEFINER属性创建的（如果你使用SECURITY DEFINER，你最好保持谨慎，因为你在玩PostgreSQL版本的“setuid”函数）。如果您根据预期权限在不同的SCHEMAS中间隔表格，那么当与search_path变量一起使用时，这会变得非常方便。

ALTER ROLE my_role SET search_path = my_schema, auth_schema, public; 
-- Avoid using the public schema (pretty please) 

凡auth_schema有my_role不应该有直接读取或写入权限表的集合。赋予GROUPS特权也很有用。

这里有一些相关的文档：

http://developer.postgresql.org/pgdocs/postgres/sql-grant.html

不要忘记你可以使用“\^h GRANT”在psql里很容易找出语法或记住什么可以对所有对象中进行模式（搜索“IN SCHEMA”）。了解。