Postgresql – ALTER DEFAULT PRIVILEGES – permission denied

permissionspostgresql

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?

Best Answer

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.

Related Question