Re: Generate user/group sysids from a sequence?

Currently, the default sysid assigned to a user or group is computed as
"max(sysid)+1". We've seen a couple of complaints now from people who
deleted their newest user, made another user, and found that permissions
from the deleted user carried over to the new one.

It seems to me that the easiest solution to this is to generate the
default sysid from a sequence object, instead. Unless someone
deliberately resets the sequence, there'd be no conflicts.

A small difficulty is that explicitly-specified sysids could conflict
with sysids generated later by the sequence. We could perhaps fix this
by forcing up the sequence setting to be at least as large as an
explicitly-given ID (compare the handling of explicitly loaded OIDs).

Comments?

regards, tom lane

On 17 Jan 2003 at 1:16, Tom Lane wrote:

> Currently, the default sysid assigned to a user or group is computed as
> "max(sysid)+1". We've seen a couple of complaints now from people who
> deleted their newest user, made another user, and found that permissions
> from the deleted user carried over to the new one.
>
> It seems to me that the easiest solution to this is to generate the
> default sysid from a sequence object, instead. Unless someone
> deliberately resets the sequence, there'd be no conflicts.
>
> A small difficulty is that explicitly-specified sysids could conflict
> with sysids generated later by the sequence. We could perhaps fix this
> by forcing up the sequence setting to be at least as large as an
> explicitly-given ID (compare the handling of explicitly loaded OIDs).

I would say keep range of user specified ids and automatically generated ids
exclusive to each other.

Something like user can specify the id upto 64K, automatically generated ids
are above that.

Like unix ports. Below 1024, you need to be root to get it listening..

Of course one fine day, somebody is going to ask for a configuration option for
this but a DBA can always set the sequence value to any range he wants.

Just a thought..

Bye
Shridhar

--
telepression, n.: The deep-seated guilt which stems from knowing that you did
not try hard enough to look up the number on your own and instead put the
burden on the directory assistant. -- "Sniglets", Rich Hall & Friends

"Shridhar Daithankar" <shridhar_daithankar(at)persistent(dot)co(dot)in> writes:
> I would say keep range of user specified ids and automatically generated ids
> exclusive to each other.

No, that won't do. The principal reason why we keep the explicit SYSID
option around at all is so that a DBA can deliberately recreate a user
with the same SYSID he had before, in case the user is dropped by
mistake leaving objects or permissions behind. So we can't just forbid
conflicts completely.

regards, tom lane

Shridhar Daithankar <shridhar_daithankar(at)persistent(dot)co(dot)in> writes:
> If this is the idea, I suggest that all user/group transactions be logged. So
> that admin can go thr. them to find out what was id of an user at any given
> time. Otherwise admin is not likely to keep list of uids handy and in tough
> situation, that is what he/she is going to need.

No, it's not really a problem. The only reason why you'd care about
recreating a user with a pre-existing SYSID is if there are still
objects or permissions entries in the database with that ownership ID
--- and in that case, you can easily see what ID they have. An example:

regression=# create table foo (f1 int);
CREATE TABLE
regression=# create user joe;
CREATE USER
regression=# grant select on foo to joe;
GRANT
regression=# \z foo
Access privileges for database "regression"
Schema | Table | Access privileges
--------+-------+----------------------------
public | foo | {=,postgres=arwdRxt,joe=r}
(1 row)

... time passes ...

regression=# drop user joe;
DROP USER

... after awhile you realize your mistake:

regression=# \z foo
Access privileges for database "regression"
Schema | Table | Access privileges
--------+-------+----------------------------
public | foo | {=,postgres=arwdRxt,123=r}
(1 row)

... and here's how you get out of it:

regression=# create user joe with sysid 123;
CREATE USER
regression=# \z foo
Access privileges for database "regression"
Schema | Table | Access privileges
--------+-------+----------------------------
public | foo | {=,postgres=arwdRxt,joe=r}
(1 row)

(now you can do a REVOKE if you need to)

regards, tom lane

Tom Lane wrote:
> Currently, the default sysid assigned to a user or group is computed as
> "max(sysid)+1". We've seen a couple of complaints now from people who
> deleted their newest user, made another user, and found that permissions
> from the deleted user carried over to the new one.
>
> It seems to me that the easiest solution to this is to generate the
> default sysid from a sequence object, instead. Unless someone
> deliberately resets the sequence, there'd be no conflicts.
>
> A small difficulty is that explicitly-specified sysids could conflict
> with sysids generated later by the sequence. We could perhaps fix this
> by forcing up the sequence setting to be at least as large as an
> explicitly-given ID (compare the handling of explicitly loaded OIDs).

