Обсуждение: [GENERAL] Allow login on slave only

Поиск
Список
Период
Сортировка

[GENERAL] Allow login on slave only

От
"Andomar"
Дата:

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.

 

Thanks,

Andomar

Re: [GENERAL] Allow login on slave only

От
"David G. Johnston"
Дата:
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.


Re: [GENERAL] Allow login on slave only

От
Sherrylyn Branchaw
Дата:

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

Re: [GENERAL] Allow login on slave only

От
Tom Lane
Дата:
"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

Re: [GENERAL] Allow login on slave only

От
Joseph Kregloh
Дата:
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:

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.

 

Thanks,

Andomar