Re: Restricted access on DataBases - Mailing list pgsql-general

Hello

 

 

Also try this:

 

ALTER DEFAULT PRIVILEGES FOR ex_mainuser GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO ex_dbuser;

 

You execute the ALTER DEFAULT PRIVILEGES as su, so the grant applies to objects created by su and not ex_mainuser, unless you specify it with FOR ex_mainuser.

 

Besides, if the objects in the table will not be created by the owner, but by your admin, then I don’t very much see the point in giving ownership. That could be done anyway in the public schema, unless you changed that.

--- login with postgres:

CREATE DATABASE db_testrole
  WITH ENCODING='UTF8'
       TEMPLATE=template0      
      CONNECTION LIMIT=-1;

CREATE ROLE u_tr_db LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

CREATE ROLE u_tr_main LOGIN
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT u_tr_db TO u_tr_main;

ALTER DATABASE db_testrole
  OWNER TO u_tr_db;

REVOKE ALL ON DATABASE db_testrole FROM public;
GRANT CREATE, TEMPORARY ON DATABASE db_testrole TO public;
GRANT ALL ON DATABASE db_testrole TO u_tr_db;

ALTER DEFAULT PRIVILEGES
    GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES
    TO u_tr_db;

create table t_canyouseeme_1 (k int);

select * from t_canyouseeme_1;

ERROR: permission denied for relation t_canyouseeme_1
SQL state: 42501

A.)

---- login with u_tr_main:

set role u_tr_db;

create table t_canyouseeme_2 (k int);

---- login with u_tr_db:

select * from t_canyouseeme_2; -- OK!

B.)

---- login with su:

ALTER DEFAULT PRIVILEGES FOR role u_tr_main  GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO u_tr_db;

---- login with u_tr_main:

create table t_canyouseeme_3 (k int);

---- login with u_tr_db:

select * from t_canyouseeme_3; -- OK!