4

I've recently implemented streaming replication and need to create a "report user" for the replica. This user will be used to access the database via ODBC to generate reports with.

On the slave (replica DB), It seems I cannot create new users. I also need to change the password of 'postgres' user on the slave. Here's what I've tried and the errors I get:

CREATE ROLE readaccess;
ERROR:  cannot execute CREATE ROLE in a read-only transaction

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

How can I create a new read only user for my replica? How can I change the password of the user 'postgres' on the replica?

Note: I do realize that for a replica, you can (or should) only have read-only access.

Thank you.

Jchase
  • 61
  • 1
  • 6

1 Answers1

5

As you have discovered, the replica is read-only anyway. So you have a few choices.

  1. Just use any of your accounts from the primary. They will have the same permissions on the replica (except that they cannot change data).
  2. Create a special user that only has 'select' permissions on the primary, and that user flow through to the replica.

As for having different passwords on the primary and replica, you can't. If that is a hard requirement for you, you'll have to look into "logical replication".

Chris Curvey
  • 9,738
  • 10
  • 48
  • 70