PostgreSQL privilege grant not visible

Question

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

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?

Update

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;

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.

Update: solution

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;

PostgreSQL privilege grant not visible

Update

Update: solution

Answers (1)

Related Questions