Re: Using LDAP for PostgreSQL permissions/authentication - Mailing list pgsql-general

On Fri, Sep 13, 2013 at 10:00 PM, Bill Moran <wmoran@potentialtech.com> wrote:
>
> Looking at using LDAP to ease the pain of maintaining user accounts across
> many tens of PostgreSQL servers ...
>
> As documented, LDAP solves a few of the problems we have -- since everyone
> will be in LDAP, we can use LDAP's password complexity rules and password
> expiration to handle those security requirements, and (of course) when
> someone changes their password, they don't have to remember to change it
> on every server ... these are big wins.
>
> But it doesn't help with the headache of creating the accounts on all the
> servers, or dropping them as part of employee termination procedures, or
> doing security audits, or changing permissions on multiple servers when
> an employee gets a promotion, etc.
>
> What would help with those challenges, is if PostgreSQL didn't need to have
> a mapping of LDAP -> PostgreSQL account.  Instead, something where PostgreSQL
> checked some LDAP attribute(s) for a list of database roles and used those
> to determine what access to allow.
>
> For example:
> * In LDAP, have a user wmoran
>  - That user has an attribute like "databaseRoles=analyst,beancounter"
> * In PostgreSQL, there is no user called wmoran
> * In PostgreSQL, there are roles called analyst and beancounter
> * Table permissions are GRANTed to analyst and/or beancounter as appropriate
>
> Thus, when I go to log in as wmoran, LDAP checks my password, then informs
> PostgreSQL to allow me in with specified roles, and I can do operations
> granted to those roles.
>
> Obviously, that's not how it works now ... my question is why not?  Is it just
> a matter of nobody's gotten to it yet, or are there issues that make such
> an implementation difficult/troublesome/impossible?  If it's possible, does
> anyone have any concept of how hard it would be to implement?

Pretty hard - but nothing is impossible. It would basically mean that
you have to update shared catalogs during login (e.g. the mapping
between user role and group roles). And you have to do it potentially
*before* logging in, because we'd need to be able to verify which
roles have CONNECT permissions on the database, or are members of the
proper role in AD.

It's not enough to just attach the information to the current session,
at least some of it needs to be persisted to deal with things like
object ownership etc, as Stephen pointed out.


It is, however, fairly easy to build a little script that synchronizes
the role membership from ldap to the database at regular intervals,
and at least for me, this has always been enough. If your LDAP server,
or user provisioning system for that one, has some sort of trigger
functionality, you can make it "almost synchronous" as well.


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/