Re: Can Pg somehow recognize/honor linux groups to control user access ?
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 signature.asc Description: OpenPGP digital signature
Re: Can Pg somehow recognize/honor linux groups to control user access ?
Dimitri Maziuk 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 a group 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
Re: Can Pg somehow recognize/honor linux groups to control user access ?
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 a group 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 signature.asc Description: OpenPGP digital signature
Re: Can Pg somehow recognize/honor linux groups to control user access ?
On Wed, Aug 22, 2018 at 9:38 AM, Ravi Krishna wrote: > 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 > > 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.
Re: Can Pg somehow recognize/honor linux groups to control user access ?
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. *
Re: Can Pg somehow recognize/honor linux groups to control user access ?
AFAIK PG does not support it , as yet. IMO this should be implemented as a priority.
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!
