pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedModify Bootstrap user ID
4+ messages / 2 participants
[nested] [flat]
* Modify Bootstrap user ID
@ 2024-02-21 18:17 Nikhil Shetty <nikhil.dba04@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Nikhil Shetty @ 2024-02-21 18:17 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Modify Bootstrap user ID
@ 2024-02-21 18:39 Tom Lane <tgl@sss.pgh.pa.us>
parent: Nikhil Shetty <nikhil.dba04@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2024-02-21 18:39 UTC (permalink / raw)
To: Nikhil Shetty <nikhil.dba04@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Modify Bootstrap user ID
@ 2024-02-22 13:31 Nikhil Shetty <nikhil.dba04@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Nikhil Shetty @ 2024-02-22 13:31 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Modify Bootstrap user ID
@ 2024-02-22 13:34 Nikhil Shetty <nikhil.dba04@gmail.com>
parent: Nikhil Shetty <nikhil.dba04@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Nikhil Shetty @ 2024-02-22 13:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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%20cann....
Thanks,
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 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
>>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-02-22 13:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-21 18:17 Modify Bootstrap user ID Nikhil Shetty <nikhil.dba04@gmail.com>
2024-02-21 18:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-22 13:31 ` Nikhil Shetty <nikhil.dba04@gmail.com>
2024-02-22 13:34 ` Nikhil Shetty <nikhil.dba04@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox