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
admin2, both belonging to
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;
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');
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
create schema future authorization grp_admin; create table future.vip ( thename varchar); insert into future.vip values ('Robot 5126');
select * from future.vip; thename ------------ Robot 5126
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).