4

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?

4

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.