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