如何为 Postgresql 中的所有数据库创建一个具有只读权限的用户？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6452956/
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
How to create a user with readonly privileges for all databases in Postgresql?
提问by Alptugay
I want to create a user with only select privilege for all tables in all databases. I thought that I could get a list of databases and apply the following command for each database:
我想为所有数据库中的所有表创建一个仅具有选择权限的用户。我以为我可以获得一个数据库列表并对每个数据库应用以下命令：
GRANT select ON DATABASE dbname to user1;
But I got the following error:
但我收到以下错误：
ERROR: invalid privilege type SELECT for database
When I googled people advised to do the grant selectoperation for all tables. But new tables are being added always. So this is not an acceptable solution for me. Does anyone know any workarounds?
当我用谷歌搜索时，人们建议grant select对所有表进行操作。但是总是会添加新表。所以这对我来说不是一个可以接受的解决方案。有谁知道任何解决方法？
回答by a_horse_with_no_name
You cannot do this on database level, only on schema level.
您不能在数据库级别执行此操作，只能在模式级别执行此操作。
Assuming you are only using the publicschema in each database, you can do this:
假设您只public在每个数据库中使用架构，您可以这样做：
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user1;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO user;
回答by IMSoP
You need to do 2 things: firstly, allow access to existing objects; and secondly, set the default access for new objects created from now on.
您需要做两件事：首先，允许访问现有对象；其次，为从现在开始创建的新对象设置默认访问权限。
Note that granting access to "TABLES" includes views, but does not include sequences (such as the auto-increment function for "SERIAL" columns), so you'll probably want to grant access to those as well.
请注意，授予对“TABLES”的访问权限包括视图，但不包括序列（例如“SERIAL”列的自动递增函数），因此您可能还想授予对这些视图的访问权限。
The below assumes you want to do everything in the publicschema. The ALTER DEFAULT PRIVILEGESstatement can act on the entire database by omitting the IN SCHEMA ...clause; the GRANThas to be run once for each schema.
下面假设您要在public架构中执行所有操作。该ALTER DEFAULT PRIVILEGES声明可以通过省略对整个数据库行动IN SCHEMA ...条款; 在GRANT必须对每个方案运行一次。
-- Grant access to current tables and views
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user1;
-- Now make sure that's also available on new tables and views by default
ALTER DEFAULT PRIVILEGES
IN SCHEMA public -- omit this line to make a default across all schemas
GRANT SELECT
ON TABLES
TO user1;
-- Now do the same for sequences
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO user1;
ALTER DEFAULT PRIVILEGES
IN SCHEMA public -- omit this line to make a default across all schemas
GRANT SELECT, USAGE
ON SEQUENCES
TO user1;
PostgreSQL manual
PostgreSQL 手册
回答by Mike Sherrill 'Cat Recall'
I realize you've already said this isn't an acceptable answer, but it's the right answer anyway.
我知道您已经说过这不是一个可接受的答案，但无论如何它都是正确的答案。
Specifying security (GRANT and REVOKE) is part of table design and testing.
指定安全性（GRANT 和 REVOKE）是表设计和测试的一部分。
Don't move tables to production before table definitions, security, tests, and test data are under version control.
在表定义、安全性、测试和测试数据处于版本控制之下之前，不要将表移至生产环境。
Having said that, PostgreSQL doesn't have any SELECT permissions on databases. You can grant only CREATE, CONNECT, or TEMP permissions on databases.
话虽如此，PostgreSQL 对数据库没有任何 SELECT 权限。您只能授予对数据库的 CREATE、CONNECT 或 TEMP 权限。
You can grant SELECT on all tables in a given schema. I don't know how that affects tables created after running the GRANT statement, but it's fairly easy to test.
您可以在给定模式中的所有表上授予 SELECT 权限。我不知道这对运行 GRANT 语句后创建的表有何影响，但测试起来相当容易。
回答by Vishnu Kumar
For Postgres versions lower than 9.0:
对于低于 9.0 的 Postgres 版本：
psql -d DBNAME -qAt -c "SELECT 'GRANT SELECT ON ' || tablename || ' TO USER;'
FROM pg_tables WHERE schemaname = 'public'" | psql -d DBNAME
psql -d DBNAME -qAt -c "SELECT 'GRANT SELECT ON ' || viewname || ' TO USER;'
FROM pg_views WHERE schemaname = 'public'" | psql -d DBNAME
psql -d DBNAME -qAt -c "SELECT 'GRANT SELECT ON ' || relname || ' TO USER;'
FROM pg_statio_all_sequences WHERE schemaname = 'public'" | psql -d DBNAME
回答by exud
I do the next steps for create read-only user:
我执行以下步骤来创建只读用户：
create your_user:
创建 your_user：
1. createuser --interactive --pwprompt
go to postgresql in your_databases:
转到 your_databases 中的 postgresql：
2. psql your_database
define access privileges:
定义访问权限：
3. GRANT SELECT ON ALL TABLES IN SCHEMA public TO your_user;
define default access privileges:
定义默认访问权限：
4. ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO your_user;
