[pgAdmin III] #381: Missing target_role for DEFAULT PRIVILEGES in SQL script

#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