Allow login on slave only

On Fri, Dec 16, 2016 at 9:17 AM, Andomar <andomar@aule.net> wrote:

We run a master server and a hot standby server. Reporting users login to
the standby server to run long queries. However, their login is also valid
on the master server. Is it possible to prevent a user from logging in to
the master server?

A statement like:

alter role newbie_business_analyst nologin;

Is replicated, so it would block logins on both servers.

​I suspect that the only way to do this would be outside of the catalog.
Either via firewall rules or pg_hba.conf​ (and the "reject" option)

https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

David J.

We run a master server and a hot standby server. Reporting users login to
the standby server to run long queries. However, their login is also valid
on the master server. Is it possible to prevent a user from logging in to
the master server?

What I do is use roles as groups, and create separate roles for master
login and standby login. I grant the former to trusted users and the latter
to trusted and untrusted users. Then I put those groups in the pg_hba.conf
file of the master and standby respectively.

Here's a line from the standby's pg_hba.conf (ignore the SSL options)
hostssl all +direct_login_standby 10.61.164.128/26 cert
clientcert=1

And from the master:
hostssl all +direct_login_master 10.61.164.128/26 cert
clientcert=1

Hope that helps.

Sherrylyn

"Andomar" <andomar@aule.net> writes:

We run a master server and a hot standby server. Reporting users login to
the standby server to run long queries. However, their login is also valid
on the master server. Is it possible to prevent a user from logging in to
the master server?

You could use different pg_hba.conf files on master and slave. Or there's
always packet filtering...

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

You can leave authentication to something else. For example authenticate
the users by having them connect to pgBouncer first.

-Joseph

On Fri, Dec 16, 2016 at 11:17 AM, Andomar <andomar@aule.net> wrote: