Обсуждение: Permissions not removed when group dropped

Hi,

I'm using PostgreSQL 8.0. I created a group called grpA and granted grpA
'SELECT' permission on view viewA. When I dropped grpA and created group
grpB, group grpB 'automatically' has SELECT permission to viewA. After
dropping a group with permission to a view, I see that the permission
stored in the relacl field fo pg_class is changed from the group name to
the sysid of the dropped group. When a new group is created, it gets a
sysid that is one greater than the largest of the sysid (e.g., the sysid
of the last group dropped).  Thus the new group may 'inherit' the
permissions of a dropped group.

I have not found this behavior documented any where. Is this behavior
intended? What do I need to do so that when I drop a group all the
permissions of the group are also 'dropped' (i.e., cleared from the
relacl field)?

Thanks for you help.

Rich Harris

On Fri, May 13, 2005 at 03:43:54PM -0500, Harris, Richard wrote:

Hi,

> I'm using PostgreSQL 8.0. I created a group called grpA and granted grpA
> 'SELECT' permission on view viewA. When I dropped grpA and created group
> grpB, group grpB 'automatically' has SELECT permission to viewA. After
> dropping a group with permission to a view, I see that the permission
> stored in the relacl field fo pg_class is changed from the group name to
> the sysid of the dropped group. When a new group is created, it gets a
> sysid that is one greater than the largest of the sysid (e.g., the sysid
> of the last group dropped).  Thus the new group may 'inherit' the
> permissions of a dropped group.
>
> I have not found this behavior documented any where. Is this behavior
> intended? What do I need to do so that when I drop a group all the
> permissions of the group are also 'dropped' (i.e., cleared from the
> relacl field)?

This is a known limitation.  You have to remove the group from all ACLs
before dropping the group; OTOH you can specify a SYSID when creating a
group.

We are working on it, and hopefully in 8.1 you will be told where the
user/group is referenced if you try to drop it.  Automatically deleting
the references from all ACLs has not been discussed but it's a possible
outcome of the implementation.

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"And as an added bonus, now my computer goes to the toilet for me, leaving me
free to spend time on more useful activities! yay slug codefests!" (C. Parker)

Alvaro Herrera <alvherre@surnet.cl> writes:
> We are working on it, and hopefully in 8.1 you will be told where the
> user/group is referenced if you try to drop it.  Automatically deleting
> the references from all ACLs has not been discussed but it's a possible
> outcome of the implementation.

[ raised eyebrow... ]  You expect to be able to tell anything about the
internals of a different database?  Much less do anything to it?

            regards, tom lane

On Fri, May 13, 2005 at 10:48:13PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@surnet.cl> writes:
> > We are working on it, and hopefully in 8.1 you will be told where the
> > user/group is referenced if you try to drop it.  Automatically deleting
> > the references from all ACLs has not been discussed but it's a possible
> > outcome of the implementation.
>
> [ raised eyebrow... ]  You expect to be able to tell anything about the
> internals of a different database?  Much less do anything to it?

No, that's why I haven't mentioned it :-)  I know I can't do anything
about objects in other databases.  But I have wondered if we could
implement CASCADE behavior for an object whose dependencies are only
local to the current database (where CASCADE actually mean delete
references in ACLs, and reject the action altogether if there is an
ownership reference.  And of course, if the object is a tablespace,
reject as well.)

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"La persona que no quería pecar / estaba obligada a sentarse
 en duras y empinadas sillas    / desprovistas, por cierto
 de blandos atenuantes"                          (Patricio Vogel)

Alvaro Herrera <alvherre@surnet.cl> writes:
> No, that's why I haven't mentioned it :-)  I know I can't do anything
> about objects in other databases.  But I have wondered if we could
> implement CASCADE behavior for an object whose dependencies are only
> local to the current database (where CASCADE actually mean delete
> references in ACLs, and reject the action altogether if there is an
> ownership reference.  And of course, if the object is a tablespace,
> reject as well.)

Well, I think ACL references could be auto-dropped (ie, even in RESTRICT
mode); and group memberships too.  It seems to me that those do not
amount to independent objects that need protection.  What I'd like
CASCADE to mean is "drop owned objects".

I agree it makes sense to restrict the owned database and owned
tablespace cases even in CASCADE mode, although that's more of a
judgement call than a necessary conclusion.

So what we've got is:

1. Reject if any references to user from within other databases
(implementation restriction).

2. Reject if user owns any databases or tablespaces (safety feature).

3. If CASCADE mode, drop any owned objects within the current database;
if RESTRICT mode, reject if there are owned objects within the current
database.

4. Auto-drop any remaining references (ACLs and group memberships).

5. Drop the user itself.

The main problem I see with this is that if you do have a user you want
to get rid of who owns objects in multiple databases, it's still mighty
hard to do it.  It'd be nice to have some kind of command that either
drops or reassigns ownership of everything the user has in the current
database.  Then you could use that repeatedly until you'd reached a
point where DROP USER would work.

            regards, tom lane