Re: Grantor name gets lost when grantor role dropped

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Jeff Davis <pgsql(at)j-davis(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org, mr-russ(at)pws(dot)com(dot)au
Subject: Re: Grantor name gets lost when grantor role dropped
Date: 2007-05-03 22:50:44
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs pgsql-hackers

Jeff Davis wrote:

> 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.

I've been staring at this for a while. Upon first reading it, I thought
that it would be simply a matter of adding pg_shdepend entries for the
pg_auth_members rows. This starts sounding suspicious the moment you
consider that there will be one pg_shdepend entry for each role granted,
that is, a lot.

The second problem with this idea is that it's not at all possible,
because pg_shdepend entries can reference an object by OID, but
pg_auth_members rows don't have OIDs. So the most we could do is add
entries for pg_authid.

So I'm currently considering the following alternatives:

1. do nothing at all with pg_shdepend. Upon role deletion, seqscan
pg_auth_members and reject the drop altogether if there is a role
granted to another which mentions the to-be-dropped role ID as grantor.
This is easiest in terms of code (it's even mentioned in the comments in

2. record one pg_shdepend entry for each role that has granted something
to each role (unless the grantor is the same role being granted, in
which case we needn't record anything). So if role A grants Z and X to
C, and role B grants Y and W to C, C now has access to W, Y, X and Z and
there are two pg_shdepend entries:
C -> A
C -> B
So dropping a role would be disallowed automatically without any code
changes, with the checkSharedDependencies() call that's already in
DropRole. Adding a role membership would require a bit more work,
because we'd first need to check that there's not already a pg_shdepend
entry for that combination. Removing a role membership also becomes
more work; we need to check that no other grant depends on the same
grantor before removing the entry.
Note that I'm considering that this alternative requires adding a
GRANTOR symbol to the SharedDependencyType, which probably rules this
out for backpatching.

Comments? I'm leaning towards implementing (2). The patch for
pg_dumpall would also be needed.

Alvaro Herrera
The PostgreSQL Company - Command Prompt, Inc.

In response to


Browse pgsql-bugs by date

  From Date Subject
Next Message Evgeny Gridasov 2007-05-04 02:12:20 BUG #3261: autovacuum starts regarding config setting
Previous Message Douglas Toltzman 2007-05-03 20:29:11 Re: order by question.

Browse pgsql-hackers by date

  From Date Subject
Next Message Jeff Davis 2007-05-03 23:50:42 Re: Bitmap Heap Scan anomaly
Previous Message jaba the mobzy 2007-05-03 21:33:23 Bitmap Heap Scan anomaly