Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > 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
> > DropRole).
>
> > 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.
>
> Both of these have got race conditions ... not but what the dependency
> code has got race condition problems already, but maybe we should try
> to avoid introducing more? I haven't got any better ideas though.
I couldn't parse this paragraph very well. However I'm not sure why you
say the dependency code has got race conditions? We do lock the object
before checking the dependencies, so it's not possible to add a new
dependency while we're dropping the object.
.. right? I'm going to have a look at it again.
> Why is it that we record grantor at all? One could argue that granting
> membership in a role is done on behalf of that role and there's no real
> need to remember exactly who did it.
I think you should ask Stephen Frost about that -- added to CC.
If the grantor bit is not important, then what we should do is just omit
emitting the GRANTED BY part in pg_dumpall, which fixes this report.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.