postgresql PGsql ：在架构上向用户授予每一个权利
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6799224/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PGsql : Grant every single right to a user on a schema
提问by Lucas Kauffman
I can't figure out how to give every single right to a specific user, I want a user to have every single right on a schema:
我不知道如何为特定用户授予每一项权利，我希望用户拥有架构上的每一项权利：
- inserts, deletes, updates, selects, ... on existing tables
- 在现有表上插入、删除、更新、选择……
I have tried doing :
我试过这样做：
GRANT ALL PRIVILEGES ON SCHEMA schema to "user";
GRANT ALL ON SCHEMA schema to "local_518561";
GRANT ALL PRIVILEGES ON table schema.table to "user";
GRANT ALL ON table schema.table to "user";
The querys return succesfull, but every time I use the other user I get insuffiecent permissions error.
查询返回成功，但每次我使用其他用户时，我都会收到权限不足的错误。
采纳答案by Lucas Kauffman
The answer lies in the sequences, if you do not give rights to the table AND the sequence (if any) than you cannot insert.
答案在于序列，如果您不授予表和序列（如果有）的权限，则您无法插入。
回答by Clint Pachl
GRANT ALL PRIVILEGES ON SCHEMA schema_name TO role_name;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO role_name;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schema_name TO role_name;
回答by doctore
If you are using a version of PostgreSQL < 9, you can use the following store procedures to manage permissions of tables and sequences:
如果您使用的是 PostgreSQL < 9 的版本，则可以使用以下存储过程来管理表和序列的权限：
CREATE OR REPLACE FUNCTION grantTablesOfSchema (user VARCHAR,
permissions VARCHAR, schema VARCHAR) RETURNS VARCHAR AS
$body$
DECLARE
regActual RECORD;
numTables INTEGER;
BEGIN
numTables := 0;
FOR regActual IN
SELECT tablename FROM pg_tables WHERE schemaname = schema
LOOP
numTables := numTables + 1;
EXECUTE 'GRANT ' || permissions || ' ON ' || schema || '.' || regActual.tablename || ' TO ' || user;
END LOOP;
RETURN 'Tables: ' || numTables::VARCHAR;
END;
$body$
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION grantSequencesOfSchema (user VARCHAR,
permissions VARCHAR, database VARCHAR, schema VARCHAR) RETURNS VARCHAR AS
$body$
DECLARE
regActual RECORD;
numSequences INTEGER;
BEGIN
numSequences := 0;
FOR regActual IN
SELECT sequence_catalog, sequence_schema, sequence_name
FROM information_schema.sequences
WHERE sequence_catalog = database AND sequence_schema = schema
LOOP
numSequences := numSequences + 1;
EXECUTE 'GRANT ' || permissions || ' ON ' || schema || '.' || regActual.sequence_name || ' TO ' || user;
END LOOP;
RETURN 'Sequences: ' || numSequences::VARCHAR;
END;
$body$
LANGUAGE 'plpgsql';
And a example of use:
以及一个使用示例：
CREATE USER user1 WITH PASSWORD 'user1@user1?user1';
GRANT CONNECT ON DATABASE database1 TO user1;
GRANT USAGE ON SCHEMA schema1 TO user1;
SELECT * FROM grantTablesOfSchema ('user1', 'SELECT, UPDATE, INSERT, DELETE', 'schema1');
SELECT * FROM grantSequencesOfSchema ('user1', 'ALL', 'database1', 'schema1');
If, on the contrary your version of PostgreSQL is >= 9:
相反，如果您的 PostgreSQL 版本 >= 9：
GRANT ALL ON ALL SEQUENCES IN SCHEMA schema1 TO user1;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema1 TO user1;
