Grantor name gets lost when grantor role dropped

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Cc: mr-russ(at)pws(dot)com(dot)au
Subject: Grantor name gets lost when grantor role dropped
Date: 2007-04-17 01:59:40
Message-ID: 1176775180.4152.97.camel@dogma.v10.wvs
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

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;

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alvaro Herrera 2007-04-17 02:44:30 Re: Grantor name gets lost when grantor role dropped
Previous Message Carsten 2007-04-16 23:56:29 BUG #3232: Regression: pgsql server startup problem with encrypted partitions

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Dunstan 2007-04-17 02:11:08 Re: [HACKERS] Re: IDENTITY/GENERATED v36 Re: Final version of IDENTITY/GENERATED patch
Previous Message Tom Lane 2007-04-17 01:58:57 Re: Hacking on PostgreSQL via GIT