Postgresql – Alter default privileges for a certain column

postgresql

I want a RoleGroup to have certain privileges on a specific column in a table ….the roleGroup not DB owner :

grant  SELECT, INSERT ,UPDATE  (colOne) on table schemaOne.tableOne 
to roleGroup;

When I want alter default privileges of this column (to include future users), I get ERROR saying can’t alter default privileges of one column only :

alter default privileges for role DB_OWNER in schema schemaOne grant 
select,insert,update(colOne) on table schemaOne.tableOne to 
roleGroup;

which forced me to do this :

Alter Default priviliges for role DB_OWNER in schema schemOne grant 
select,insert,update to roleGroup ;

what’s the point of the first grant then ?!! Or am I making some mistake ?

Best Answer

"alter default privileges" applies to newly (in the future) created tables, not newly created users/roles. It generally doesn't make much sense to grant a column specific privilege on a table which does not yet exist, as it might not even have that column once the table is created.

If new users are granted the roleGroup role, then they will automatically have the desired permission on the existing column of the existing table.

I don't think there is a way to automatically grant a role to newly created users, you will have to remember to do it yourself, or script it.

Related Question