Re: Modify Bootstrap user ID

[Nikhil Shetty <nikhil.dba04@xxxxxxxxx>]

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

https://www.postgresql.org/docs/8.0/sql-alteruser.html#:~:text=The%20current%20session%20user%20cannot,for%20a%20specified%20configuration%20variable.

Thanks,

Nikhil

On Thu, 22 Feb 2024 at 19:01, Nikhil Shetty <nikhil.dba04@xxxxxxxxx> 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 login

We had to reset the password to make it work

Thanks,

Nikhil

On Thu, 22 Feb 2024 at 00:09, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

Nikhil Shetty <nikhil.dba04@xxxxxxxxx> 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