
It occurred to me after I wrote that functions with 'security definer'
would present a problem with any default owner changing scheme. I like the
mass reassignment suggestion.

andrew

Chris wrote:
> Ah OK, I must have been thinking of the database owner check.  I'd vote
> for (1) checking that they own no objects and by default owning all
> their stuff to the database owner.  Plus add an optional clause:
>
> DROP USER foo OWNER TO bob;
>
> Chris
>
>>
>> The docs (new and old) explicitly state you can do this; see for
>> example http://www.postgresql.org/docs/7.3/static/sql-dropuser.html
>>
[snip]
>> But ISTM that in such a case the user's objects should possibly be
>> reassigned to the database owner (who can't be dropped), in kinda the
>> same way that a *nix process that is orphaned is reparented to init. I
>> guess that might break other things, or would it?
>>
>> Or maybe we need 'drop user foo with cascade'.
>>




Sun, 10 Aug 2003 21:10:47 -0500 (EST)
"Andrew Dunstan" <andrew@dunslane.net>
Re: dropping a user causes pain (#2)
Hi,

I dropped the owner of a table (with no complaints), and now I get this:

psql:

asdf=# \dt
         List of relations
 Schema | Name | Type  |   Owner
--------+------+-------+------------
 public | a1   | table |

pg_dump:

pg_dump: WARNING: owner of data type "a1" appears to be invalid
pg_dump: WARNING: owner of table "a1" appears to be invalid

Didn't there used to be a check that occurred, preventing you from dropping
a user who owned objects?

Chris


Mon, 11 Aug 2003 10:13:18 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
dropping a user causes pain (#2)

The docs (new and old) explicitly state you can do this; see for example 
http://www.postgresql.org/docs/7.3/static/sql-dropuser.html

But ISTM that in such a case the user's objects should possibly be 
reassigned to the database owner (who can't be dropped), in kinda the 
same way that a *nix process that is orphaned is reparented to init. I 
guess that might break other things, or would it?

Or maybe we need 'drop user foo with cascade'.

Or both.

cheers

andrew


Christopher Kings-Lynne wrote:

>Hi,
>
>I dropped the owner of a table (with no complaints), and now I get this:
>
>psql:
>
>asdf=# \dt
>         List of relations
> Schema | Name | Type  |   Owner
>--------+------+-------+------------
> public | a1   | table |
>
>pg_dump:
>
>pg_dump: WARNING: owner of data type "a1" appears to be invalid
>pg_dump: WARNING: owner of table "a1" appears to be invalid
>
>Didn't there used to be a check that occurred, preventing you from dropping
>a user who owned objects?
>
>Chris
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>  
>


Sun, 10 Aug 2003 22:49:43 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: dropping a user causes pain (#2)
Ah OK, I must have been thinking of the database owner check.  I'd vote for
(1) checking that they own no objects and by default owning all their stuff
to the database owner.  Plus add an optional clause:

DROP USER foo OWNER TO bob;

Chris



----- Original Message ----- 
From: "Andrew Dunstan" <andrew@dunslane.net>
To: "Hackers" <pgsql-hackers@postgresql.org>
Sent: Monday, August 11, 2003 10:49 AM
Subject: Re: [HACKERS] dropping a user causes pain (#2)


>
> The docs (new and old) explicitly state you can do this; see for example
> http://www.postgresql.org/docs/7.3/static/sql-dropuser.html
>
> But ISTM that in such a case the user's objects should possibly be
> reassigned to the database owner (who can't be dropped), in kinda the
> same way that a *nix process that is orphaned is reparented to init. I
> guess that might break other things, or would it?
>
> Or maybe we need 'drop user foo with cascade'.
>
> Or both.
>
> cheers
>
> andrew
>
>
> Christopher Kings-Lynne wrote:
>
> >Hi,
> >
> >I dropped the owner of a table (with no complaints), and now I get this:
> >
> >psql:
> >
> >asdf=# \dt
> >         List of relations
> > Schema | Name | Type  |   Owner
> >--------+------+-------+------------
> > public | a1   | table |
> >
> >pg_dump:
> >
> >pg_dump: WARNING: owner of data type "a1" appears to be invalid
> >pg_dump: WARNING: owner of table "a1" appears to be invalid
> >
> >Didn't there used to be a check that occurred, preventing you from
dropping
> >a user who owned objects?
> >
> >Chris
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 7: don't forget to increase your free space map settings
> >
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>


Mon, 11 Aug 2003 11:01:42 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: dropping a user causes pain (#2)
Christopher Kings-Lynne writes:

> Ah OK, I must have been thinking of the database owner check.  I'd vote for
> (1) checking that they own no objects and by default owning all their stuff
> to the database owner.

The reason none of this will work is that users are global, so when you
drop a user, you would have to dig through all databases to do whatever
action you imagine.  That is not possible.

-- 
Peter Eisentraut   peter_e@gmx.net

Mon, 11 Aug 2003 10:12:05 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: dropping a user causes pain (#2)
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Ah OK, I must have been thinking of the database owner check.  I'd vote for
> (1) checking that they own no objects and by default owning all their stuff
> to the database owner.  Plus add an optional clause:
> DROP USER foo OWNER TO bob;

If you can suggest a plausible way that DROP USER is going to change the
contents of other databases (which might well contain things owned by
the target user), this might get onto the TODO list --- although I'd
personally prefer RESTRICT/CASCADE options.  So far, since no one has
the foggiest idea how to implement cross-database removal, it's just
been left as-is.

			regards, tom lane

Mon, 11 Aug 2003 10:31:21 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: dropping a user causes pain (#2) 
> If you can suggest a plausible way that DROP USER is going to change the
> contents of other databases (which might well contain things owned by
> the target user), this might get onto the TODO list --- although I'd
> personally prefer RESTRICT/CASCADE options.  So far, since no one has
> the foggiest idea how to implement cross-database removal, it's just
> been left as-is.

Ya ya.  I had forgotten that aspect.

Chris



Mon, 11 Aug 2003 22:40:13 +0800 (WST)
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: dropping a user causes pain (#2) 

I did have a thought that it could be done lazily (on backend startup) 
on other databases and immediately on the current database. I guess it 
depends on the cost of checking for such things - wouldn't want to add 
greatly to startup time.

That would leave a small window of orphanage for existing backends on 
other databases, but is arguably an improvement on the current situation.

OTOH I'm not sure how much harm this causes, other than aesthetic.

andrew

Tom Lane wrote:

>"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>  
>
>>Ah OK, I must have been thinking of the database owner check.  I'd vote for
>>(1) checking that they own no objects and by default owning all their stuff
>>to the database owner.  Plus add an optional clause:
>>DROP USER foo OWNER TO bob;
>>    
>>
>
>If you can suggest a plausible way that DROP USER is going to change the
>contents of other databases (which might well contain things owned by
>the target user), this might get onto the TODO list --- although I'd
>personally prefer RESTRICT/CASCADE options.  So far, since no one has
>the foggiest idea how to implement cross-database removal, it's just
>been left as-is.
>
>			regards, tom lane
>
>  
>



Mon, 11 Aug 2003 10:58:46 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: dropping a user causes pain (#2)
Andrew Dunstan wrote:

>
> I did have a thought that it could be done lazily (on backend startup) 
> on other databases and immediately on the current database. I guess it 
> depends on the cost of checking for such things - wouldn't want to add 
> greatly to startup time.
>
> That would leave a small window of orphanage for existing backends on 
> other databases, but is arguably an improvement on the current situation.
>
> OTOH I'm not sure how much harm this causes, other than aesthetic.
>
Dropping a user could merely set a "dropped" flag to disable login, and 
some VACUUM action could cleanup databases.

Regards,
Andreas



Mon, 11 Aug 2003 18:13:08 +0200
Andreas Pflug <pgadmin@pse-consulting.de>
Re: dropping a user causes pain (#2)
Andreas Pflug wrote:

> Dropping a user could merely set a "dropped" flag to disable login, 
> and some VACUUM action could cleanup databases.
>
How would you know when everything was cleaned up on all the databases?

A quick perusal of dev docs suggests that cleanup would need to take 
place in the following:

pg_am
pg_class
pg_conversion
pg_group
pg_namespace
pg_opclass
pg_operator
pg_proc (and watch for prosecdef)
pg_type

cheers

andrew


Mon, 11 Aug 2003 12:55:51 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: dropping a user causes pain (#2)
Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Andrew Dunstan wrote:
>> OTOH I'm not sure how much harm this causes, other than aesthetic.
>> 
> Dropping a user could merely set a "dropped" flag to disable login, and 
> some VACUUM action could cleanup databases.

Not sure I care for the "vacuum" part of that, but how about this
variant: DROP USER sets a flag in pg_shadow to disable login, and
the pg_shadow entry isn't removed, ever.  (We could tweak the pg_user
view to hide dropped users, but anything looking directly at pg_shadow
would have to be taught about the flag, analogous to what happened with
attisdropped in the last release.)

The advantage here is that the sysid assigned to the user would remain
present in pg_shadow and couldn't accidentally be assigned to a new
user.  This would prevent the problem of new users "inheriting"
permissions and even object ownership from deleted users due to chance
coincidence of sysid.

I suppose one could delete the pg_shadow row once one is darn certain
there is no trace of the user's sysid anywhere, but it's not clear to me
it's worth the trouble.

			regards, tom lane

Mon, 11 Aug 2003 13:10:17 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: dropping a user causes pain (#2) 

If people want to remove a user, I assume they don't want to keep
old objects around.

How about if we created a script that goes through all the databases and
reports items owned by a specific user, or orphaned items not owned by
anyone?

---------------------------------------------------------------------------

Tom Lane wrote:
> Andreas Pflug <pgadmin@pse-consulting.de> writes:
> > Andrew Dunstan wrote:
> >> OTOH I'm not sure how much harm this causes, other than aesthetic.
> >> 
> > Dropping a user could merely set a "dropped" flag to disable login, and 
> > some VACUUM action could cleanup databases.
> 
> Not sure I care for the "vacuum" part of that, but how about this
> variant: DROP USER sets a flag in pg_shadow to disable login, and
> the pg_shadow entry isn't removed, ever.  (We could tweak the pg_user
> view to hide dropped users, but anything looking directly at pg_shadow
> would have to be taught about the flag, analogous to what happened with
> attisdropped in the last release.)
> 
> The advantage here is that the sysid assigned to the user would remain
> present in pg_shadow and couldn't accidentally be assigned to a new
> user.  This would prevent the problem of new users "inheriting"
> permissions and even object ownership from deleted users due to chance
> coincidence of sysid.
> 
> I suppose one could delete the pg_shadow row once one is darn certain
> there is no trace of the user's sysid anywhere, but it's not clear to me
> it's worth the trouble.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 11 Aug 2003 17:22:14 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: dropping a user causes pain (#2)

Makes sense, but I think DROP USER should also warn immediately if it 
detects the most common case (I imagine) where the user owns things in 
the current database.

andrew


Bruce Momjian wrote:

>If people want to remove a user, I assume they don't want to keep
>old objects around.
>
>How about if we created a script that goes through all the databases and
>reports items owned by a specific user, or orphaned items not owned by
>anyone?
>
>  
>


Mon, 11 Aug 2003 17:37:49 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: dropping a user causes pain (#2)
> Not sure I care for the "vacuum" part of that, but how about this
> variant: DROP USER sets a flag in pg_shadow to disable login, and
> the pg_shadow entry isn't removed, ever.  (We could tweak the pg_user
> view to hide dropped users, but anything looking directly at pg_shadow
> would have to be taught about the flag, analogous to what happened with
> attisdropped in the last release.)
> 
> The advantage here is that the sysid assigned to the user would remain
> present in pg_shadow and couldn't accidentally be assigned to a new
> user.  This would prevent the problem of new users "inheriting"
> permissions and even object ownership from deleted users due to chance
> coincidence of sysid.
> 
> I suppose one could delete the pg_shadow row once one is darn certain
> there is no trace of the user's sysid anywhere, but it's not clear to me
> it's worth the trouble.

+1

(Hey I've seen other people do that :P )

Chris


Tue, 12 Aug 2003 09:09:52 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: dropping a user causes pain (#2) 
Tom Lane writes:

> The advantage here is that the sysid assigned to the user would remain
> present in pg_shadow and couldn't accidentally be assigned to a new
> user.  This would prevent the problem of new users "inheriting"
> permissions and even object ownership from deleted users due to chance
> coincidence of sysid.

But how does one actually get rid of the privileges?

Btw., the problem is going to get worse if we get nested roles, roles with
grant options, and possibly other parts of the enhanced privilege
facilities.  For example, if you remove a user from a role/group, you
would need to search the entire database cluster for any privileges
granted through that group that this user had used to create some kind of
permanent state.  I'm not sure if we want to cover all of these cases with
various "this link no longer exists" flags, especially since later on the
link could be reestablished.

-- 
Peter Eisentraut   peter_e@gmx.net

Tue, 12 Aug 2003 09:51:04 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: dropping a user causes pain (#2) 
Andrew Dunstan writes:

> Makes sense, but I think DROP USER should also warn immediately if it
> detects the most common case (I imagine) where the user owns things in
> the current database.

Well, the "dropuser" program connects to template1, so in that case it'd
be a rather uncommon occurrence.

-- 
Peter Eisentraut   peter_e@gmx.net

Tue, 12 Aug 2003 09:53:11 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: dropping a user causes pain (#2)

ISTM there's a difference between an object without an (exisiting) owner 
and an object whose owner doesn't currently have the privileges required 
to create it, although maybe there's a good case for a script to detect 
the latter as a part of a good administrator's arsenal of tricks in 
keeping things sane and clean.

andrew

Peter Eisentraut wrote:

>Tom Lane writes:
>
>  
>
>>The advantage here is that the sysid assigned to the user would remain
>>present in pg_shadow and couldn't accidentally be assigned to a new
>>user.  This would prevent the problem of new users "inheriting"
>>permissions and even object ownership from deleted users due to chance
>>coincidence of sysid.
>>    
>>
>
>But how does one actually get rid of the privileges?
>
>Btw., the problem is going to get worse if we get nested roles, roles with
>grant options, and possibly other parts of the enhanced privilege
>facilities.  For example, if you remove a user from a role/group, you
>would need to search the entire database cluster for any privileges
>granted through that group that this user had used to create some kind of
>permanent state.  I'm not sure if we want to cover all of these cases with
>various "this link no longer exists" flags, especially since later on the
>link could be reestablished.
>
>  
>



Tue, 12 Aug 2003 14:57:02 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: dropping a user causes pain (#2)