如何在PostgreSQL中创建只读用户? [英] How do you create a read-only user in PostgreSQL?
问题描述
我想在PostgreSQL中创建一个只能从特定数据库执行SELECT的用户.在MySQL中，命令为:
I'd like to create a user in PostgreSQL that can only do SELECTs from a particular database. In MySQL the command would be:
GRANT SELECT ON mydb.* TO 'xxx'@'%' IDENTIFIED BY 'yyy';
PostgreSQL中的等效命令或一系列命令是什么?
What is the equivalent command or series of commands in PostgreSQL?
我尝试过...
postgres=# CREATE ROLE xxx LOGIN PASSWORD 'yyy';
postgres=# GRANT SELECT ON DATABASE mydb TO xxx;
但是，您似乎只能在数据库上授予的权限是CREATE，CONNECT，TEMPORARY和TEMP.
But it appears that the only things you can grant on a database are CREATE, CONNECT, TEMPORARY, and TEMP.
推荐答案
允许使用/选择单个表
如果仅将CONNECT授予数据库，则用户可以连接，但没有其他特权.您必须像这样分别授予对名称空间(schemas)的USAGE和对表和视图的SELECT的权限:
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;
多个表/视图(PostgreSQL 9.0 +)
在最新版本的PostgreSQL中，您可以使用单个命令来授予模式中所有表/视图/等的权限，而不必一个个地键入它们:
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:
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.
为避免冗长的多表更改出错，建议使用以下自动"过程为每个表/视图生成所需的GRANT SELECT:
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:
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粘贴.自然，这只会应用于已经创建的表.
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.
这篇关于如何在PostgreSQL中创建只读用户?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
