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

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

您可以通过从数据库元数据生成授权语句来实现自动化。

将使用/选择授权给单个表

如果只向数据库授予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;

Note that by default this will only affect objects (tables) created by the user that issued this command: although it can also be set on any role that the issuing user is a member of. However, you don't pick up default privileges for all roles you're a member of when creating new objects... so there's still some faffing around. If you adopt the approach that a database has an owning role, and schema changes are performed as that owning role, then you should assign default privileges to that owning role. IMHO this is all a bit confusing and you may need to experiment to come up with a functional workflow.

多个表/视图(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');

这应该在公共的所有表、视图和序列上输出相关的GRANT命令到GRANT SELECT，以实现复制-粘贴。当然，这将只应用于已经创建的表。

请注意PostgreSQL 9.0(今天在beta测试中)将有一个简单的方法来做到这一点:

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

摘自回复despesz的链接。

Postgres 9。X似乎有能力完成所请求的事情。参见Grant On Database Objects段落:

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

它说:“还有一个选项可以授予一个或多个模式中相同类型的所有对象的特权。该功能目前仅支持表、序列和函数(但请注意，ALL tables被认为包括视图和外部表)。”

本页还讨论了角色和称为“ALL PRIVILEGES”的特权的使用。

同时还介绍了GRANT功能与SQL标准的比较情况。

下面是我发现的添加只读用户的最好方法(使用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

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

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

以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;

参考本博客:

创建只读用户脚本:

CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'Test1234' 
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';
\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;
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;

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

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

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

修改目标db public shema的默认权限:

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

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

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

GRANT pg_read_all_data TO xxx;