ALTER DEFAULT PRIVILEGES FOR USER in error

From: DECHERF Étienne <etienne(dot)decherf-ext(at)aphp(dot)fr>
To: "pgsql-fr-generale(at)lists(dot)postgresql(dot)org" <pgsql-fr-generale(at)lists(dot)postgresql(dot)org>
Subject: ALTER DEFAULT PRIVILEGES FOR USER in error
Date: 2018-10-17 13:40:49
Message-ID: 35B45AE5854FD442A1775EB1337F9701C8B322@BBS-EXCMBX-P005.wprod.ds.aphp.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-fr-generale

Hello,

I 've created a PostgreSQL user "John" with SELECT grants on it.
"ALTER DEFAULT PRIVILEGES FOR USER" command hungs in my script above :

Here is how I created a "John" user :

CREATE ROLE "john";
REVOKE ALL PRIVILEGES ON DATABASE pgdb FROM "john";
ALTER ROLE "john" WITH LOGIN;
ALTER ROLE "john" WITH PASSWORD 'johnpassword' VALID UNTIL '2018-12-31';
GRANT CONNECT ON DATABASE dbpg TO "john";
GRANT USAGE ON SCHEMA schema1 TO "john";
GRANT USAGE ON SCHEMA schema2 TO "john";

-- to revoke SELECT on 2 tables on 2 schemas : the 4 tables must be unavailable :

REVOKE SELECT ON TABLE schema1.table1 TO "john";
REVOKE SELECT ON TABLE schema1.table2 TO "john";

REVOKE SELECT ON TABLE schema2.table3 TO "john";
REVOKE SELECT ON TABLE schema2.table4 TO "john";

-- I would like John may CREATE his TABLES and may SELECT and UPDATE his tables (to write):
ALTER DEFAULT PRIVILEGES FOR USER "john" IN SCHEMA schema1, schema2 GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO "john" ;
-- but I've received a message like this one above :

Results :
John can login and connect to the database : success
John can't access any 4 tables on the 2 schemas (REVOKES) : success
John can access in read_only (SELECT) on the other tables of schema1 and schema2 : success

John can't create any tables in any schema : error
He should be able to create objects (tables, index,...) in schemas schema1 and schema2, with read / write access on them.
Here are retuned error messages in each schema :
SQL Error [42501]: ERROR: permission denied for schema schema1
Position: 14
ERROR: permission denied for schema schema1
Position: 14
ERROR: permission denied for schema schema1
Position: 14

SQL Error [42501]: ERROR: permission denied for schema schema2
Position: 14
ERROR: permission denied for schema schema2
Position: 14
ERROR: permission denied for schema schema2
Position: 14

Remark :
ALTER DEFAULT PRIVILEGES FOR USER has here the whole possible grants (SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER).

Can you see why John can't create table ?

Thanks a lot for your help !

Etienne DECHERF
DBA Senior
etienne(dot)decherf-ext(at)aphp(dot)fr
+33 6 67462246

Responses

Browse pgsql-fr-generale by date

  From Date Subject
Next Message talk to ben 2018-10-17 13:52:57 Re: ALTER DEFAULT PRIVILEGES FOR USER in error
Previous Message Flavio Henrique Araque Gurgel 2018-10-08 14:19:35 Re: Consommation d'espace d'autovacuum