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
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 |