I am sending this email on behalf of Russel Smith. He discovered this
bug and his description follows:
Verified on 8.2.3 on Fedora Core 6
Verified on 8.1.3 on RHEL4, custom compile. (I can't control the update to
8.1.8)
The output of an empty role name would possibly not be a problem, but when you
are
doing a dump and restore, pg_dumpall dumps invalid syntax as below;
GRANT "postgres" TO "test_role" GRANTED BY "";
We either need to rethink the way we handle grantor information and when it's
valid.
Or we need to at least allow dump/restore to work as expected when a dropped
role
granted privileges to other users.
To add to my woes when investigating this, GRANTED BY syntax is not included in
the
8.2 documentation at all. It's not listed as valid syntax, and there are no
comments saying what it does.
The self contained test case to produce this is below;
Regards
Russell Smith
psql postgres < bug.sql 2>&1 > output.txt
CREATE ROLE test_role
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
CREATE ROLE invalid_grantor
SUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
SET ROLE invalid_grantor;
GRANT "postgres" TO "test_role";
SET ROLE postgres;
select * from pg_roles;
select pg_auth_members.*, ur.rolname, gr.rolname from pg_auth_members LEFT JOIN
pg_roles ur ON roleid = oid
LEFT JOIN pg_roles gr ON gr.oid = grantor;
DROP ROLE invalid_grantor;
select pg_auth_members.*, ur.rolname, gr.rolname from pg_auth_members LEFT JOIN
pg_roles ur ON roleid = oid
LEFT JOIN pg_roles gr ON gr.oid = grantor;
DROP ROLE test_role;
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
