Обсуждение: reuse sysids security hole?
(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 relationsSchema | Name | Type | Owner --------+-------------+-------+--------public | blurfltable | table | floobl (1 row) facetest=#
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
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
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 > > >
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, Pennsylvania19073
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
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)
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
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, Pennsylvania19073
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 > > >
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