Thread: Using LDAP for PostgreSQL permissions/authentication
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? -- Bill Moran <wmoran@potentialtech.com>
* Bill Moran (wmoran@potentialtech.com) wrote:
> 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.
Better is to use Kerberos, imv. It's what AD does.
> 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.
Nope; I'd use puppet or chef or something along those lines to deal with
this aspect, much as I'd do with Unix accounts. Using nsswitch and
tying every user name look up to LDAP has certain.. drawbacks.
> 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.
That's a little over-simplistic, isn't it? What about objects which are
created by the 'wmoran' account?
> 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?
My gut feeling on this is 'pretty darn hard' and 'not sure there are
many who really want it'. That last particularly because tools like
puppet and chef exist and solve this problem in a better way, imv
anyway, than LDAP. Back in the day, I was a big proponent of LDAPv3 and
all of the nice things it did, but the complexities involved in "what
happens when the network goes away" grew tiring and managing accounts
through a config management system which also tracks history of changes,
both to the master repo and to the individual systems, wins hands down.
Thanks,
Stephen
Attachment
On Fri, 13 Sep 2013 16:29:47 -0400 Stephen Frost <sfrost@snowman.net> wrote: > > > 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. > > That's a little over-simplistic, isn't it? What about objects which are > created by the 'wmoran' account? To address this one question, it's not terribly difficult to make a rule that handles this. LDAP could have a "primaryDatabaseRole" attribute that is used when a single role is required (such as for object ownership) ... that's just one possibility. -- Bill Moran <wmoran@potentialtech.com>
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/