Default privileges not working

Поиск
Список
Период
Сортировка
От atiris@gmail.com
Тема Default privileges not working
Дата
Msg-id 20160929170830.16874.96462@wrigleys.postgresql.org
обсуждение исходный текст
Ответы Re: Default privileges not working  (Stephen Frost <sfrost@snowman.net>)
Список 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 по дате отправления:

Предыдущее
От: Assaf Gordon
Дата:
Сообщение: suggestion: add explicit example for COPY of new-vs-old syntax
Следующее
От: Stephen Frost
Дата:
Сообщение: Re: Default privileges not working