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

9 回答

• 524

  将使用/选择授予单个表

  如果仅将CONNECT授予数据库，则用户可以连接但没有其他权限 . 您必须单独为表和名称上的名称空间（模式）和SELECT授予USAGE，如下所示：

  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版本）

  为避免冗长的多表更改中的错误，建议使用以下'automatic'进程为每个表/视图生成所需的 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，用于复制粘贴爱 . 当然，这只适用于已经创建的表 .

  回复于 2024-04-20T11:38:05+08:00
• 33

  请注意，PostgreSQL 9.0（今天在beta测试中）将有一个simple way to do that：

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

  回复于 2024-04-20T11:38:05+08:00
• 8

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

  $ 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
  

  回复于 2024-04-20T11:38:05+08:00
• 14

  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;
  

  回复于 2024-04-20T11:38:05+08:00
• 2

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

  要使用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 不返回任何结果），则's probably because you don' t对该模式具有 USAGE 权限 . USAGE 是一项权限，允许用户实际使用已分配的权限 . 什么's the point of this? I'我不确定 . 修理：

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

  回复于 2024-04-20T11:38:05+08:00
• 21

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

  回复于 2024-04-20T11:38:05+08:00
• 9

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

  回复于 2024-04-20T11:38:05+08:00
• 1

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

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

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

  回复于 2024-04-20T11:38:05+08:00
• 0

  取自发布以回复despesz'链接的链接 .

  Postgres 9.x似乎有能力做所要求的事情 . 请参阅Grant On Database Objects段落：

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

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

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

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

  回复于 2024-04-20T11:38:05+08:00