Thread: Re: [ADMIN] Permissions not removed when group dropped

[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@surnet.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@surnet.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)