如何在PostgreSQL中为所有数据库创建一个只读权限的用户？

Question

如何在PostgreSQL中为所有数据库创建一个只读权限的用户？

securitypostgresqlpermissionsdatabase-permissions

34

我想创建一个只能在所有数据库的所有表上进行选择操作的用户。我认为我可以获取数据库列表，并针对每个数据库应用以下命令：

GRANT select ON DATABASE dbname to user1;

但是我得到了以下错误：

ERROR:  invalid privilege type SELECT for database

当我谷歌搜索时，有人建议对所有表执行 grant select 操作。但新的表总是在不断添加。所以这对我来说不是一个可接受的解决方案。有人知道任何解决方法吗？

- Alptugay

@Jonas 实际上这是基本的 SQL，所以不适合 DBA。- http://dba.stackexchange.com/faq - Stefano

7个回答

33

你无法在数据库级别上实现此功能，只能在模式级别上实现。

假设你只在每个数据库中使用public模式，那么你可以这样做：

GRANT SELECT ON ALL TABLES IN SCHEMA public TO user1;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO user;

- user330315

2

你的说法是错误的，这将适用于新创建的表和视图 - “ALL TABLES”仅仅是在每个当前存在的表上运行GRANT SELECT的简写，并不设置任何默认值。要为新对象设置默认值，您需要使用ALTER DEFAULT PRIVILEGES命令：http://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html（请参见下面我的答案） - IMSoP

2

原始问题是关于在所有表和所有数据库中进行SELECT的主题。这在MySQL中是可能的，但我认为在PostgreSQL中不可能。在PostgreSQL中，必须独立地为每个数据库授予这些权限。也许您可以将此作为澄清添加进去（或者如果可能的话，请纠正我）。 - Michael

@Michael：嗯，我认为第一句话“你不能在数据库层面上这样做”已经很明确了。你必须按照模式来做，不能按照数据库（或整个安装）来做。 - user330315

3

是的 - 如果你知道Postgres的话。我猜（像我一样）很多对pg了解有限的人会找到这个问答。并不是每个人都知道模式是特定于数据库的。此外，如果你只是通过 psql 连接，你可以发出上述命令而不会出错 -> 这让我认为GRANT是应用于安装范围内的 :). 也许可以将 psql database_name 添加为第一个命令。另一方面，这些评论可能已经足够其他人使用了 :) - Michael

4

对于低于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

- Vishnu Kumar

我不得不执行以下命令：

psql -d DBNAME -qAt -c "SELECT 'GRANT SELECT ON ' || relname || ' TO USER;' FROM pg_statio_all_tables WHERE schemaname = 'public'" | psql -d DBNAME

- tymik

4

我知道你已经说过这不是一个可接受的答案，但它仍然是正确的答案。

指定安全性（GRANT和REVOKE）是表设计和测试的一部分。

在表定义、安全性、测试和测试数据都处于版本控制之前，不要将表移动到生产环境中。

话虽如此，PostgreSQL没有对数据库进行任何SELECT权限。您只能在数据库上授予CREATE、CONNECT或TEMP权限。

您可以在给定模式中授予所有表的SELECT权限。我不知道运行GRANT语句后创建的表会受到什么影响，但这很容易测试。

PostgreSQL Grant 语法

- Mike Sherrill 'Cat Recall'

4

我做下一步来创建只读用户:

创建您的用户:

1. createuser --interactive --pwprompt

前往您的数据库中的PostgreSQL：

2. psql your_database

定义访问权限：

3. GRANT SELECT ON ALL TABLES IN SCHEMA public TO your_user;

定义默认访问权限：

4. ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO your_user;

- exud

3

我知道这是一个非常老的问题，但当搜索这个问题时它仍然会出现，所以我只是发布了一个最新的答案。

从Postgres 14或更新版本开始，现在有预定义的角色来实现这个目的。

CREATE USER your_readonly_user WITH PASSWORD 'changeme';
GRANT pg_read_all_data TO your_readonly_user;

根据文档，这将赋予所有必要的特权：

“阅读所有数据（表、视图、序列），就好像拥有这些对象的 SELECT 权限和所有模式的 USAGE 权限一样，即使没有明确授予它。此角色没有设置 BYPASSRLS 角色属性。如果正在使用 RLS，则管理员可能希望在 GRANT 给该角色的角色上设置 BYPASSRLS。”

欲了解更多关于预定义角色的信息，请参见此处。

- emrekgn

1

尝试：

CREATE USER readonly WITH ENCRYPTED PASSWORD 'yourpassword';
GRANT CONNECT ON DATABASE <database_name> to readonly;
GRANT USAGE ON SCHEMA public to readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

- yucer

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- IMSoP · Accepted Answer

你需要做两件事：首先，允许对现有对象的访问；其次，设置从现在开始创建的新对象的默认访问权限。

请注意，授予“TABLES”的访问权限包括视图，但不包括序列（例如“SERIAL”列的自增函数），因此您可能还需要授予这些对象的访问权限。

以下假设您希望在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手册