Display of DEFAULT PRIVILEGES incorrect (RM #694)

[dpage]

Issue migrated from Redmine: https://redmine.postgresql.org/issues/694 Originally created by Erwin Brandstetter at 2013-11-20 03:20:37 UTC.

Tested with pgAdmin 1.18.1 on Windows XP. Remote Postgres 9.1.10 Server on Debian Linux. But I assume this bug is affects all current versions.

h2. Steps to reproduce

As superuser @postgres@:

CREATE role foo;
CREATE role bar;
CREATE SCHEMA test;
GRANT ALL ON SCHEMA test TO foo;

SET ROLE foo;

ALTER DEFAULT PRIVILEGES IN SCHEMA test
GRANT SELECT ON TABLES TO bar;

RESET ROLE;

Now pgAdmin displays in the SQL pane to every role:

-- Schema: test

-- DROP SCHEMA test;

CREATE SCHEMA test
  AUTHORIZATION postgres;

GRANT ALL ON SCHEMA test TO postgres;
GRANT ALL ON SCHEMA test TO foo;

ALTER DEFAULT PRIVILEGES IN SCHEMA test
    GRANT SELECT ON TABLES
    TO bar;

Which is incorrect. @DEFAULT PRIVILEGES@ only apply to particular roles: http://www.postgresql.org/docs/current/interactive/sql-alterdefaultprivileges.html

The last part must be:

ALTER DEFAULT PRIVILEGES FOR ROLE foo IN SCHEMA test
    GRANT SELECT ON TABLES
    TO bar;

With: @FOR ROLE foo@

psql 9.1.10 gets it right:

postgres@db:~$ env LANG='C' psql db -E -p5433

db=# \ddp+ test
********* QUERY **********
SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS "Owner",
  n.nspname AS "Schema",
  CASE d.defaclobjtype WHEN 'r' THEN 'table' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'function' END AS "Type",
  pg_catalog.array_to_string(d.defaclacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_default_acl d
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace
WHERE (n.nspname ~ '^(test)$'
        OR pg_catalog.pg_get_userbyid(d.defaclrole) ~ '^(test)$')
ORDER BY 1, 2, 3;
**************************

         Default access privileges
 Owner | Schema | Type  | Access privileges
-------+--------+-------+-------------------
 foo   | test   | table | bar=r/foo

The query can assist in fixing the bug.

This has caused us some nasty confusion before I could pin it down.

The roots of the bug may or may not be related to this (fixed) bug in Postgres: http://www.postgresql.org/message-id/j2t3073cc9b1004031339k57a9c4f4m7c04154eac9149be@mail.gmail.com

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/694#note-1 Originally created by Dave Page at 2016-02-18 09:09:54 UTC.

Some additional research by Sanket@EDB (confirmed by me) shows this is a bigger issue than first thought:

I have evaluated the code and found another issue while trying to resolve this bug.

Lets assume we have created 3 roles foo1, foo2 and bar1 and one schema "test". Now using foo1 role we have given SELECT privileges to bar1 as mentioned below:

SET ROLE foo1; ALTER DEFAULT PRIVILEGES IN SCHEMA test GRANT SELECT ON TABLES TO bar1; RESET ROLE;

And using foo2 role we have given ALL privileges to bar1: SET ROLE foo2; ALTER DEFAULT PRIVILEGES IN SCHEMA test GRANT ALL ON TABLES TO bar1; RESET ROLE;

so if you look at the output of pg_default_acl table, it will look as below:

defaclrole schema defobjtype defaclacl

  21633      21637      r (table)      {bar1=r/foo1}
  21634      21637      r (table)      {bar1=arwdDxt/foo2}

where 21633 is oid of foo1 and 21634 is oid of foo2 and 21637 is oid of test3.

So now we will have 2 granters(foo1, foo2) and 1 grantee(bar1) on schema test

so to resolve the main issue mentioned by Erwin we have to show 2 ALTER DEFAULT PRIVILEGES statement for both granter role in reversed engineering query.

This solution will create another issue in property dialog of schema.

In property dialog -> default privileges tab -> roles and privileges are listed only for current role but not for all granters. so in our case only for current role (lets assume its foo1) it will show default privileges but not for granter foo2.

I think resolving this issue is itself a big project which would take a lot of time.