A sequence sounds like a good idea. When we create a user, we can use
MAX() to find the maximum, and if that is less than the sequence value,
bump up the sequence to equal max and add the row, again incrementing
the sequence. Another idea would be to put a trigger on the column so
that any INSERT/UPDATE would automatically bump up the sequence with
setval().

The reason I was being cautious is to handle cases where people are
poking in pg_shadow directly.

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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> The reason I was being cautious is to handle cases where people are
> poking in pg_shadow directly.

If they're poking pg_shadow directly, I think it's up to them to avoid
or cope with sysid conflicts (the unique indexes on the table will
prevent the worst errors). ISTM it is sufficient for CREATE USER
to not cause conflicts when used as documented.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > The reason I was being cautious is to handle cases where people are
> > poking in pg_shadow directly.
>
> If they're poking pg_shadow directly, I think it's up to them to avoid
> or cope with sysid conflicts (the unique indexes on the table will
> prevent the worst errors). ISTM it is sufficient for CREATE USER
> to not cause conflicts when used as documented.

OK. Do we have many people left upgrading from pg_dump's that COPY into
pg_shadow?

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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> OK. Do we have many people left upgrading from pg_dump's that COPY into
> pg_shadow?

Hm, good point. I had forgotten we ever did that ;-)

It looks like 7.0.* was the last release where pg_dumpall did that.
Is that far enough back?

[ looks further... ] Actually, such a dump is broken now anyway,
because the column layout of pg_shadow has changed since 7.0.
So I think it's a moot point.

We could perhaps arrange the code so that if nextval'ing the sequence
produces a duplicate sysid, we just loop back and nextval again until
we get a nonconflicting id. I had hoped to remove the seqscan of
pg_shadow from CREATE USER; but we could replace it with syscache probes
for duplicate usename and id, and just repeat the syscache probe if
we have to do another nextval. This way, we don't need to bother with
touching the sequence at all during a CREATE USER with explicit sysid.

regards, tom lane

On Fri, Jan 17, 2003 at 11:38:24AM -0500, Bruce Momjian wrote:
> Tom Lane wrote:
> >
> > A small difficulty is that explicitly-specified sysids could conflict
> > with sysids generated later by the sequence. We could perhaps fix this
> > by forcing up the sequence setting to be at least as large as an
> > explicitly-given ID (compare the handling of explicitly loaded OIDs).
>
> A sequence sounds like a good idea. When we create a user, we can use
> MAX() to find the maximum, and if that is less than the sequence value,
> bump up the sequence to equal max and add the row, again incrementing
> the sequence. Another idea would be to put a trigger on the column so
> that any INSERT/UPDATE would automatically bump up the sequence with
> setval().
>

Hmm, unlike the OID case, I'd think there's unlikely to be many 'preused'
userids. Why not just retry if the sequence hits an existing entry?

Ross

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > OK. Do we have many people left upgrading from pg_dump's that COPY into
> > pg_shadow?
>
> Hm, good point. I had forgotten we ever did that ;-)
>
> It looks like 7.0.* was the last release where pg_dumpall did that.
> Is that far enough back?
>
> [ looks further... ] Actually, such a dump is broken now anyway,
> because the column layout of pg_shadow has changed since 7.0.
> So I think it's a moot point.

Good.

> We could perhaps arrange the code so that if nextval'ing the sequence
> produces a duplicate sysid, we just loop back and nextval again until
> we get a nonconflicting id. I had hoped to remove the seqscan of
> pg_shadow from CREATE USER; but we could replace it with syscache probes
> for duplicate usename and id, and just repeat the syscache probe if
> we have to do another nextval. This way, we don't need to bother with
> touching the sequence at all during a CREATE USER with explicit sysid.

Well, the problem is that this could still cause the reuse of a deleted
user, no? Wasn't that the problem we were originally trying to solve?

The reason I was suggesting the trigger/setval earlier is that it would
eliminate the sequential scan and prevent reuse, I think.

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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>> This way, we don't need to bother with
>> touching the sequence at all during a CREATE USER with explicit sysid.

> Well, the problem is that this could still cause the reuse of a deleted
> user, no? Wasn't that the problem we were originally trying to solve?

