Re: 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(at)surnet(dot)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(at)surnet(dot)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(at)surnet(dot)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

[Moved to -hackers]

On Sat, May 14, 2005 at 11:32:23AM -0400, Tom Lane wrote:

> So what we've got [for DROP USER] 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.

Sounds reasonable.

Additionally we need to think what should happen if the user is the
grantor of some privilege. I think we should warn in RESTRICT mode, and
in CASCADE, revoke the privilege from the grantee.

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

Hmm. We could implement something like "DROP USER LOCALLY [CASCADE |
RESTRICT]", which would be a very misleading name for operations 2-4
above. Additionally, if the user doesn't have references in other
databases, drop the user itself. (Note it's inconsistent.)

For DROP TABLESPACE, I think we should also provide some sort of DROP
LOCALLY, that drops all objects in the current database. (Suggestions
for the exact spelling of the option are welcome.)

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"La gente vulgar solo piensa en pasar el tiempo;
el que tiene talento, en aprovecharlo"

Alvaro Herrera <alvherre(at)surnet(dot)cl> writes:
> Additionally we need to think what should happen if the user is the
> grantor of some privilege. I think we should warn in RESTRICT mode, and
> in CASCADE, revoke the privilege from the grantee.

You mean "fail in RESTRICT mode", no?

> Hmm. We could implement something like "DROP USER LOCALLY [CASCADE |
> RESTRICT]", which would be a very misleading name for operations 2-4
> above. Additionally, if the user doesn't have references in other
> databases, drop the user itself. (Note it's inconsistent.)

I'd go for something more like "DROP OWNED OBJECTS", which'd be just
the stuff internal to the current database (owned objects and ACL
entries). You don't need to drop group memberships per-database.

regards, tom lane

On Sun, May 15, 2005 at 05:48:56PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre(at)surnet(dot)cl> writes:
> > Additionally we need to think what should happen if the user is the
> > grantor of some privilege. I think we should warn in RESTRICT mode, and
> > in CASCADE, revoke the privilege from the grantee.
>
> You mean "fail in RESTRICT mode", no?

Yes, with a message indicating what happened.

> > Hmm. We could implement something like "DROP USER LOCALLY [CASCADE |
> > RESTRICT]", which would be a very misleading name for operations 2-4
> > above. Additionally, if the user doesn't have references in other
> > databases, drop the user itself. (Note it's inconsistent.)
>
> I'd go for something more like "DROP OWNED OBJECTS", which'd be just
> the stuff internal to the current database (owned objects and ACL
> entries). You don't need to drop group memberships per-database.

Ok.

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"Find a bug in a program, and fix it, and the program will work today.
Show the program how to find and fix a bug, and the program
will work forever" (Oliver Silfridge)