如何在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。

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中，您可以使用单个命令授予对模式中所有表/视图/等的权限，而不必一个个地键入它们：

GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;

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

ALTER DEFAULT PRIVILEGES IN SCHEMA public

   GRANT SELECT ON TABLES TO xxx;

请注意，默认情况下，这仅会影响发出此命令的用户创建的对象（表）：尽管也可以在发出用户所属的任何角色上进行设置。但是，在创建新对象时，您不会为您所隶属的所有角色选择默认特权...因此仍然存在一些麻烦。如果采用数据库具有所有者角色的方式，并且模式更改是作为该所有者角色执行的，则应为该所有者角色分配默认特权。恕我直言，这有点令人困惑，您可能需要进行实验以提出功能正常的工作流程。

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

为了避免冗长的多表更改中的错误，建议使用以下“自动”过程来生成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');

这应该将所有GRANT命令的相关GRANT命令公开输出到所有表，视图和序列上，以实现复制n粘贴。自然，这将仅应用于已经创建的表。

Answer 2

请注意，PostgreSQL 9.0（今天处于beta测试中）将有一种简单的方法：

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

GRANT

Answer 3

创建只读用户的脚本：

CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'Test1234' 

NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';

为该只读用户分配权限：

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;

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO Read_Only_User;