Обсуждение: Default privileges not working
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;
Greetings, * atiris@gmail.com (atiris@gmail.com) wrote: > Thanks for the reply. [...] > 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"; Default privileges are assigned to roles. In other words, you can only say "tables created by user X have default privileges Y." If you omit the user from the ALTER DEFAULT PRIVILEGES command, then the CURRENT_USER is used. Use: \ddp in psql to see the default privileges created and which user they are associated with. My guess is that in the above scenario, default privileges were only set up for the 'postgres' user. > -- 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 There were no default privileges set up for the "user_power" role and, therefore, when that role created a table, no privileges were set for it. That's why the query by user_readwrite failed. Try doing this first: ALTER DEFAULT PRIVILEGES FOR user_power IN SCHEMA PUBLIC GRANT ... And then creating a table as the "user_power" role. > -- 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? Assign default privileges for all roles which will be creating objects. Thanks! Stephen
Вложения
Jozef, * Jozef Pažin (atiris@gmail.com) wrote: > -- grant for new tables > -- only users "postgres" and "test_power" can create tables; > alter default privileges for role "test_power" in schema public grant > select on tables to "test_readonly", "test_readwrite", "test_power"; > alter default privileges for role "test_power" in schema public grant > insert, update, delete on tables to "test_readwrite", "test_power"; > alter default privileges for role "test_power" in schema public grant all > on tables to "test_power"; > > alter default privileges for user "postgres" in schema public grant select > on tables to "test_readonly", "test_readwrite", "test_power"; > alter default privileges for user "postgres" in schema public grant insert, > update, delete on tables to "test_readwrite", "test_power"; > alter default privileges for user "postgres" in schema public grant all on > tables to "test_power"; Above, you set default privileges for the 'postgres' and the 'test_power' roles, however... > -- CONNECT AS USER: user_power Here, you are connecting as the 'user_power' role, for which no default privileges were set. > select * from a; > create table b (x numeric); -- ok This table is created as the 'user_power' role and, since there were no default privileges set for this role, it is created with no privileges granted. Leading to... > -- CONNECT AS USER: user_readwrite > select * from b; -- wrong -- SQL Error [42501]: ERROR: permission denied > for relation b > insert into b values (5); -- wrong -- SQL Error [42501]: ERROR: permission > denied for relation b These permission denied errors, which are entirely correct because no default privileges were set for the case where the 'user_power' role creates objects in the 'public' schema. Please use \dp to see what the privileges are after object creation, and use \ddp to see what the default privileges will be for objects created by which roles in which schemas. > -- 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? You must set up default privileges for all roles which will be creating objects. Above, you only set them for the 'postgres' role and the 'test_power' role, but then the 'user_power' role created objects. One approach to dealing with this is to have fewer roles which can create objects and then require users to do a 'SET ROLE' prior to creating an object, eg: CONNECT AS USER: user_power SET ROLE test_power; CREATE TABLE b (a int); The above action creates the table as the 'test_power' role and therefore the default privileges for the 'test_power' role will be applied to all newly created objects. Thanks! Stephen
Вложения
Hi Stephen,
thanks for your help, I tried it, but without success.
I think there is small typo in your proposal, you need to set
USER or ROLE keyword in ALTER DEFAULT PRIVILEGES:
> Default privileges are assigned to roles. In other words, you can only
> say "tables created by user X have default privileges Y." If you omit
> the user from the ALTER DEFAULT PRIVILEGES command, then the
> CURRENT_USER is used.
FOR { ROLE | USER } target_role
But even after adjustment I can not achieve the desired state.
After I rewrite script according your proposal. I still get the same
errors. Now I enclose with errors also output from dds command.
You can here find again full script to avoid any misunderstandings
what I was run. I run only this commands in this order and my
DB version is: PostgreSQL 9.5.4 on x86_64-pc-linux-gnu
Again thanks for your reply.
-- CONNECT AS USER: postgres
create database test;
-- CONNECT AS USER: postgres -- ON 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 for new tables
-- only users "postgres" and "test_power" can create tables;
alter default privileges for role "test_power" in schema public grant select on tables to "test_readonly", "test_readwrite", "test_power";
alter default privileges for role "test_power" in schema public grant insert, update, delete on tables to "test_readwrite", "test_power";
alter default privileges for role "test_power" in schema public grant all on tables to "test_power";
alter default privileges for user "postgres" in schema public grant select on tables to "test_readonly", "test_readwrite", "test_power";
alter default privileges for user "postgres" in schema public grant insert, update, delete on tables to "test_readwrite", "test_power";
alter default privileges for user "postgres" in schema public grant all on tables to "test_power";
-- I tried also this (both roles in one command) with the same result:
-- alter default privileges for role "test_power", "postgres" in schema
-- public grant select on tables to "test_readonly", "test_readwrite", "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
delete from a where x = 1; -- ok
create table b (x numeric); -- ok: permission denied
drop table a; -- ok: permission denied
-- CONNECT AS USER: user_power
select * from a;
create table b (x numeric); -- ok
insert into a values (3); -- ok
insert into b values (4); -- ok
-- CONNECT AS USER: user_readwrite
select * from b; -- wrong -- SQL Error [42501]: ERROR: permission denied for relation b
insert into b values (5); -- wrong -- SQL Error [42501]: ERROR: permission denied for relation b
-- Output from console:
-- psql -d test
-- \ddp
--
-- Default access privileges
-- Owner | Schema | Type | Access privileges
-- ------------+--------+-------+--------------------------------
-- postgres | public | table | test_readonly=r/postgres +
-- | | | test_readwrite=arwd/postgres +
-- | | | test_power=arwdDxt/postgres
-- test_power | public | table | test_readonly=r/test_power +
-- | | | test_readwrite=arwd/test_power+
-- | | | test_power=arwdDxt/test_power
-- (2 rows)
-- CONNECT AS USER: user_readonly
select * from a; -- ok
insert into a values (6); -- ok: permission denied
select * from b; -- wrong -- SQL Error [42501]: ERROR: permission denied for relation b
-- CONNECT AS USER: user_power
drop table a; -- wrong -- SQL Error [42501]: ERROR: must be owner of relation a
-- user_power has granted from test_power: all privileges on schema public and all on all tables in schema public
-- so why he can not drop table a?
alter table a owner to "user_power"; -- and he can not set new owner also.
drop table b; -- ok
-- What is wrong?
-- No one except user who create table b can read from it.
-- But with tables created as USER "postgres", everything is ok.
-- Even "user_power" can not remove tables created by "postgres".
-- 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 database test;
drop role user_readonly;
drop role user_readwrite;
drop owned by user_power;
reassign owned by user_power to postgres;
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;
Regards
Jozef
thanks for your help, I tried it, but without success.
I think there is small typo in your proposal, you need to set
USER or ROLE keyword in ALTER DEFAULT PRIVILEGES:
> Default privileges are assigned to roles. In other words, you can only
> say "tables created by user X have default privileges Y." If you omit
> the user from the ALTER DEFAULT PRIVILEGES command, then the
> CURRENT_USER is used.
FOR { ROLE | USER } target_role
But even after adjustment I can not achieve the desired state.
After I rewrite script according your proposal. I still get the same
errors. Now I enclose with errors also output from dds command.
You can here find again full script to avoid any misunderstandings
what I was run. I run only this commands in this order and my
DB version is: PostgreSQL 9.5.4 on x86_64-pc-linux-gnu
Again thanks for your reply.
-- CONNECT AS USER: postgres
create database test;
-- CONNECT AS USER: postgres -- ON 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 for new tables
-- only users "postgres" and "test_power" can create tables;
alter default privileges for role "test_power" in schema public grant select on tables to "test_readonly", "test_readwrite", "test_power";
alter default privileges for role "test_power" in schema public grant insert, update, delete on tables to "test_readwrite", "test_power";
alter default privileges for role "test_power" in schema public grant all on tables to "test_power";
alter default privileges for user "postgres" in schema public grant select on tables to "test_readonly", "test_readwrite", "test_power";
alter default privileges for user "postgres" in schema public grant insert, update, delete on tables to "test_readwrite", "test_power";
alter default privileges for user "postgres" in schema public grant all on tables to "test_power";
-- I tried also this (both roles in one command) with the same result:
-- alter default privileges for role "test_power", "postgres" in schema
-- public grant select on tables to "test_readonly", "test_readwrite", "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
delete from a where x = 1; -- ok
create table b (x numeric); -- ok: permission denied
drop table a; -- ok: permission denied
-- CONNECT AS USER: user_power
select * from a;
create table b (x numeric); -- ok
insert into a values (3); -- ok
insert into b values (4); -- ok
-- CONNECT AS USER: user_readwrite
select * from b; -- wrong -- SQL Error [42501]: ERROR: permission denied for relation b
insert into b values (5); -- wrong -- SQL Error [42501]: ERROR: permission denied for relation b
-- Output from console:
-- psql -d test
-- \ddp
--
-- Default access privileges
-- Owner | Schema | Type | Access privileges
-- ------------+--------+-------+--------------------------------
-- postgres | public | table | test_readonly=r/postgres +
-- | | | test_readwrite=arwd/postgres +
-- | | | test_power=arwdDxt/postgres
-- test_power | public | table | test_readonly=r/test_power +
-- | | | test_readwrite=arwd/test_power+
-- | | | test_power=arwdDxt/test_power
-- (2 rows)
-- CONNECT AS USER: user_readonly
select * from a; -- ok
insert into a values (6); -- ok: permission denied
select * from b; -- wrong -- SQL Error [42501]: ERROR: permission denied for relation b
-- CONNECT AS USER: user_power
drop table a; -- wrong -- SQL Error [42501]: ERROR: must be owner of relation a
-- user_power has granted from test_power: all privileges on schema public and all on all tables in schema public
-- so why he can not drop table a?
alter table a owner to "user_power"; -- and he can not set new owner also.
drop table b; -- ok
-- What is wrong?
-- No one except user who create table b can read from it.
-- But with tables created as USER "postgres", everything is ok.
-- Even "user_power" can not remove tables created by "postgres".
-- 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 database test;
drop role user_readonly;
drop role user_readwrite;
drop owned by user_power;
reassign owned by user_power to postgres;
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;
Regards
Jozef