From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@surnet.cl>
Cc: Harris, Richard <Richard_Harris@adp.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Permissions not removed when group dropped
Date: Sat, 14 May 2005 11:32:23 -0400
Message-ID: <3868.1116084743@sss.pgh.pa.us> (raw)
In-Reply-To: <20050514053053.GA26186@surnet.cl>
References: <E2F600719FA6FF438A680A22A00DA43C05F7AA2C@EXCHANGEDS01.ds.ad.adp.com>
<20050513213505.GB17830@surnet.cl>
<172.1116038893@sss.pgh.pa.us>
<20050514053053.GA26186@surnet.cl>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, alvherre@surnet.cl, Richard_Harris@adp.com
Subject: Re: Permissions not removed when group dropped
In-Reply-To: <3868.1116084743@sss.pgh.pa.us>
References: <E2F600719FA6FF438A680A22A00DA43C05F7AA2C@EXCHANGEDS01.ds.ad.adp.com> <20050513213505.GB17830@surnet.cl> <172.1116038893@sss.pgh.pa.us> <20050514053053.GA26186@surnet.cl> <3868.1116084743@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox