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