
(Thought triggered by something Tom said the other day)

Is this a security hole? Looks like one to me. Would it be better to use 
a sequence generator for sysids instead of using max+1 on the user 
table? Or else store the last sysid used somewhere?

andrew

facetest=# create user blurfl;
CREATE USER
facetest=# create table blurfltable (a text, b text);
CREATE TABLE
facetest=# alter table blurfltable owner to blurfl;
ALTER TABLE
facetest=# drop user blurfl;
DROP USER
facetest=# create user floobl;
CREATE USER
facetest=# \dt blurfltable
           List of relations
 Schema |    Name     | Type  | Owner 
--------+-------------+-------+--------
 public | blurfltable | table | floobl
(1 row)

facetest=#


Tue, 12 Aug 2003 10:37:19 -0400
Andrew Dunstan <andrew@dunslane.net>
reuse sysids security hole?
On Tue, 12 Aug 2003, Andrew Dunstan wrote:

> 
> (Thought triggered by something Tom said the other day)
> 
> Is this a security hole? Looks like one to me. Would it be better to use 
> a sequence generator for sysids instead of using max+1 on the user 
> table? Or else store the last sysid used somewhere?

This issue has been discussed before and it was agreed that since most
UNIX systems will behave in the same way, there's no way to know. Also, it
is not possible for a given database to know the max(sysid) of pg_user in
another database.

Thanks,

Gavin


Wed, 13 Aug 2003 00:42:56 +1000 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: reuse sysids security hole?
Gavin Sherry <swm@linuxworld.com.au> writes:
> On Tue, 12 Aug 2003, Andrew Dunstan wrote:
>> Is this a security hole? Looks like one to me. Would it be better to use 
>> a sequence generator for sysids instead of using max+1 on the user 
>> table? Or else store the last sysid used somewhere?

> This issue has been discussed before and it was agreed that since most
> UNIX systems will behave in the same way, there's no way to know. Also, it
> is not possible for a given database to know the max(sysid) of pg_user in
> another database.

You forget that pg_shadow is a shared (cluster-wide) table.

I believe we could make a shared sequence object, too, if we wanted to
go the sequence route.

Right at the moment I like both ideas: a shared sequence to generate new
sysids, and don't ever delete pg_shadow rows.  One attraction of the
sequence generator is that scans over pg_shadow could get rather tedious
if we follow the latter policy.  But with a sequence, CREATE USER
wouldn't need to do a scan.

Something else that should be factored into any redesign of pg_shadow is
the notion of combining users and groups, at least to the extent of
having a common sysid space for both.  See discussion started by Peter
a month or two back (I think thread title mentioned "roles").

			regards, tom lane

Tue, 12 Aug 2003 10:59:13 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: reuse sysids security hole? 

I like the sequence generator idea too.

I know Unix is bad in this area - but that's no reason for us to be bad 
too. This is actually one of the (few) areas where Windows is better 
than Unix.  Let's go for best practice.

(new todo item "Prevent automatic reuse of sysids" ?)

andrew


Tom Lane wrote:

>Gavin Sherry <swm@linuxworld.com.au> writes:
>  
>
>>On Tue, 12 Aug 2003, Andrew Dunstan wrote:
>>    
>>
>>>Is this a security hole? Looks like one to me. Would it be better to use 
>>>a sequence generator for sysids instead of using max+1 on the user 
>>>table? Or else store the last sysid used somewhere?
>>>      
>>>
>
>  
>
>>This issue has been discussed before and it was agreed that since most
>>UNIX systems will behave in the same way, there's no way to know. Also, it
>>is not possible for a given database to know the max(sysid) of pg_user in
>>another database.
>>    
>>
>
>You forget that pg_shadow is a shared (cluster-wide) table.
>
>I believe we could make a shared sequence object, too, if we wanted to
>go the sequence route.
>
>Right at the moment I like both ideas: a shared sequence to generate new
>sysids, and don't ever delete pg_shadow rows.  One attraction of the
>sequence generator is that scans over pg_shadow could get rather tedious
>if we follow the latter policy.  But with a sequence, CREATE USER
>wouldn't need to do a scan.
>
>Something else that should be factored into any redesign of pg_shadow is
>the notion of combining users and groups, at least to the extent of
>having a common sysid space for both.  See discussion started by Peter
>a month or two back (I think thread title mentioned "roles").
>
>			regards, tom lane
>
>  
>



Tue, 12 Aug 2003 11:22:48 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: reuse sysids security hole?

Can I have a TODO for this?

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

