postgresql PostgreSQL中如何创建只读用户？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/760210/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you create a read-only user in PostgreSQL?
提问by Ethan
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 中创建一个只能从特定数据库执行 SELECT 的用户。在 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.
但似乎您可以对数据库授予的唯一权限是 CREATE、CONNECT、TEMPORARY 和 TEMP。
回答by araqnid
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:
如果您只将 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;
Multiple tables/views (PostgreSQL 9.0+)
多个表/视图（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 objectsin 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)
多个表/视图（PostgreSQL 9.0 之前的版本）
To avoid errors in lengthy, multi-table changes, it is recommended to use the following 'automatic' process to generate the required GRANT SELECTto 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 命令输出到公共所有表、视图和序列上的 GRANT SELECT，以便复制粘贴。当然，这只会应用于已经创建的表。
回答by bortzmeyer
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;
回答by Anvesh
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;
回答by Jay Taylor
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:
然后登录所有相关机器（主+读-从/热备等）并运行：
$ 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
回答by Adrian Macneil
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. \dreturns no results), it's probably because you don't have USAGEpermissions for the schema. USAGEis 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该架构的权限。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;
回答by Jakub Jirutka
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。此脚本授予指定角色对数据库模式中所有表、视图和序列的只读权限，并将它们设置为默认值。
回答by josephmisiti
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
回答by thomi_ch
I read trough all the possible solutions, which are all fine, if you remember to connect to the database before you grant the things ;) Thanks anyway to all other solutions!!!
我阅读了所有可能的解决方案，如果您记得在授予之前连接到数据库，那么这些解决方案都很好；) 无论如何感谢所有其他解决方案！！！
user@server:~$ sudo su - postgres
create psql user:
创建 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
start psql cli and set a password for the created user:
启动 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
connect to the target database:
连接到目标数据库：
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".
grant all the needed privileges:
授予所有需要的权限：
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
alter default privileges for targets db public shema:
更改目标 db public shema 的默认权限：
target_database=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES
回答by Viktor Viktor
CREATE USER username SUPERUSER password 'userpass';
ALTER USER username set default_transaction_read_only = on;
回答by Pablo Santa Cruz
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.
您可以通过从数据库元数据生成授权语句来自动执行此操作。
