Thread: Modify Bootstrap user ID
Hi Team,
We want to upgrade a postgres v13 database to v16. but the bootstrap user used to initialise the database for both are different
Can we update the bootstrap user for v13 ? like assign oid as 10 in pg_authid?
Thanks,
Nikhil
Nikhil Shetty <nikhil.dba04@gmail.com> writes: > We want to upgrade a postgres v13 database to v16. but the bootstrap user > used to initialise the database for both are different > Can we update the bootstrap user for v13 ? like assign oid as 10 in > pg_authid? Do not try to change role OIDs --- you will certainly miss some places where the OID appears. You can rename it though. You might have tried this: regression=# alter user postgres rename to pgsql; ERROR: session user cannot be renamed but it's easy to get around that: regression=# create user joe superuser; CREATE ROLE regression=# \c - joe You are now connected to database "regression" as user "joe". regression=# alter user postgres rename to pgsql; ALTER ROLE regression=# \c - pgsql You are now connected to database "regression" as user "pgsql". regression=# drop user joe; DROP ROLE regards, tom lane
Hi Tom,
We tried by renaming the user but it has removed the md5 password from pg_authid and we were not able to login
We had to reset the password to make it work
Thanks,
Nikhil
On Thu, 22 Feb 2024 at 00:09, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Nikhil Shetty <nikhil.dba04@gmail.com> writes:
> We want to upgrade a postgres v13 database to v16. but the bootstrap user
> used to initialise the database for both are different
> Can we update the bootstrap user for v13 ? like assign oid as 10 in
> pg_authid?
Do not try to change role OIDs --- you will certainly miss some
places where the OID appears. You can rename it though.
You might have tried this:
regression=# alter user postgres rename to pgsql;
ERROR: session user cannot be renamed
but it's easy to get around that:
regression=# create user joe superuser;
CREATE ROLE
regression=# \c - joe
You are now connected to database "regression" as user "joe".
regression=# alter user postgres rename to pgsql;
ALTER ROLE
regression=# \c - pgsql
You are now connected to database "regression" as user "pgsql".
regression=# drop user joe;
DROP ROLE
regards, tom lane
I see below in an old version of the doc which mentions why it was cleared
Because MD5-encrypted passwords use the user name as cryptographic salt, renaming a user clears their MD5 password
Nikhil
On Thu, 22 Feb 2024 at 19:01, Nikhil Shetty <nikhil.dba04@gmail.com> wrote:
Hi Tom,We tried by renaming the user but it has removed the md5 password from pg_authid and we were not able to loginWe had to reset the password to make it workThanks,NikhilOn Thu, 22 Feb 2024 at 00:09, Tom Lane <tgl@sss.pgh.pa.us> wrote:Nikhil Shetty <nikhil.dba04@gmail.com> writes:
> We want to upgrade a postgres v13 database to v16. but the bootstrap user
> used to initialise the database for both are different
> Can we update the bootstrap user for v13 ? like assign oid as 10 in
> pg_authid?
Do not try to change role OIDs --- you will certainly miss some
places where the OID appears. You can rename it though.
You might have tried this:
regression=# alter user postgres rename to pgsql;
ERROR: session user cannot be renamed
but it's easy to get around that:
regression=# create user joe superuser;
CREATE ROLE
regression=# \c - joe
You are now connected to database "regression" as user "joe".
regression=# alter user postgres rename to pgsql;
ALTER ROLE
regression=# \c - pgsql
You are now connected to database "regression" as user "pgsql".
regression=# drop user joe;
DROP ROLE
regards, tom lane