Missing target_role for DEFAULT PRIVILEGES in SQL script (RM #625)

[dpage]

Issue migrated from Redmine: https://redmine.postgresql.org/issues/625 Originally created by Anonymous at 2012-09-30 19:11:29 UTC.

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 #579 and #580, but is a different issue.