1

I want to have several users able to create schemas and tables. This schemas (and tables) should be available to all. I would like to setup the DB, with all permissions in place (and future permissions using ALTER DEFAULT PRIVILEGES). Users shouldn't have to change any permissions afterwards.

The schemas are created using a client software, QGIS in this case. So, users are only able to issue CREATE SCHEMA newschemaname.

The problem is, even if the users belong to the same group, they are not able to see each other schemas.

Unable to access other's schemas

I'm using two roles for testing. Users admin1 and admin2, both belonging to grp_admin:

CREATE ROLE grp_admin NOLOGIN;
-- one user should be able to create db and extensions; these permissions are not inherited
CREATE ROLE admin1 WITH PASSWORD 'test2k19' LOGIN inherit SUPERUSER CREATEDB CREATEROLE; 
GRANT grp_admin TO admin1;
CREATE ROLE admin2 WITH PASSWORD 'test2k19' LOGIN inherit; 
GRANT grp_admin TO admin2;

As admin1, I created a new database, and granted some permissions (and also permission for future objects):

CREATE DATABASE people;
\c people

-- To create schemas and tables
GRANT ALL PRIVILEGES ON DATABASE people TO grp_admin;

ALTER DEFAULT PRIVILEGES GRANT select, INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO grp_admin;
ALTER DEFAULT PRIVILEGES GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO grp_admin;
ALTER DEFAULT PRIVILEGES GRANT EXECUTE ON FUNCTIONS TO grp_admin;

If, as user admin1 I create a new schema and table, the other admin2 has no access to it:

create schema archive;
create table archive.vip ( thename varchar);
insert into archive.vip values ('Fernão de Magalhães');

As admin2:

select * from archive.vip;
ERROR:  42501: permission denied for schema archive
LINE 1: select * from archive.vip;

Able to access other's schemas

If I use authorization grp_admin when create the schema, it works. As user admin1:

create schema future authorization grp_admin;
create table future.vip ( thename varchar);
insert into future.vip values ('Robot 5126');

As user admin2:

select * from future.vip;
  thename   
------------
 Robot 5126

The problem

Since users are creating schemas with QGIS (a client application), issuing just CREATE SCHEMA newschemaname, users are not able to share each other schemas and tables.

How can I have this second admin2 accessing the admin1 created schemas? (Without issuing GRANT permissions after each schema created).

1

You forgot to set default privileges for schemas:

ALTER DEFAULT PRIVILEGES GRANT CREATE, USAGE ON SCHEMAS TO grp_admin;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.