Postgresql 12 - ddluser and dmluser for same schema dml permission denied on tables [duplicate]

Question

I have created a postgres 12 instance and executed the below command sequence:

psql --host localhost -U postgres -c "CREATE DATABASE mydb"
psql --host localhost -U postgres -d mydb -f /tmp/createdb.sql

The need is to create tables with ddluser and the dmluser can manipulate(insert/select/delete) the contents of the tables that ddluser will create.

The contents of /tmp/createdb.sql are the following:

DROP USER IF EXISTS ddluser;
DROP USER IF EXISTS dmluser;
DROP ROLE IF EXISTS ddlrole;
DROP ROLE IF EXISTS dmlrole;
CREATE USER ddluser WITH PASSWORD '1234';
CREATE USER dmluser WITH PASSWORD '1234';
CREATE SCHEMA myschema;
REVOKE CREATE ON SCHEMA myschema FROM PUBLIC;
REVOKE ALL ON DATABASE mydb FROM PUBLIC;
CREATE ROLE ddlrole;
GRANT CONNECT ON DATABASE mydb TO ddlrole;
GRANT USAGE, CREATE ON SCHEMA myschema TO ddlrole;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA myschema FROM ddlrole;
GRANT ddlrole TO ddluser;
CREATE ROLE dmlrole;
GRANT CONNECT ON DATABASE mydb TO dmlrole;
GRANT USAGE ON SCHEMA myschema TO dmlrole;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO dmlrole;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dmlrole;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO dmlrole;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO dmlrole;
GRANT dmlrole TO dmluser;

The table I create for example is the below:

CREATE TABLE myschema.accounts (
user_id serial PRIMARY KEY,
username VARCHAR ( 50 ) UNIQUE NOT NULL,
password VARCHAR ( 50 ) NOT NULL,
email VARCHAR ( 255 ) UNIQUE NOT NULL,
created_on TIMESTAMP NOT NULL,
    last_login TIMESTAMP );

Output is:

mydb=> select * from myschema.accounts;
ERROR:  permission denied for table accounts
mydb=> exit

Answer 1

Assuming that:

• the database user running the commands in /tmp/createdb.sql is a superuser (let's say it's postgres)

• the database user running the CREATE TABLE later is ddluser

Your expectation is probably that this command run by the superuser: ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dmlrole; will make every object created in myschema have these permissions.

But in fact only objects created by postgres would be affected, because, according to the documentation for ALTER DEFAULT PRIVILEGES:

You can change default privileges only for objects that will be created by yourself or by roles that you are a member of.

(yourself in this context means of course the current db user)

The simplest solution is to switch temporarily to ddlrole in the creation script.

[... statements run by the superuser...]

SET ROLE ddlrole;

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dmlrole;
    
RESET ROLE;

[... rest of statements run by the superuser...]

Then the tables created in the future in myschema will have these permissions when they're created by a member of ddlrole.