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

I'd like to create a user in PostgreSQL that can only do SELECTs from a particular database. In MySQL the command would be:

我想在PostgreSQL中创建一个用户，该用户只能从特定的数据库中进行选择。在MySQL中，命令是:

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

What is the equivalent command or series of commands in PostgreSQL?

PostgreSQL中的等效命令或系列命令是什么?

I tried...

我试着…

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

But it appears that the only things you can grant on a database are CREATE, CONNECT, TEMPORARY, and TEMP.

但是似乎您可以在数据库上授予的惟一东西是创建、连接、临时和临时。

9 个解决方案

#1

Grant usage/select to a single table

If you only grant CONNECT to a database, the user can connect but has no other privileges. You have to grant USAGE on namespaces (schemas) and SELECT on tables and views individually like so:

如果只授予连接到数据库，用户可以连接，但没有其他特权。您必须授予名称空间(模式)的使用权限，并对表和视图分别进行选择，如下所示:

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;

Multiple tables/views (PostgreSQL 9.0+)

In the latest versions of PostgreSQL, you can grant permissions on all tables/views/etc in the schema using a single command rather than having to type them one by one:

在最新版本的PostgreSQL中，您可以使用一个命令授予模式中所有表/视图/等等的权限，而不必逐个键入它们:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;

This only affects tables that have already been created. More powerfully, you can automatically have default roles assigned to new objects in future:

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

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.

注意，在默认情况下，这只会影响发出此命令的用户创建的对象(表):尽管它也可以设置在发出该命令的用户属于的任何角色上。但是，当您创建新对象时，您不会为您所属的所有角色获取默认特权……所以还是有一些问题。如果采用的方法是数据库拥有一个拥有的角色，而模式更改被执行为拥有角色，那么您应该为该角色分配默认特权。这有点令人困惑，您可能需要进行一些实验来设计一个功能工作流。

Multiple tables/views (PostgreSQL versions before 9.0)

To avoid errors in lengthy, multi-table changes, it is recommended to use the following 'automatic' process to generate the required GRANT SELECT to each table/view:

为了避免长时间的多表更改中出现错误，建议使用以下“自动”过程为每个表/视图生成所需的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');

This should output the relevant GRANT commands to GRANT SELECT on all tables, views, and sequences in public, for copy-n-paste love. Naturally, this will only be applied to tables that have already been created.

这将输出相关的GRANT命令，以在公共场合的所有表、视图和序列上授予SELECT，以便复制-粘贴。当然，这只适用于已经创建的表。

#2

Do note that PostgreSQL 9.0 (today in beta testing) will have a simple way to do that:

请注意，PostgreSQL 9.0(目前正在进行beta测试)将有一种简单的方法:

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

#3

Here is the best way I've found to add read-only users (using PostgreSQL 9.0 or newer):

这里是我找到的添加只读用户的最佳方法(使用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;

Then log in to all related machines (master + read-slave(s)/hot-standby(s), etc..) and run:

然后登录到所有相关的机器上(master +读-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

#4

Reference taken from this blog:

引用自本博客:

Script to Create Read-Only user:

创建只读用户的脚本:

CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'Test1234' 
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';

Assign permission to this read only user:

为该只读用户分配权限:

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;

#5

I’ve created a convenient script for that; pg_grant_read_to_db.sh. This script grants read-only privileges to a specified role on all tables, views and sequences in a database schema and sets them as default.

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

#6

By default new users will have permission to create tables. If you are planning to create a read-only user, this is probably not what you want.

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

To create a true read-only user with PostgreSQL 9.0+, run the following steps:

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

If your read-only user doesn't have permission to list tables (i.e. \d returns no results), it's probably because you don't have USAGE permissions for the schema. USAGE is a permission that allows users to actually use the permissions they have been assigned. What's the point of this? I'm not sure. To fix:

如果您的只读用户没有列出表的权限(例如，\d没有返回结果)，可能是因为您没有模式的使用权限。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;

#7

If your database is in the public schema, it is easy (this assumes you have already created the readonlyuser)

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

If your database is using customschema, execute the above but add one more command:

如果您的数据库正在使用customschema，请执行上面的操作，但是还要添加一个命令:

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

#8

The not straightforward way of doing it would be granting select on each table of the database:

要做到这一点并不简单，可以在数据库的每个表上提供选择:

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

You could automate that by generating your grant statements from the database metadata.

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

#9

Taken from a link posted in response to despesz' link.

摘自回复desesz链接的链接。

Postgres 9.x appears to have the capability to do what is requested. See the Grant On Database Objects paragraph of:

Postgres 9。x似乎有能力完成所要求的任务。见有关数据库对象的赠款:

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

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

Where it says: "There is also an option to grant privileges on all objects of the same type within one or more schemas. This functionality is currently supported only for tables, sequences, and functions (but note that ALL TABLES is considered to include views and foreign tables)."

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

This page also discusses use of ROLEs and a PRIVILEGE called "ALL PRIVILEGES".

本页面还讨论了角色的使用和称为“所有特权”的特权。

Also present is information about how GRANT functionalities compare to SQL standards.

另外，还有关于授予功能如何与SQL标准相比较的信息。