Default privileges not working - Mailing list pgsql-docs
From | atiris@gmail.com |
---|---|
Subject | Default privileges not working |
Date | |
Msg-id | 20160929170830.16874.96462@wrigleys.postgresql.org Whole thread Raw |
Responses |
Re: Default privileges not working
|
List | pgsql-docs |
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.6/static/sql-alterdefaultprivileges.html Description: This is full script for describe problem with default privileges. I want to create some roles in database in tree structure to grant access from "read-only" to most powerfull roles. Then I created user under this roles. Power role has access to create table, but no one except this user can read or write to this table even according documentation this should be possible. I make a mistake somewhere? How I can achieve the desired state in roles? Thanks for the reply. -- CONNECT AS USER: postgres create database test; -- revoke all revoke connect on database test from public; revoke all on schema public from public; revoke all on all tables in schema public from public; -- create readonly role create role "test_readonly" nologin noinherit; grant connect on database test to "test_readonly"; -- schema grant usage on schema public to "test_readonly"; -- tables grant select on all tables in schema public to "test_readonly"; -- create readonly user create role "user_readonly" login encrypted password 'user_readonly' in role "test_readonly"; -- create rw role create role "test_readwrite" nologin inherit; grant "test_readonly" to "test_readwrite"; -- schema -- from readonly -- tables grant insert, update, delete on all tables in schema public to "test_readwrite"; -- create readwrite user create role "user_readwrite" login encrypted password 'user_readwrite' in role "test_readwrite"; -- create power role create role "test_power" nologin inherit; grant "test_readwrite" to "test_power"; -- schema grant all privileges on schema public to "test_power"; -- tables grant all on all tables in schema public to "test_power"; -- create readwrite user create role "user_power" login encrypted password 'user_power' in role "test_power"; -- grant new tables alter default privileges in schema public grant select on tables to "test_readonly", "test_readwrite", "test_power"; alter default privileges in schema public grant insert, update, delete on tables to "test_readwrite", "test_power"; alter default privileges in schema public grant all on tables to "test_power"; -- as postgres i can create table in public schema and insert into it create table a (x numeric); insert into a values (1); select * from a; -- everything ok -- CONNECT AS USER: user_readwrite select * from a; -- ok insert into a values (2); -- ok create table b (x numeric); -- ok: permission denied -- CONNECT AS USER: user_power select * from a; create table b (x numeric); -- ok, created insert into a values (3); insert into b values (4); -- ok, everything like expected -- CONNECT AS USER: user_readwrite select * from b; -- SQL Error [42501]: ERROR: permission denied for relation b -- why? according to grant default privileges on tables for insert update delete and select this user can do any selection insertion or deletion from tables in public schema insert into b values (5); -- SQL Error [42501]: ERROR: permission denied for relation b -- CONNECT AS USER: user_readonly select * from a; -- ok insert into a values (6); -- ok: permission denied select * from b; -- SQL Error [42501]: ERROR: permission denied for relation b -- So no one except user who create table b can read from it. -- But with tables created as USER: postgres, everything is ok. -- How can I use default privileges to grant read to any new tables created to USER readonly. -- And grant all CRUD operations to USER readwrite, and grant delete table by USER power? -- Clean up -- CONNECT AS USER: postgres drop role user_readonly; drop role user_readwrite; drop owned by user_power; drop role user_power; reassign owned by test_readonly to postgres; drop owned by test_readonly; drop role test_readonly; reassign owned by test_readwrite to postgres; drop owned by test_readwrite; drop role test_readwrite; reassign owned by test_power to postgres; drop owned by test_power; drop role test_power; drop database test;
pgsql-docs by date: