creating a readonly user on postgres hot standby server

Question

I have a slave postgres server running in hot standby mode. Currently there is DB owner role that I use to connect and query to this server. Now, I want to create another readonly user/role with a password that I can share to other users so that they can query this standby server. What I read from documentation is that since it's stand by server, I cannot create another user on the database. How do I solve this problem? How do I create another user with read only access?

You can't write on the slave anyway, so even if the user you are using has write privileges on the master, there is no way to do any writing with that user on the slave. — a_horse_with_no_name
What I am trying to do is create a reporting database, by replicating the production database. For security and performance issues, I do not want to modify production database. Is there any other solution, than master-slave, to achieve this? — Sunil Makwana
you can create a user that is only allowed to connect to the standby database but not to the master. This can be achieved through the configuration in pg_hba.conf — a_horse_with_no_name

clemens clemens · Accepted Answer · 2018-03-06T06:23:31

The hot standby server is a exact copy of its master server. Thus, you can't create a user only on the hot standby server. But you may create a readonly user on the master server, which will be automatically copied to the hot standby server.

creating a readonly user on postgres hot standby server

1 Answers