Change posgresql 9.3 slave roll password

Question

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?

Answer 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.