upgrade from postgres 12 to 13 causes user authority problem

user_17517110 0
Viewed 325

I have a windows install of postgres 12.6-1 installed at port 6432. I have installed a newer version at port 9432 to test the database against our application.

Firstly I tried to dump globals from the 12 to sql, and install the user list into the 13. This was a disaster as all the users including the superuser now were inaccessible.

So I read the release notes, and it say to use pg_upgrade. After a lot of pain, I get it to run, but it appears to have just run the pg_dumpall like I did. pg_upgrade failed at the point of generating databases as the local super user, because the user load has damaged the passwords and now the database cannot be accessed again.

I have checked the SQL output from the PG_DUMPALL command with and without --binary-upgrade, and it appears to be identical in it's generation of MD5 hash data from the database.

Do I need another tool?

An I doing something wrong?

The 13 database is empty, so any drastic action would be ok.

authorization upgrade postgresql
1 Answers

The ED 13 installation defaults the pg_hba.conf encryption to scram-sha-256. If you have loaded passwords with this encryption, keep it. If you (like me) unknowingly loaded md5 encrypted passwords, just change the encryption to md5 on the lines for pg_hba.conf and restart postgres.

If you wish to keep the scram-sha-256 encryption level, Then I suspect there is no alternative but to edit the pg_dumpall output and change the syntax to plain text password entry, and reset the passwords on the new db. I know this works because I just tried loading a sample of the file with plain text password, and was able to log in as the new user.

Thanks to Adrian Klaver and jjanes.

user_175171100
Related