Reverse engineered SQL for DEFAULT PRIVILEGES incorrect
От | Erwin Brandstetter |
---|---|
Тема | Reverse engineered SQL for DEFAULT PRIVILEGES incorrect |
Дата | |
Msg-id | 52C2A9A5.1080809@falter.at обсуждение исходный текст |
Список | 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@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
В списке pgadmin-support по дате отправления: