Re: Can Pg somehow recognize/honor linux groups to control user access ?

["Joshua D. Drake" <jd@xxxxxxxxxxxxxxxxx>]

On 08/22/2018 08:56 AM, David Gauthier wrote:

Hi:

The title says it all.  I need to be control who can gain access to a DB based on a linux user group.  I can set up a generic role and password, but also want to prevent users who are not in a specific linux group from accessing the DB.  For code that works with the DB, this is easy (just chmod the group on the code file(s)).  But is there a way to add an additional gauntlet that checks membership in the linux group if, for example, they were trying to get in using psql at the linux prompt ?

There are a couple hundred in the linux group and the list of names changes constantly. I suppose creating a DB role per user in the linux group may be possible if something like a cron was maintaining this (creating/dropping uid based roles as the group membership changes) then give everyone the same password.  But does that prevent someone outside the linux group from just logging in with someone else's uid and the generic password?

 

I'm hoping that this is a common need and that someone has a good solution.

Thanks in Advance for any help!

You could probably write a pam module to do it but it seems to be your are inverting the problem and should be looking at this from a Postgres not Linux perspective. Perhaps consider using an SSO solution for both Linux and Postgres.

JD

-- 
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development. 
Advocate: @amplifypostgres || Learn: https://postgresconf.org
*****     Unless otherwise stated, opinions are my own.   *****