Thread: Can Pg somehow recognize/honor linux groups to control user access ?
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!
AFAIK PG does not support it , as yet. IMO this should be implemented as a priority.
Re: Can Pg somehow recognize/honor linux groups to control useraccess ?
From
"David G. Johnston"
Date:
AFAIK PG does not support it , as yet. IMO this should be implemented as a priority.
It does not support it natively, no. What it does support is PAM which I'm led to believe (haven't used it myself) can be configured to accommodate this use case as well as many other configurations people may think up.
David J.
Re: Can Pg somehow recognize/honor linux groups to control useraccess ?
From
"Joshua D. Drake"
Date:
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. *****
On Aug 22, 2018, at 12:08 , David G. Johnston <david.g.johnston@gmail.com> wrote:AFAIK PG does not support it , as yet. IMO this should be implemented as a priority.It does not support it natively, no. What it does support is PAM which I'm led to believe (haven't used it myself) can be configured to accommodate this use case as well as many other configurations people may think up.
I am not sure this is the same what I was thinking about. For example, in db2, connect privilege can be granted to a group and so is grant privilege as in
GRANT SELECT ON TABLE SCHEMA.TABLE TO GROUP ABC;
GRANT CONNECT ON DATABASE TO GROUP ABC
And the group may not necessarily be local group on the node. it can be AD too.
In fact DBAs don't even need to get involved when a new user needs DB access. Sysadmin had to just add that user in a group and we are done.
Re: Can Pg somehow recognize/honor linux groups to control useraccess ?
From
"David G. Johnston"
Date:
I am not sure this is the same what I was thinking about. For example, in db2, connect privilege can be granted to a group and so is grant privilege as inGRANT SELECT ON TABLE SCHEMA.TABLE TO GROUP ABC;GRANT CONNECT ON DATABASE TO GROUP ABC
The "LOGIN" attribute for a Role is not inheritable and I do not imagine changing it to be is something the project can consider.
Every login user must have their own role in the database. These roles are separate from O/S users though "mappings" can be established between the two in pg_hba.conf.
David J.
On 08/22/2018 11:38 AM, Ravi Krishna wrote: > > In fact DBAs don't even need to get involved when a new user needs DB access. Sysadmin had to just add that user in agroup and we are done. How is that different from giving your grants to a database role and just telling the new user the name and password of that role to connect as? -- Dimitri Maziuk Programmer/sysadmin BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu
Attachment
On 08/22/2018 12:54 PM, Ravi Krishna wrote: >> >> How is that different from giving your grants to a database role and >> just telling the new user the name and password of that role to connect as? > > Well here I have to do some work, with the groups approach, it is outsourced to devops. Secondly when you take into accountAD, the user does not have to remember his password for db login. It is same as AD. So it seems to me that the feature may be worth adding is to fetch the password, *as well as "ldapsearchattribute"* from LDAP: https://www.postgresql.org/docs/10/static/auth-methods.html#AUTH-LDAP You should be able to get the role name from AD already, but the password they still have to remember. Although I still don't see this really working for anything more complicated than one database and no user in more than one group. -- Dimitri Maziuk Programmer/sysadmin BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu
Attachment
Dimitri Maziuk <dmaziuk@bmrb.wisc.edu> writes: > On 08/22/2018 11:38 AM, Ravi Krishna wrote: >> >> In fact DBAs don't even need to get involved when a new user needs DB access. Sysadmin had to just add that user in agroup and we are done. > > How is that different from giving your grants to a database role and > just telling the new user the name and password of that role to connect as? I hope your not suggesting that multiple users use the same login credentials to access the database? This would totally destroy the important security principals of attribution and repudiation. I have not looked at PAM for PG, but that is certainly something worth checking out. I have used it in other contexts and it is very powerful. Depending on the implementation, theoretically, it should be possible to have PAM provide the information to determine what database roles to give to a login. What you will probably need is some additional component to act as the 'meta-directory' so that you can have consistent usernames, uid/gid across servers/systems (i.e. ldap/ad) Something else to look at is openLDAP. Again, not yet looked at it in the PG context, but have used it successfully to manage access to other systems (including databases in other RDMS) before. Currently, we use openLDAP and PAM to manage access on Linux servers. One of the things on my 'todo' list is to look at it in a PG context, just haven't got there yet. Avoid any solution which requires a 'generic' account with shared passwords. Apart from the security implications, you will almost certainly run into problems with auditors and many regulatory standards. -- Tim Cross
On 08/22/2018 05:17 PM, Tim Cross wrote: > > I hope your not suggesting that multiple users use the same login > credentials to access the database? A database role is effectively a "shared account" with "shared password" so I'm not sure what your point is. It's just not a "computer login" shared account. That said, there is nothing scary about shared computer accounts either. It's the users that are the scary part. -- Dimitri Maziuk Programmer/sysadmin BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu
Attachment
Joshua D. Drake <jd@commandprompt.com> writes: > 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. > I think this is more an identity management problem rather than SSO (SSO can be considered a sub-topic within identity management). In this case, you are probably looking for some mix of 'single sign on' and 'same sign on', depending on business requirements. This is a large and complex problem which needs a 'whole of business' approach. Essentially, you need a single directory which is a 'source of truth' for all accounts. It manages usernames, user and group IDs, passwords and attributes representing what services and resources each individual is entitled to access. Downstream systems, such as PG, use the information in this central repository to manage authentication (username/password) and authorisation (which services and what roles within those services). A very common architecture is to have synchronised openLDAP and Active Directory as the centralised directory (source of truth) and some application which sits on top to manage the data in the directory servers (MS FIM/MIM, Oracle IDM, Dell Quest etc). How complex and large this ends up being really depends on the particulars of the environment i.e. size, mix of platforms, etc. I would start with either openLDAP or Active Directory (depending on environment) and then PAM if more fine grained control is required that cannot be satisfied via ldap/ad. Tim -- Tim Cross