Generate user/group sysids from a sequence? 
Author Message
 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Tue, 05 Jul 2005 14:18:29 GMT
 Generate user/group sysids from a sequence?


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

---------------------------(end of broadcast)---------------------------



Tue, 05 Jul 2005 14:30:48 GMT
 Generate user/group sysids from a sequence?

Quote:

> 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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Tue, 05 Jul 2005 22:46:53 GMT
 Generate user/group sysids from a sequence?

Quote:

> 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

---------------------------(end of broadcast)---------------------------



Tue, 05 Jul 2005 23:24:07 GMT
 Generate user/group sysids from a sequence?

Quote:

> 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

  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------



Wed, 06 Jul 2005 00:38:33 GMT
 Generate user/group sysids from a sequence?

Quote:

> 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Wed, 06 Jul 2005 00:42:39 GMT
 Generate user/group sysids from a sequence?

Quote:


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

  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------



Wed, 06 Jul 2005 00:44:37 GMT
 Generate user/group sysids from a sequence?

Quote:

> 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly



Wed, 06 Jul 2005 01:14:56 GMT
 Generate user/group sysids from a sequence?

Quote:


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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Wed, 06 Jul 2005 01:26:39 GMT
 Generate user/group sysids from a sequence?

Quote:


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

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

  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Wed, 06 Jul 2005 01:36:13 GMT
 Generate user/group sysids from a sequence?

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Wed, 06 Jul 2005 01:40:14 GMT
 Generate user/group sysids from a sequence?

Quote:


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

  +  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



Wed, 06 Jul 2005 01:45:39 GMT
 Generate user/group sysids from a sequence?

Quote:


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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly



Wed, 06 Jul 2005 03:03:57 GMT
 Generate user/group sysids from a sequence?
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

Quote:



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

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

---------------------------(end of broadcast)---------------------------



Wed, 06 Jul 2005 03:23:00 GMT
 Generate user/group sysids from a sequence?

Quote:

> 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly



Wed, 06 Jul 2005 03:33:13 GMT
 
 [ 17 post ]  Go to page: [1] [2]

 Relevant Pages 

1. Informix User Group - Forum 2000 - A Two Day User Group Technical

2. The User Group News: South Florida Informix User Group meeting

3. Copy permissions from user/group to different user/group

4. Generate sequence number

5. How can I get SQL Server to generate a unique Sequence Number

6. Best way to generate SQL Server 4.21 sequence nos from front end

7. sequence numbers generated

8. How to generate a Sequence in SQL Server

9. Auto generate a number in sequence

10. a database generated sequence number

11. generate sequence number in sql select

12. RTREE Index on primary key generated by a sequence


 
Powered by phpBB® Forum Software