CODEWITHSUNDEEP
sqldatabasepostgresql
Matthew
Matthew

Reputation: 1615

Is there a way to grant permissions on tables that don't exist yet (or that get recreated)?

I have a database with a read-only role set up; let's call it 'app_user_ro'. I am using a third party data loading tool (running as 'app_user') that drops and recreates the tables. After recreating the tables, the previous permissions are gone and 'app_user_ro' can no longer select from the tables.

I am using Postgres 11.3 running on Ubuntu 19.04. I've looked at Grant privileges on future tables in PostgreSQL? and tried altering the default privileges but still 'app_user_ro' can't read data from the tables after they are dropped and recreated.

I've tried: 

-- The accepted answer
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_user_ro;

-- An alternative answer from the comments:
ALTER DEFAULT PRIVILEGES FOR USER app_user_ro IN SCHEMA public GRANT SELECT ON TABLES TO app_user_ro;

The only thing that works is manually granting the permissions after each import: 

GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_user_ro;

The docs state:

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

The roles I had were: 

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

Originally I was running the ALTER DEFAULT... commands as the 'postgres' role but more recently I added the 'app_user_ro' role to the 'app_user' role (the one loading the data) and tried running it as the 'app_user' role with no change in behaviour.

For now I'll just schedule a job to grant the permissions after each import but it seems like there should be a better way!

Upvotes: 3

Views: 3012

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246033

The puzzle piece you are missing is that ALTER DEFAULT PRIVILEGES only affects tables that get created by the role specified in the FOR USER/ROLE clause (or, if you omit that, by the user who ran the statement).

So if it is app_user that creates the tables, you'll have to run

ALTER DEFAULT PRIVILEGES FOR ROLE app_user ...;

Upvotes: 4

Related Questions