Re: Generate user/group sysids from a sequence?

Shridhar Daithankar <shridhar_daithankar@persistent.co.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 fooAccess 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 fooAccess 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 fooAccess 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