ALTER DEFAULT PRIVILEGES FOR USER in error
DECHERF Étienne <[email protected]> Wed, 17 Oct 2018 13:40:49 +0000
| Newsgroups | gmane.comp.db.postgresql.french |
|---|---|
| Message-ID | <35B45AE5854FD442A1775EB1337F9701C8B322@BBS-EXCMBX-P005.wprod.ds.aphp.fr> |
--_000_35B45AE5854FD442A1775EB1337F9701C8B322BBSEXCMBXP005wpro_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 unavail= able : 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 ta= bles (to write): ALTER DEFAULT PRIVILEGES FOR USER "john" IN SCHEMA schema1, schema2 GRANT S= ELECT, 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 sc= hema2 : 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 (SELEC= T, 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 [email protected] +33 6 67462246 --_000_35B45AE5854FD442A1775EB1337F9701C8B322BBSEXCMBXP005wpro_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <html dir=3D"ltr"> <head> <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-8859-= 1"> <style type=3D"text/css" id=3D"owaParaStyle">P {margin-top:0;margin-bottom:= 0;}</style> </head> <body fpstyle=3D"1" ocsi=3D"0"> <div style=3D"direction: ltr;font-family: Tahoma;color: #000000;font-size: = 10pt;">Hello,<br> <br> I 've created a PostgreSQL user "John" with SELECT grants on it.<= br> "ALTER DEFAULT PRIVILEGES FOR USER" command hungs in my script ab= ove :<br> <br> Here is how I created a "John" user :<br> <br> CREATE ROLE "john";<br> REVOKE ALL PRIVILEGES ON DATABASE pgdb FROM "john";<br> ALTER ROLE "john" WITH LOGIN;<br> ALTER ROLE "john" WITH PASSWORD 'johnpassword' VALID UNTIL '2018-= 12-31';<br> GRANT CONNECT ON DATABASE dbpg TO "john";<br> GRANT USAGE ON SCHEMA schema1 TO "john";<br> GRANT USAGE ON SCHEMA schema2 TO "john";<br> <br> -- to revoke SELECT on 2 tables on 2 schemas : the 4 tables must be unavail= able : <br> <br> REVOKE SELECT ON TABLE schema1.table1 TO "john";<br> REVOKE SELECT ON TABLE schema1.table2 TO "john";<br> <br> REVOKE SELECT ON TABLE schema2.table3 TO "john";<br> REVOKE SELECT ON TABLE schema2.table4 TO "john";<br> <br> -- I would like John may CREATE his TABLES and may SELECT and UPDATE his ta= bles (to write): <br> ALTER DEFAULT PRIVILEGES FOR USER "john" IN SCHEMA schema1, schem= a2 GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON T= ABLES TO "john" ;<br> -- but I've received a message like this one above :<br> <br> Results :<br> John can login and connect to the database : success<br> John can't access any 4 tables on the 2 schemas (REVOKES) : success<br> John can access in read_only (SELECT) on the other tables of schema1 and sc= hema2 : success<br> <br> John can't create any tables in any schema : error<br> He should be able to create objects (tables, index,...) in schemas schema1 = and schema2, with read / write access on them.<br> Here are retuned error messages in each schema :<br> SQL Error [42501]: ERROR: permission denied for schema schema1<br> Position: 14<br> ERROR: permission denied for schema schema1<br> Position: 14<br> ERROR: permission denied for schema schema1<br> Position: 14<br> <br> SQL Error [42501]: ERROR: permission denied for schema schema2<br> Position: 14<br> ERROR: permission denied for schema schema2<br> Position: 14<br> ERROR: permission denied for schema schema2<br> Position: 14<br> <br> Remark :<br> <div>ALTER DEFAULT PRIVILEGES FOR USER has here the whole possible grants (= SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER).</div> <div><br> </div> <div>Can you see why John can't create table ?</div> <div><br> </div> <div>Thanks a lot for your help !</div> <div><br> </div> <div>Etienne DECHERF</div> <div>DBA Senior</div> <div>[email protected]</div> <div>+33 6 67462246</div> <div><br> </div> <br> </div> </body> </html> --_000_35B45AE5854FD442A1775EB1337F9701C8B322BBSEXCMBXP005wpro_--