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

将使用/选择授予单个表

如果您仅将 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;

这只会影响已经创建的表。更强大的是，您将来可以自动拥有 default roles assigned to new objects：

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 SELECT，以便进行复制粘贴。自然，这只适用于已经创建的表。

---

Reference taken from this blog:

创建只读用户的脚本：

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;

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

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

---

请注意，PostgreSQL 9.0（今天处于 beta 测试阶段）将有一个 simple way to do that：

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

---

从 PostgreSQL v14 开始，您只需授予预定义的 pg_read_all_data 角色即可：

GRANT pg_read_all_data TO xxx;

---

这是我发现添加只读用户的最佳方法（使用 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;

然后登录所有相关机器（master + read-slave(s)/hot-standby(s)等）并运行：

$ 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

---

默认情况下，新用户将有权创建表。如果您打算创建一个只读用户，这可能不是您想要的。

要使用 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;

---

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

---

我阅读了所有可能的解决方案，一切都很好，如果你记得在授予东西之前连接到数据库;）无论如何感谢所有其他解决方案！

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 cli 并为创建的用户设置密码：

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

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

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

---

如果您的数据库在公共架构中，这很容易（假设您已经创建了 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

---

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

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

您可以通过从数据库元数据生成授权语句来自动执行此操作。

---

取自为响应 despesz' 链接而发布的链接。

Postgres 9.x 似乎有能力做所要求的事情。请参阅授予数据库对象的段落：

http://www.postgresql.org/docs/current/interactive/sql-grant.html

它说：“还有一个选项可以授予对一个或多个模式中相同类型的所有对象的权限。此功能目前仅支持表、序列和函数（但请注意，所有表都被认为包括视图）和外国表）。”

本页还讨论了 ROLE 和称为“ALL PRIVILEGES”的 PRIVILEGE 的使用。

还提供了有关 GRANT 功能如何与 SQL 标准进行比较的信息。

---

CREATE USER username SUPERUSER  password 'userpass';
ALTER USER username set default_transaction_read_only = on;

---