1

I need to change the password on my slave posgresql server, because the person who set it up left the password the same as the password for the super user account on our other server.

When I run psql as the super user on the slave server, and then attempt to execute the command

ALTER USER username PASSWORD 'newPassword';

I get the error:

ERROR: cannot execute ALTER ROLE in a read-only transaction

When I attempt to change the transaction with:

SET TRANSACTION READ WRITE;

I get the error:

ERROR: cannot set transaction read-write mode during recovery

I understand that the slave server can only do reads, though how do I bypass this to change the roles password, or pull the slave temporarily out of the recover mode?

  • 3
    You will need to change the password on the master, not on the slave – a_horse_with_no_name Apr 24 '14 at 14:46
1

the person who set it up left the password the same as the password for the super user account on our other server

If the other server is the master, it's normal because having different passwords in the replicated database is not possible. PostgreSQL replicates everything from the master, including user accounts and their passwords. There's no way to divert that temporarily.

If someone needs to be superuser on the secondary without knowing this password, a different authentication method should be used on this server, such as Certificate Authentication, or external methods (ldap, radius...) . As a configuration file, the pg_hba.conf can be different on the slave.

  • My concern was that because the password for the superuser was the same on both servers, if our config file were leaked the password would be visible to who ever got it for our super user account. I just created a less privileged user for the application though, and use that for authentication. – jacobsoloid Apr 24 '14 at 18:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.