如何在PostgreSQL中创建只读用户？

Question

我希望在PostgreSQL中创建一个只能从特定数据库进行SELECT操作的用户。 在MySQL中，相应的命令是：

GRANT SELECT ON mydb.* TO 'xxx'@'%' IDENTIFIED BY 'yyy';

在PostgreSQL中，等效的命令或命令系列是什么？

我已经尝试过...

postgres=# CREATE ROLE xxx LOGIN PASSWORD 'yyy';
postgres=# GRANT SELECT ON DATABASE mydb TO xxx;

但看起来你只能在数据库上授予 CREATE、CONNECT、TEMPORARY 和 TEMP 权限。

- Ethan

Answer 1

授权使用/选择单个表

如果您只授予数据库的CONNECT权限，用户可以连接但没有其他权限。您必须分别授予命名空间（模式）上的USAGE和表和视图上的SELECT权限，如下所示：

GRANT CONNECT ON DATABASE mydb TO xxx;
-- This assumes you're actually connected to mydb..
GRANT USAGE ON SCHEMA public TO xxx;
GRANT SELECT ON mytable TO xxx;

多表/视图 (PostgreSQL 9.0+)

在最新版本的PostgreSQL中，您可以使用单个命令对模式(schema)中的所有表/视图等授予权限，而不必一个一个地输入：

GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;

这只对已经创建的表有影响。更强大的是，您可以自动将默认角色分配给未来的对象：

ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT SELECT ON TABLES TO xxx;

请注意，默认情况下，此命令只会影响由发出此命令的用户创建的对象（表）：尽管它也可以设置在该发出用户是成员的任何角色上。但是，当创建新对象时，您不会为所有成员角色获取默认权限...因此仍需要一些麻烦。如果您采用数据库具有拥有角色，并且模式更改是以该拥有角色执行的方法，则应将默认权限分配给该拥有角色。在我看来，这有点混乱，您可能需要进行实验以找到一个功能性的工作流程。

多个表/视图（PostgreSQL版本9.0之前）

为了避免在长时间的多表更改中出现错误，建议使用以下“自动”过程生成所需的GRANT SELECT以赋予每个表/视图：

SELECT 'GRANT SELECT ON ' || relname || ' TO xxx;'
FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE nspname = 'public' AND relkind IN ('r', 'v', 'S');

以下代码将输出在public schema下，授予SELECT权限的GRANT命令，适用于所有表格、视图和序列。注意这只会应用于已经创建的表格。

- araqnid

---

你应该将有关PG9的编辑放在帖子顶部。 - Danilo Bargen

不错。我想补充一点，您可能还需要允许该用户读取序列; 因此：GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO xxx; - JJC

需要注意的是，为了防止该用户能够创建新表，我必须执行REVOKE CREATE ON SCHEMA public FROM PUBLIC;操作。如果没有这个操作，“只读”用户无法修改现有表，但可以在模式中创建新表并向这些表添加/删除数据。 - Ajedi32

@Ajedi32 这应该是被接受的答案的一部分！谢谢 - Asfand Qazi

对于像我这样的新手，我认为值得一提的是，你应该使用 psql mydb 命令来启动控制台，否则大部分操作都不能进行。对我个人而言，花费了相当长的时间才弄清楚如何操作。希望这能帮助到其他人。 - Anas

Answer 2

参考来源于这篇博客：

创建只读用户的脚本：

CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'Test1234';
\connect YourDatabaseName;

给这个只读用户分配权限：

GRANT CONNECT ON DATABASE YourDatabaseName TO Read_Only_User;
GRANT USAGE ON SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO Read_Only_User;

-- [Optional] Use with care
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO Read_Only_User;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

分配权限以阅读将来创建的所有新表

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO Read_Only_User;

- Anvesh

---

除一件遗漏之外，这是一个非常好的答案：ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO Read_Only_User;这也将允许在同一数据库中创建的所有表都能被读取。 - Ravbaker

允许只读用户访问序列是不寻常的。读取序列会更新它，而通常只在进行“插入”时才需要使用它们。 - jpmc26

@FabienHaddadi 这意味着，除非您有一些非常特殊的要求，否则我不认为需要向只读用户授予任何序列权限。 - jpmc26

@FabienHaddadi 最好的预防方法是使用点时间快照来创建转储(--serializable-deferrable可以实现)。除此之外，我不会使用只读用户来修复损坏的数据导入。 - jpmc26

需要注意的是，从GRANT USAGE...开始的指令（例如对表格进行GRANT permissions）应在目标数据库中运行，否则权限将无法生效。为此，您可以使用\connect db_name;连接到数据库（它也可以在SQL文件中使用）。 - Lucas Basquerotto

Answer 3

从PostgreSQL v14开始，你可以通过授予预定义的pg_read_all_data角色来实现：

GRANT pg_read_all_data TO xxx;

- Laurenz Albe

---

然而，据我所知，这将授予对实例中所有数据库的只读访问权限，而不是特定数据库的访问权限，对吗？ - adamo

@adamo 正确；但很粗糙。 - Laurenz Albe

非常适合我的使用情况，即一个单一授权允许对所有数据库进行读取访问，包括现有和未来的表格。 - undefined

