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@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/j2t3073cc9b1004031339k57a9c4f4m7c04154eac914...@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.6010...@falter.at
Regards
Erwin
--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