Hmm, yeah I guess so. Okay, we do need to compare an explicit SYSID
setting to the sequence, and bump up the sequence if it's greater.
Annoying, but I guess there's no choice.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> >> This way, we don't need to bother with
> >> touching the sequence at all during a CREATE USER with explicit sysid.
>
> > Well, the problem is that this could still cause the reuse of a deleted
> > user, no? Wasn't that the problem we were originally trying to solve?
>
> Hmm, yeah I guess so. Okay, we do need to compare an explicit SYSID
> setting to the sequence, and bump up the sequence if it's greater.
> Annoying, but I guess there's no choice.

Yea, I wished we could have avoided it too, but at least we are removing
the sequential scan.

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

On Fri, 2003-01-17 at 12:36, Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> >> This way, we don't need to bother with
> >> touching the sequence at all during a CREATE USER with explicit sysid.
>
> > Well, the problem is that this could still cause the reuse of a deleted
> > user, no? Wasn't that the problem we were originally trying to solve?
>
> Hmm, yeah I guess so. Okay, we do need to compare an explicit SYSID
> setting to the sequence, and bump up the sequence if it's greater.
> Annoying, but I guess there's no choice.

*puts on 'outside the box' hat*

I'm sure most of you are familiar with at least one flavor of Unix, so
I'm sure I'm not going to leave anyone confused. (I hope.)

Why not maintain a sequence, but allow implicit UID assignment? ie;
CREATE USER user --UID1;
CREATE USER user2 WITH UID 2; --UID2
DROP USER user2 --buhbye;
CREATE user2 --Now gets UID3;
CREATE user3 WITH UID2 --user3 now has UID2;

Default behaviour being if a UID has -been- used (not is -in- use) that
it continues on?

-Ketrien Saihr-Kenchedra
I don't need no steeenking sig!
http://ljpg.sf.net/ - http://www.blurty.com/~ketrien/

Have we decided it's really too difficult to remove all references to a
given sysid when the user is dropped? It seems like we're creating
multiple new problems in an effort to workaround one existing problem.

Robert Treat

On Fri, 2003-01-17 at 12:38, Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > >> This way, we don't need to bother with
> > >> touching the sequence at all during a CREATE USER with explicit sysid.
> >
> > > Well, the problem is that this could still cause the reuse of a deleted
> > > user, no? Wasn't that the problem we were originally trying to solve?
> >
> > Hmm, yeah I guess so. Okay, we do need to compare an explicit SYSID
> > setting to the sequence, and bump up the sequence if it's greater.
> > Annoying, but I guess there's no choice.
>
> Yea, I wished we could have avoided it too, but at least we are removing
> the sequential scan.
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
> Have we decided it's really too difficult to remove all references to a
> given sysid when the user is dropped?

Getting at objects in other databases is considerably less practical
than anything we've discussed here. At a minimum I think it would
require launching an additional backend to connect into each other
database. But that is pretty ugly because then the updates in each
database are distinct transactions --- what if one fails, and you've
already committed removals in other databases?

In an ideal world we'd drop a user's objects during DROP USER, but
I don't foresee achieving that nirvana any time soon. At least not
unless we want to change users to become local to databases.

regards, tom lane

On Fri, 2003-01-17 at 14:32, Tom Lane wrote:
> Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
> > Have we decided it's really too difficult to remove all references to a
> > given sysid when the user is dropped?
>
> Getting at objects in other databases is considerably less practical
> than anything we've discussed here. At a minimum I think it would
> require launching an additional backend to connect into each other
> database. But that is pretty ugly because then the updates in each
> database are distinct transactions --- what if one fails, and you've
> already committed removals in other databases?
>

Perhaps you don't allow the removal unless all databases came up clean.
You can have the db admin go in a clean up each database manually if
need be. Granted this doesn't solve getting at those other databases in
the first place, but it eliminates any transactional issues.

Robert Treat

Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
> On Fri, 2003-01-17 at 14:32, Tom Lane wrote:
>> Getting at objects in other databases is considerably less practical
>> than anything we've discussed here.

> Perhaps you don't allow the removal unless all databases came up clean.
> You can have the db admin go in a clean up each database manually if
> need be. Granted this doesn't solve getting at those other databases in
> the first place, but it eliminates any transactional issues.

Oh? What if someone creates an object belonging to the user after we've
finished examining that database? (Actually, this issue exists even
within a single database; so I suppose we'd need to invent some kind of
lock on userids ...)

regards, tom lane