PostgreSQL 权限授予不可见 [英] PostgreSQL privilege grant not visible

问题描述

在 PostgreSQL 10 上，我有一个名为 tn_schema 的架构和一个名为 tn_beta_db 的数据库.我认为是这样，尽管我必须连接到相关数据库才能查看架构:

On PostgreSQL 10, I've a schema called tn_schema and a database called tn_beta_db. I think so, although I do have to be connected to the relevant database to see the schema:

[T] jeff@nantes-4:~ $ sudo su postgres -c psql
psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1))
Type "help" for help.

postgres=# \l
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
------------+----------+----------+-------------+-------------+-----------------------
 postgres   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | 
 template0  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
 template1  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
 tn_beta_db | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | 
(4 rows)

postgres=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

postgres=# \c tn_beta_db 
You are now connected to database "tn_beta_db" as user "postgres".
tn_beta_db=# \dn
   List of schemas
   Name    |  Owner   
-----------+----------
 public    | postgres
 tn_schema | postgres
(2 rows)

tn_beta_db=# 

现在奇怪的是，我想授予一个用户在这个架构上创建表的权利，并对这些表做任何必要的事情.所以我输入:

Now what's odd is that I want to grant one user the right to create tables on this schema and to do whatever necessary with those tables. So I type this:

tn_beta_db=# ALTER DEFAULT PRIVILEGES IN SCHEMA tn_schema GRANT ALL ON TABLES TO tn_beta_migrator;
ALTER DEFAULT PRIVILEGES
tn_beta_db=# 

这个回应让我觉得一切都很好.虽然当我查询时，

and the response makes me think all's well. Though when I query,

tn_beta_db=# \du
                                       List of roles
    Role name     |                         Attributes                         | Member of 
------------------+------------------------------------------------------------+-----------
 postgres         | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 tn_beta_migrator |                                                            | {}
 tn_beta_reader   |                                                            | {}
 tn_beta_writer   |                                                            | {}

tn_beta_db=# 

看起来我什么都没做.而且，确实，当我以用户 tn_beta_migrator 连接时，我发现我无法在 tn_schema 中创建表:

it doesn't look like I've done anything. And, indeed, when I connect as user tn_beta_migrator, I see I can't create tables in tn_schema:

[T] jeff@nantes-4:~ $ psql --username tn_beta_migrator --host localhost tn_beta_db
Password for user tn_beta_migrator: 
psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1))
Type "help" for help.

tn_beta_db=> create table foo();
CREATE TABLE
tn_beta_db=> drop table foo;
DROP TABLE
tn_beta_db=> create table tn_schema.foo();
ERROR:  permission denied for schema tn_schema
LINE 1: create table tn_schema.foo();
                     ^
tn_beta_db=> 

我会注意到我也试过这个，这不会改变结果:

I'll note that I also tried this, which doesn't change the outcome:

GRANT ALL ON ALL TABLES IN SCHEMA tn_schema TO tn_beta_migrator;

对我误解的内容有任何指示吗?

Any pointers on what I've misunderstood?

我有三个用户，要清楚:一个是只读的，一个是 r/w-only，另一个具有全表修改权限.目标是允许用户创建的表可供读者读取，而由读写器读写.

I have three users, to be clear: one that is read-only, one that is r/w-only, and another with full table modification permissions. The goal is that the tables created by the user allowed to do so are readable by the reader and read-writable by the read-writer.

根据反馈，我发出了以下命令:

Following feedback, I've issued these commands:

    -- Connect to the correct db, as schemas are part of databases.
    \c tn_beta_db

    -- For future tables, these are the privileges I want to see.  Note
    -- that I need to alter the privileges for the role in question,
    -- even though I grant to the role as well.
    ALTER DEFAULT PRIVILEGES IN SCHEMA tn_schema FOR ROLE tn_beta_reader \
        GRANT SELECT ON TABLES TO tn_beta_reader;
    ALTER DEFAULT PRIVILEGES IN SCHEMA tn_schema FOR ROLE tn_beta_writer \
        GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO tn_beta_writer;
    ALTER DEFAULT PRIVILEGES IN SCHEMA tn_schema FOR ROLE tn_beta_migrator \
        GRANT ALL ON TABLES TO tn_beta_migrator;

    -- This wasn't a problem, but to be explicit, I do want
    -- all three roles to be able to connect.
    GRANT CONNECT ON DATABASE tn_beta_db TO tn_beta_reader;
    GRANT CONNECT ON DATABASE tn_beta_db TO tn_beta_writer;
    GRANT CONNECT ON DATABASE tn_beta_db TO tn_beta_migrator;

    -- While I'm doing this at a time when there are not yet any tables
    -- in the database, it would be good to for this script to work
    -- even at some later time.  So here I grant the appropriate rights
    -- to those tables that might already exist, as ALTER DEFAULT
    -- does not change privileges for existing tables.
    GRANT SELECT ON ALL TABLES IN SCHEMA tn_schema TO tn_beta_reader;
    GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA tn_schema TO tn_beta_writer;
    GRANT ALL ON ALL TABLES IN SCHEMA tn_schema TO tn_beta_migrator;

    -- And finally, I note that this role is allowed to create tables.
    GRANT CREATE ON SCHEMA tn_schema TO tn_beta_migrator;

通过这些修改，tn_beta_migrator 能够创建表，但不能从中选择、插入或更新，也不能再次删除它们.

With these modifications, tn_beta_migrator is able to create tables, but can't select, insert, or update from them nor can it drop them again.

根据@laurenz-albe 的建议，我做了一个新的最小示例 这里.这导致了答案.对于那些关注的人，问题是我忘记授予使用权:

Following suggestion by @laurenz-albe, I made a new minimal example here. That led to an answer. For those who follow, the issue was that I'd forgotten to grant usage:

GRANT USAGE ON SCHEMA tn_schema TO tn_beta_migrator;

推荐答案

你误解的事情列表:

1. 更改默认权限 不会更改任何现有对象的权限，在您的情况下是架构.

1. ALTER DEFAULT PRIVILEGES does not change the permissions on any existing object, in your case the schema.

您需要授予CREATE 权限 模式:

You need to grant the CREATE privilege on the schema:

GRANT CREATE ON SCHEMA tn_schema TO tn_beta_migrator;

您运行的 ALTER DEFAULT PRIVILEGES 语句只会影响模式 tn_schema 中用户 postgres 创建的表的权限，但是您似乎希望 tn_beta_migrator 创建表.

The ALTER DEFAULT PRIVILEGES statement you ran will only affect the permissions on tables created by user postgres in schema tn_schema, but it seems that you want tn_beta_migrator to create tables.

您根本不需要ALTER DEFAULT PRIVILEGES，因为创建表的用户将成为表所有者并且默认拥有该表的所有权限.

You don't need ALTER DEFAULT PRIVILEGES at all, since the user that creates the table becomes the table owner and has all privileges on the table by default.

您可以在psql中使用\ddp查看默认权限.

You can view default privileges with \ddp in psql.

架构是数据库的一部分，因此您需要连接到数据库才能查看其架构.

Schemas are part of a database, so you need to connect to the database to see its schemas.

如果您希望 tn_beta_migrator 创建的表在默认情况下获得某些权限，您必须为该用户定义默认权限(而不是为 postgres，就像你一样):

If you want that the tables created by tn_beta_migrator get certain permissions by default, you must define default privileges for that user (and not for postgres, like you did):

ALTER DEFAULT PRIVILEGES FOR ROLE tn_beta_migrator IN SCHEMA tn_schema GRANT ...;

这篇关于PostgreSQL 权限授予不可见的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！