Reverse engineered SQL for DEFAULT PRIVILEGES incorrect

From: Erwin Brandstetter <brandstetter(at)falter(dot)at>
To: pgadmin-support(at)postgresql(dot)org
Subject: Reverse engineered SQL for DEFAULT PRIVILEGES incorrect
Date: 2013-12-31 11:25:25
Message-ID: 52C2A9A5.1080809@falter.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-support

Hi developers!

The owner / target_role is missing from the SQL script for ALTER DEFAULT
PRIVILEGES.

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.

== 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 -- with: 'FOR
ROLE foo '
GRANT SELECT ON TABLES
TO bar;

== End steps ==

psql 9.1.10 gets it right:

postgres(at)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 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

I created an issue in Redmine under my Postgres account (brsa) with all
the details, steps to reproduce et al.
https://redmine.postgresql.org/issues/694

Posted on hackers before, but it doesn't seem to get noticed:
http://www.postgresql.org/message-id/528C2D00.6010905@falter.at

Regards
Erwin

Browse pgadmin-support by date

  From Date Subject
Next Message tarmo sepp 2014-01-06 07:33:55 Future of "Apply" button
Previous Message Karl-Philipp Richter 2013-12-31 03:46:37 [bug report] underscores in table cell not visible