Re: Generate user/group sysids from a sequence?

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