Thread: 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