Answer 4

请注意，PostgreSQL 9.0（目前正在测试版）将提供一种简单的方法来实现该操作：

test=> GRANT SELECT ON ALL TABLES IN SCHEMA public TO joeuser;

- bortzmeyer

---

为了使此功能正常工作，我必须在特定的数据库中。Postgresql 9.5。 - user1158559

这仅适用于模式中已存在的表。如果写入用户后来创建或替换表，则只读用户将无法访问它们。 - anneb

Answer 5

以下是我发现的最佳方法，可用于添加只读用户（适用于 PostgreSQL 9.0 或更新版本）：

$ sudo -upostgres psql postgres
postgres=# CREATE ROLE readonly WITH LOGIN ENCRYPTED PASSWORD '<USE_A_NICE_STRONG_PASSWORD_PLEASE';
postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

然后登录到所有相关的机器（主节点+只读从节点/热备份等），并运行：

$ echo "hostssl <PUT_DBNAME_HERE> <PUT_READONLY_USERNAME_HERE> 0.0.0.0/0 md5" | sudo tee -a /etc/postgresql/9.2/main/pg_hba.conf
$ sudo service postgresql reload

- Jay Taylor

---

我喜欢这种方法，但我还需要授予 CONNECT ON DATABASE [thedatabase] TO [theuser or role]; 和 GRANT USAGE ON SCHEMA public TO [theuser or role]; - Ian Connor

公共模式仍允许此类用户创建表。此外，新表和序列也没有被覆盖。不幸的是，这比想象中要复杂得多。:-/ 我会在验证后发布我最终采取的措施。 - JJC

在您上面的脚本中，您试图创建角色两次。我猜想您打算使用“ALTER ROLE...”来启用该角色登录并设置密码。 - Bogdan

如果您已经拥有该用户，在创建只读角色并授予选择权限后，将新角色授予该用户：GRANT readonly TO <USER>。 - gonz

Answer 6

默认情况下，新用户具有创建表的权限。如果您计划创建一个只读用户，则这可能不是您想要的。

要创建一个真正的只读用户并在PostgreSQL 9.0+中使用，请执行以下步骤：

# This will prevent default users from creating tables
REVOKE CREATE ON SCHEMA public FROM public;

# If you want to grant a write user permission to create tables
# note that superusers will always be able to create tables anyway
GRANT CREATE ON SCHEMA public to writeuser;

# Now create the read-only user
CREATE ROLE readonlyuser WITH LOGIN ENCRYPTED PASSWORD 'strongpassword';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonlyuser;

如果您的只读用户没有权限列出表格（即\d没有结果），那么这可能是因为您没有架构的USAGE权限。 USAGE是一种允许用户实际使用已分配权限的权限。这有什么意义？我不确定。修复方法：

# You can either grant USAGE to everyone
GRANT USAGE ON SCHEMA public TO public;

# Or grant it just to your read only user
GRANT USAGE ON SCHEMA public TO readonlyuser;

- Adrian Macneil

Answer 7

我已经为此创建了一个方便的脚本; pg_grant_read_to_db.sh。该脚本将只授予指定角色对数据库模式中所有表、视图和序列的只读权限，并将它们设置为默认值。

- Jakub Jirutka

---

非常有用，谢谢。 - Aankhen

Answer 8

我阅读了所有可能的解决方案，它们都很好，只要你在授权之前记得连接到数据库即可 ;) 不管怎样，还是感谢其他所有的解决方案！！！

user@server:~$ sudo su - postgres

创建psql用户：

postgres@server:~$ createuser --interactive 
Enter name of role to add: readonly
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

启动psql命令行并为创建的用户设置密码：

postgres@server:~$ psql
psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1), server 9.5.14)
Type "help" for help.

postgres=# alter user readonly with password 'readonly';
ALTER ROLE

连接至目标数据库:

postgres=# \c target_database 
psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1), server 9.5.14)
You are now connected to database "target_database" as user "postgres".

授予所有必要的权限：

target_database=# GRANT CONNECT ON DATABASE target_database TO readonly;
GRANT

target_database=# GRANT USAGE ON SCHEMA public TO readonly ;
GRANT

target_database=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly ;
GRANT

更改目标数据库公共模式的默认权限：

target_database=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES

- thomi_ch

Answer 9

如果您的数据库位于公共模式下，这很容易（假设您已经创建了readonlyuser）

db=> GRANT SELECT ON ALL TABLES IN SCHEMA public to readonlyuser;
GRANT
db=> GRANT CONNECT ON DATABASE mydatabase to readonlyuser;
GRANT
db=> GRANT SELECT ON ALL SEQUENCES IN SCHEMA public to readonlyuser;
GRANT

如果您的数据库使用了 customschema，请执行上述操作并添加一个命令：

db=> ALTER USER readonlyuser SET search_path=customschema, public;
ALTER ROLE

- josephmisiti

Answer 10

不太直接的方法是在数据库的每个表上授予选择权限：

postgres=# grant select on db_name.table_name to read_only_user;

你可以通过从数据库元数据生成授权语句来自动化此过程。

- Pablo Santa Cruz