Skip to main content

Default privileges Don't work when owner changes

One of the first things you learn in postgres is the importance of getting the default privileges configured. Coming from the SQLserver background, I found having to assign default privileges a little precarious but once I got over that hump it has become more obvious for its place in PG. 

The other day I discovered that default privileges don't get inherited of the new owner when the ownership changes of an object.

1.) login as postgres 
create table tab(id int) 

2.) login as d_owner 
create table taba(id int)

3.) Grant permission for readonly role access data from the public schema owned by d_owner
grant select on all tables in schema public to readrole;
alter default privileges for user  d_owner   in schema public grant select on tables to feedsapi_readonly_role

4.) Change the owner of tab from postgres to d_owner
      Alter table tab owner d_owner 
5.) login as readrole
     select 1 from tab;  --> can access 
      select 1 from taba;  --> permission issue 

6.)  Login as  d_owner  
      create table tabb(id int)
    
7.)   login as readrole   
        select 1 from tabb;  --> can access   

8.)  Login as  d_owner   
       grant select on all tables in schema public to readrole;

9.)  login as readrole   
     select 1 from taba;  -->  can access     


Comments