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