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 toreadrole ;
grant select on all tables in schema public to
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 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 asreadrole
9.) login as
select 1 from taba; --> can access
Comments
Post a Comment