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 по дате отправления:

Предыдущее
От: Karl-Philipp Richter
Дата:
Сообщение: [bug report] underscores in table cell not visible
Следующее
От: tarmo sepp
Дата:
Сообщение: Future of "Apply" button