[pgAdmin III] #381: Missing target_role for DEFAULT PRIVILEGES in SQL script - Mailing list pgadmin-hackers

#381: Missing target_role for  DEFAULT PRIVILEGES in SQL script
----------------------+-----------------------------------------------------
 Reporter:  brsa      |       Owner:  dpage
     Type:  bug       |      Status:  new  
 Priority:  minor     |   Milestone:       
Component:  pgadmin   |     Version:  trunk
 Keywords:  SQL pane  |    Platform:  all  
----------------------+-----------------------------------------------------
 The syntax or DEFAULT PRIVILEGES allows to specify a target_role by which
 the DEFAULT PRIVILEGES are granted. Relevant page in the manual, for
 convenience:
    http://www.postgresql.org/docs/current/interactive/sql-
 alterdefaultprivileges.html

 While the properties tab reflects the setting correctly, this is omitted
 in pgAdmin's SQL script. The omission can lead to unintended '''privilege
 escalation''' if the SQL script is applied. Consider the following test
 case:

 {{{
 -- as user postgres ..
 CREATE SCHEMA foo_schema;
 SET search_path = foo_schema;

 CREATE USER foo;
 GRANT ALL ON SCHEMA foo_schema to foo;

 CREATE USER bar;
 GRANT USAGE ON SCHEMA foo_schema to bar;

 -- Set default privileges (implicitly for default user postgres)
 ALTER DEFAULT PRIVILEGES IN SCHEMA foo_schema GRANT SELECT ON TABLES TO
 bar;

 /*
 Properties tab shows (correct):
    Default table ACL    {bar=r/postgres}

 SQL pane shows (correct):
    ALTER DEFAULT PRIVILEGES IN SCHEMA foo_schema
        GRANT SELECT ON TABLES
        TO bar;
 */

 CREATE TABLE t(i int); -- Works as expected. SELECT granted to bar.

 DROP TABLE t;
 ALTER DEFAULT PRIVILEGES IN SCHEMA foo_schema REVOKE SELECT ON TABLES FROM
 bar;

 -- Set default privileges '''for role foo'''
 ALTER DEFAULT PRIVILEGES FOR ROLE foo IN SCHEMA foo_schema GRANT SELECT ON
 TABLES TO bar;

 /*
 -- Properties tab shows (correct):
 Default table ACL       {bar=r/foo}

 -- SQL pane shows: ( ERROR! )
 ALTER DEFAULT PRIVILEGES IN SCHEMA foo_schema
     GRANT SELECT ON TABLES
     TO bar;

 -- Should be:
 ALTER DEFAULT PRIVILEGES FOR ROLE foo IN SCHEMA foo_schema
     GRANT SELECT ON TABLES
     TO bar;
 */

 -- Postgres works as expected:
 CREATE TABLE t(i int); -- Works as expected. User bar got no privileges.

 DROP TABLE t;
 SET ROLE foo;
 CREATE TABLE t(i int); -- Works as expected. SELECT granted to bar.

 -- Clean up
 RESET ROLE;
 DROP SCHEMA foo_schema CASCADE;
 DROP user foo;
 DROP user bar;
 }}}

 Seems loosely related to #335 and #336, but is a different issue.

--
Ticket URL: <http://code.pgadmin.org/trac/ticket/381>
pgAdmin III <http://code.pgadmin.org/trac/>
pgAdmin III