Tom Lane wrote:
> Gavin Sherry <swm@linuxworld.com.au> writes:
> > On Tue, 12 Aug 2003, Andrew Dunstan wrote:
> >> Is this a security hole? Looks like one to me. Would it be better to use 
> >> a sequence generator for sysids instead of using max+1 on the user 
> >> table? Or else store the last sysid used somewhere?
> 
> > This issue has been discussed before and it was agreed that since most
> > UNIX systems will behave in the same way, there's no way to know. Also, it
> > is not possible for a given database to know the max(sysid) of pg_user in
> > another database.
> 
> You forget that pg_shadow is a shared (cluster-wide) table.
> 
> I believe we could make a shared sequence object, too, if we wanted to
> go the sequence route.
> 
> Right at the moment I like both ideas: a shared sequence to generate new
> sysids, and don't ever delete pg_shadow rows.  One attraction of the
> sequence generator is that scans over pg_shadow could get rather tedious
> if we follow the latter policy.  But with a sequence, CREATE USER
> wouldn't need to do a scan.
> 
> Something else that should be factored into any redesign of pg_shadow is
> the notion of combining users and groups, at least to the extent of
> having a common sysid space for both.  See discussion started by Peter
> a month or two back (I think thread title mentioned "roles").
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html
> 

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

Tue, 12 Aug 2003 15:20:40 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: reuse sysids security hole?
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Can I have a TODO for this?

* Prevent accidental re-use of sysids for dropped users and groups

The other part of the thread was something like

* Prevent dropping user that still owns objects, or auto-drop the objects

which if successful would eliminate the need to worry about sysid reuse,
but I really don't see a feasible implementation at the moment.

			regards, tom lane

Tue, 12 Aug 2003 16:01:33 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: reuse sysids security hole? 
On Tue, Aug 12, 2003 at 04:01:33PM -0400, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Can I have a TODO for this?
> 
> * Prevent accidental re-use of sysids for dropped users and groups
> 
> The other part of the thread was something like
> 
> * Prevent dropping user that still owns objects, or auto-drop the objects

What about the use of a shared sequence object to generate sysids?

-- 
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"The Gord often wonders why people threaten never to come back after they've
been told never to return" (www.actsofgord.com)

Tue, 12 Aug 2003 16:11:27 -0400
Alvaro Herrera Munoz <alvherre@dcc.uchile.cl>
Re: reuse sysids security hole?
Alvaro Herrera Munoz <alvherre@dcc.uchile.cl> writes:
> What about the use of a shared sequence object to generate sysids?

I didn't think it needed its own mention in the TODO item, but if you
want to...

			regards, tom lane

Tue, 12 Aug 2003 16:17:11 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: reuse sysids security hole? 

Thanks. Added.

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

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Can I have a TODO for this?
> 
> * Prevent accidental re-use of sysids for dropped users and groups
> 
> The other part of the thread was something like
> 
> * Prevent dropping user that still owns objects, or auto-drop the objects
> 
> which if successful would eliminate the need to worry about sysid reuse,
> but I really don't see a feasible implementation at the moment.
> 
> 			regards, tom lane
> 

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

Tue, 12 Aug 2003 18:04:25 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: reuse sysids security hole?

Regarding second item, I don't think  anyone suggested autodropping 
objects, or else I misunderstood. (That would be dangerous, to say the 
least, IMHO). There were suggestions of reparenting objects, and warning 
of  objects losing ownership, although feasibility questions remain.  
(I'm still convinced something sensible can be done, though. I did have 
an idea of keeping a reference count of owned objects in the shadow 
table, but it just seemed too ugly and error prone and not worth it).

So maybe a better generic wording for TODO would be

* Better handling of dropping a user who owns objects.

andrew


Tom Lane wrote:

>Bruce Momjian <pgman@candle.pha.pa.us> writes:
>  
>
>>Can I have a TODO for this?
>>    
>>
>
>* Prevent accidental re-use of sysids for dropped users and groups
>
>The other part of the thread was something like
>
>* Prevent dropping user that still owns objects, or auto-drop the objects
>
>which if successful would eliminate the need to worry about sysid reuse,
>but I really don't see a feasible implementation at the moment.
>
>			regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>  
>


Wed, 13 Aug 2003 07:42:39 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: reuse sysids security hole?
Andrew Dunstan wrote:

>
> Regarding second item, I don't think  anyone suggested autodropping 
> objects, or else I misunderstood. (That would be dangerous, to say the 
> least, IMHO). 

I agree, but some applications might use tables dedicated to a specific 
user. While this is IMHO not a good style to use a RDBMS, people working 
like this would appreciate a DROP USER CASCADE.

Regards,
Andreas



Wed, 13 Aug 2003 18:52:37 +0200
Andreas Pflug <pgadmin@pse-consulting.de>
Re: reuse sysids security hole?