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 relations
Schema | 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:
Show quoted text
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?
--
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 19073Bruce 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, Pennsylvania 19073Regarding 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:
Show quoted text
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