ALTER DEFAULT PRIVILEGES - permission denied

Question

I have a problem with ALTER DEFAULT PRIVILEGES. I grant the permission:

ALTER DEFAULT PRIVILEGES 
    FOR USER user_name
    IN SCHEMA schema_name
    GRANT SELECT ON TABLES TO user_name;

in next step I create a table

create table schema_name.a (q int);

Now, I connect to dabasabe using conn_user and try to read the data in the new table.

select * from schema_name.a

and I receive the error:

SQL Error [42501]: ERROR: permission denied for relation a
  org.postgresql.util.PSQLException: ERROR: permission denied for relation a

Do you have any ideas why the error appears?

dezso · Accepted Answer · 2017-09-25 15:44:43Z

The problem here is the ALTER DEFAULT PRIVILEGES statement you issued will apply only to tables that are created by user_name:

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

and

target_role

The name of an existing role of which the current role is a member. If FOR ROLE is omitted, the current role is assumed.

So in your case you either use

ALTER DEFAULT PRIVILEGES 
    IN SCHEMA schema_name
    GRANT SELECT ON TABLES TO user_name;

(without the FOR USER clause), or create the table as user_name (in which case they'll be the owner, therefore no need to grant anything).

In any case, it's a good practice to always create DB objects as a given user, so that the access privileges are predictable.

leon de vries · Accepted Answer · 2019-10-16 13:57:30Z

To grant default privileges, U actually need to grant rights to the user you are creating the table with.

e.g.: you're creating the tables as SA_user, but reading the tables als READ_user. you're code needs to look like:

ALTER DEFAULT PRIVILEGES 
FOR USER SA_user
IN SCHEMA schema_name
GRANT SELECT ON TABLES TO READ_user;

So whenever the SA_user creates a table, it will grant select rights for the READ_user.

2 Answers 2