PostgreSQL: create a schema, fail to grant privileges

Question

I am creating a new database (postgresql v10). If I've not misunderstood, I should create a schema to go with it, because otherwise everything belongs to the public schema and so is accessible to all users by default.

Unfortunately, when I try this, I'm not able to insert or select data or even drop the tables I've created.

(Ultimately, I expect to have three users: one can add and modify tables, one can only read and write data (INSERT, SELECT, UPDATE, DELETE) and one is read-only (SELECT).)

Let's simplify this to its essentials. I start with no database, no schema, and no users.

postgres=# \dl
      Large objects
 ID | Owner | Description 
----+-------+-------------
(0 rows)

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

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

postgres=# 

Now I create the database, connect to it, and create the schema that is associated with the database by virtue of it being the current database. I create one user. (I intend later to create more users, but let's get this working first.)

postgres=# CREATE DATABASE tn_beta_db;
CREATE DATABASE
postgres=# \c tn_beta_db
You are now connected to database "tn_beta_db" as user "postgres".
tn_beta_db=# CREATE SCHEMA tn_schema;
CREATE SCHEMA
tn_beta_db=# CREATE ROLE tn_beta_migrator
tn_beta_db-#     NOSUPERUSER NOCREATEDB NOCREATEROLE
tn_beta_db-#     NOINHERIT LOGIN NOREPLICATION
tn_beta_db-#     NOBYPASSRLS
tn_beta_db-#     PASSWORD 'secretword';
CREATE ROLE
tn_beta_db=# 

Finally, I grant privileges on existing tables (there are none, but maybe it's good form), on future tables, and create permission on the schema. Granting CONNECT privilege doesn't seem to be necessary given LOGIN on the role, but it expresses the intent.

tn_beta_db=# ALTER DEFAULT PRIVILEGES IN SCHEMA tn_schema FOR ROLE tn_beta_migrator GRANT ALL ON TABLES TO tn_beta_migrator;
ALTER DEFAULT PRIVILEGES
tn_beta_db=# GRANT CONNECT ON DATABASE tn_beta_db TO tn_beta_migrator;
GRANT
tn_beta_db=# GRANT ALL ON ALL TABLES IN SCHEMA tn_schema TO tn_beta_migrator;
GRANT
tn_beta_db=# GRANT CREATE ON SCHEMA tn_schema TO tn_beta_migrator;
GRANT
tn_beta_db=# 

Finally, I connect to the database as tn_beta_migrator and try to do something.

[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 tn_schema.foo(x int);
CREATE TABLE
tn_beta_db=> INSERT INTO tn_schema.foo (x) VALUES (1);
ERROR:  permission denied for schema tn_schema
LINE 1: INSERT INTO tn_schema.foo (x) VALUES (1);
                    ^
tn_beta_db=> DROP TABLE tn_schema.foo;
ERROR:  permission denied for schema tn_schema
tn_beta_db=> 

I would have expected, given the grants, that this user on this database would have been able to insert into the table it just created and then to drop the table. But it can't.

Just to be extra clear on the state of the world now:

tn_beta_db=# \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 | =Tc/postgres               +
            |          |          |             |             | postgres=CTc/postgres      +
            |          |          |             |             | tn_beta_migrator=c/postgres
(4 rows)

tn_beta_db=# \dn
   List of schemas
   Name    |  Owner   
-----------+----------
 public    | postgres
 tn_schema | postgres
(2 rows)

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

tn_beta_db=# 

Any pointers on what I've done wrong or misunderstood?

Answer 1

You missed

GRANT USAGE ON SCHEMA tn_schema TO tn_beta_migrator;

I will stick to your detailed example (connected in psql to db postgres as user postgres):

CREATE DATABASE tn_beta_db;
\c tn_beta_db
CREATE SCHEMA tn_schema;
CREATE ROLE tn_beta_migrator NOSUPERUSER NOCREATEDB NOCREATEROLE
NOINHERIT LOGIN NOREPLICATION ;
GRANT CREATE ON SCHEMA tn_schema TO tn_beta_migrator;
GRANT USAGE ON SCHEMA tn_schema TO tn_beta_migrator;
\c tn_beta_db tn_beta_migrator;
CREATE TABLE tn_schema.foo(x int);
INSERT INTO tn_schema.foo (x) VALUES (1);
SELECT * FROM tn_schema.foo;
DROP TABLE tn_schema.foo;