授予使用情况PostgreSQL中将来创建的模式的特权 [英] grant usage & privileges on future created schema in PostgreSQL
问题描述
我现在集成的应用程序将创建新的架构。 (每个客户都有其自己的架构,例如schema1,schema2,schema3 .... etc)
要授予对新创建的架构和该架构中特定表的使用和只读访问权限,我执行以下命令:
The application I am integrating now will create new schemas. (each customer has its owned schema, eg. schema1, schema2, schema3 ....etc) To grant usage and read-only access to the new created schema and specific tables in the schema, I execute these commands:
GRANT USAGE ON SCHEMA schema1 TO read_only_user;
GRANT SELECT ON schema1.talbe1 TO read_only_user;
GRANT SELECT ON schema1.table2 TO read_only_user;
GRANT USAGE ON SCHEMA schema2 TO read_only_user;
GRANT SELECT ON schema2.talbe1 TO read_only_user;
GRANT SELECT ON schema2.table2 TO read_only_user;
(......and so on.....)
我只是想知道我是否可以准予使用&将来在PostgreSQL中创建的模式具有特权。只能找到更改将来创建的表的默认权限的方法,而不能找到将来创建的模式的默认权限。
I just wonder if I could grant usage & privileges on future created schema in PostgreSQL. Could only find ways to alter default privileges on future created tables but not future created schemas.
推荐答案
模式没有默认权限。但是,由于使用的模型使每个用户都有自己的模式,因此可以自动执行整个过程,包括根据需要创建用户和设置密码:
There are no default privileges for schemas. But since you are using a model whereby every user has its own schema you can automate the full process, including creating the user and setting a password, if needed:
CREATE FUNCTION new_user_schema (user text, pwd text) RETURNS void AS $$
DECLARE
usr name;
sch name;
BEGIN
-- Create the user
usr := quote_identifier(user);
EXECUTE format('CREATE ROLE %I LOGIN PASSWORD %L', usr, quote_literal(pwd));
-- Create the schema named after the user and set default privileges
sch := quote_identifier('sch_' || user);
EXECUTE format('CREATE SCHEMA %I', sch);
EXECUTE format('ALTER SCHEMA %I OWNER TO %L', sch, usr);
EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I
GRANT SELECT ON TABLES TO %L', sch, usr);
END; $$ LANGUAGE plpgsql STRICT;
然后,您可以使用简单的命令创建用户,创建架构并设置默认权限:
You can then create the user, create the schema and set up default privileges with a simple command:
SELECT new_user_schema('new_user', 'secret');
这篇关于授予使用情况PostgreSQL中将来创建的模式的特权的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!