Обсуждение: .pgpass and root: a problem
Hey folks, We're wanting to implement a more secure password policy, and so have considered switching to LDAP/Active Directory for passwords. Normally, this would be fine, but for two things: 1. Tons of our devs use .pgpass files to connect everywhere. 2. Several devs have root access to various environments. So, by switching from database-stored passwords to LDAP, we open a security problem that currently only affects the database, to developers' personal LDAP password, which is the key to every service and machine they use in the company. Unfortunately I can't see any way around this at all. Ident won't really work on remote systems, .pgpass isn't encrypted, and you can't use encrypted/hashed password entries either. I agree that we should probably have our root access much more locked down than it is, but it's still a valid problem. I don't think I'd even want a restricted set of root users able to see my LDAP password in plain text. Has anyone put thought into combining LDAP and .pgpass, or has it simply been abandoned every time the issue has presented itself? Thanks in advance! -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 sthomas@optionshouse.com ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
On Tue, Feb 5, 2013 at 10:15 AM, Shaun Thomas <sthomas@optionshouse.com> wrote: > Hey folks, > > We're wanting to implement a more secure password policy, and so have > considered switching to LDAP/Active Directory for passwords. Normally, this > would be fine, but for two things: > > 1. Tons of our devs use .pgpass files to connect everywhere. > 2. Several devs have root access to various environments. Stop. If you want secure setups you don't hand out root access to lots of people. Trying to then make it secure is like closing the barn door after the horse has left.
On 02/05/2013 10:44 AM, Scott Marlowe wrote: > > On Tue, Feb 5, 2013 at 10:15 AM, Shaun Thomas <sthomas@optionshouse.com> wrote: >> Hey folks, >> >> We're wanting to implement a more secure password policy, and so have >> considered switching to LDAP/Active Directory for passwords. Normally, this >> would be fine, but for two things: >> >> 1. Tons of our devs use .pgpass files to connect everywhere. >> 2. Several devs have root access to various environments. > > Stop. If you want secure setups you don't hand out root access to > lots of people. Trying to then make it secure is like closing the > barn door after the horse has left. I think this is a naive response Scott although I must admit it was my gut reaction as well. The reality is we shouldn't store a plain text password. At a minimum it should be hashed. That part of the problem is really on us, regardless if it is a bad idea to hand out root. Now it is true that if they can't trust their devs with this problem, those devs shouldn't have root but that is a business policy problem whereas ours is an actual security issue. Sincerely, Joshua D. Drake -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC @cmdpromptinc - 509-416-6579
On Tue, Feb 5, 2013 at 12:15 PM, Shaun Thomas <sthomas@optionshouse.com>wrote: > Hey folks, > > We're wanting to implement a more secure password policy, and so have > considered switching to LDAP/Active Directory for passwords. Normally, this > would be fine, but for two things: > > 1. Tons of our devs use .pgpass files to connect everywhere. > 2. Several devs have root access to various environments. > I would love to see pgpass storing encrypted stuff here, that'd be great... in the meantime... Is there any way that you could move your 'root-fellas' to a 'sudo' model so that they can have *most* of what they need, without allowing identity switches ? I was trying to come up with something clever, but if they're root, they're root. --Scott Mead scottm@openscg.com http://www.openscg.com > > So, by switching from database-stored passwords to LDAP, we open a > security problem that currently only affects the database, to developers' > personal LDAP password, which is the key to every service and machine they > use in the company. > > Unfortunately I can't see any way around this at all. Ident won't really > work on remote systems, .pgpass isn't encrypted, and you can't use > encrypted/hashed password entries either. > > I agree that we should probably have our root access much more locked down > than it is, but it's still a valid problem. I don't think I'd even want a > restricted set of root users able to see my LDAP password in plain text. > > Has anyone put thought into combining LDAP and .pgpass, or has it simply > been abandoned every time the issue has presented itself? > > Thanks in advance! > > -- > Shaun Thomas > OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 > 312-676-8870 > sthomas@optionshouse.com > > ______________________________**________________ > > See http://www.peak6.com/email_**disclaimer/<http://www.peak6.com/email_disclaimer/>for terms and conditions related tothis email > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general> >
On Tue, Feb 5, 2013 at 11:53 AM, Joshua D. Drake <jd@commandprompt.com> wrote: > > On 02/05/2013 10:44 AM, Scott Marlowe wrote: >> >> >> On Tue, Feb 5, 2013 at 10:15 AM, Shaun Thomas <sthomas@optionshouse.com> >> wrote: >>> >>> Hey folks, >>> >>> We're wanting to implement a more secure password policy, and so have >>> considered switching to LDAP/Active Directory for passwords. Normally, >>> this >>> would be fine, but for two things: >>> >>> 1. Tons of our devs use .pgpass files to connect everywhere. >>> 2. Several devs have root access to various environments. >> >> >> Stop. If you want secure setups you don't hand out root access to >> lots of people. Trying to then make it secure is like closing the >> barn door after the horse has left. > > > I think this is a naive response Scott although I must admit it was my gut > reaction as well. The reality is we shouldn't store a plain text password. > At a minimum it should be hashed. That part of the problem is really on us, > regardless if it is a bad idea to hand out root. > > Now it is true that if they can't trust their devs with this problem, those > devs shouldn't have root but that is a business policy problem whereas ours > is an actual security issue. I wasn't arguing against his other points. I was making the point that until he secures root any attempts at security are security theatre. I agree with your points to an extent, but the whole securing passwords thing is a really big issue. The real question to ask is what attack vector are you trying to stop? If I've got root on the db server or the server with the passwords I've already won.
On 02/05/2013 12:44 PM, Scott Marlowe wrote: > Stop. If you want secure setups you don't hand out root access to > lots of people. Trying to then make it secure is like closing the > barn door after the horse has left. I guess you missed the part where I said I thought we should lock root down better. I can certainly influence that policy, but I can't enforce it. But there's also this addendum I added: "I don't think I'd even want a restricted set of root users able to see my LDAP password in plain text." Why? Because say I don't care about the database. Say that's a lost cause because everyone and their dog has root. Whatever. By exposing LDAP passwords, now anyone with root can compromise an LDAP user's identity entirely, across the organization, on Windows and Linux servers. By using LDAP, I've turned a small "Gee, nobody ever changes their password" hole into "Bob just framed Jim for killing the CEO." Not kosher. Right now, the only person who knows my LDAP credentials is myself. Barring hackers, no admin even knows what it is. I'd kinda like to keep it that way. Someone in the admin team brought up Kerberos as a way to let the underlying system punt through to the LDAP server, so we're investigating that instead. If we then strongly encourage people to not use .pgpass and just let kerberos cache their credentials, that should take care of it. Maybe. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 sthomas@optionshouse.com ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
On Tue, Feb 5, 2013 at 1:57 PM, Scott Mead <scottm@openscg.com> wrote: > > > I would love to see pgpass storing encrypted stuff here, that'd be > great... in the meantime... > > I would suggest going one step further, and making encrypted pgpass authorization something that has to be specifically enabled in pg_hba.conf. -- Mike Nolan
Shaun,
* Shaun Thomas (sthomas@optionshouse.com) wrote:
> We're wanting to implement a more secure password policy, and so
> have considered switching to LDAP/Active Directory for passwords.
Don't use the LDAP side of AD, use the Kerberos side. Using LDAP for
auth against AD is terrible and is only available because of backwards
compatibility for broken, non-Kerberized applications. AD and Kerberos
and PostgreSQL play very well these days and provides for true SSO.
Thanks,
Stephen
On Tue, Feb 5, 2013 at 12:07 PM, Shaun Thomas <sthomas@optionshouse.com> wrote: > On 02/05/2013 12:44 PM, Scott Marlowe wrote: > >> Stop. If you want secure setups you don't hand out root access to >> lots of people. Trying to then make it secure is like closing the >> barn door after the horse has left. > > > I guess you missed the part where I said I thought we should lock root down > better. I can certainly influence that policy, but I can't enforce it. But > there's also this addendum I added: I don't miss much. I didn't miss that. You specifically said: "I agree that we should probably have our root access much more locked down than it is, but it's still a valid problem." Probably? Definitely. "I don't think I'd even want a restricted set of root users able to see my LDAP password in plain text." Why are you using LDAP and passing passwords for access to insecure systems? If you're passing PASSWORDS to an insecure system then you have a real problem. At least move to a token based system like kerberos where a lost token represents a short period of someone having the same access as you but not getting your password. Or just run your pg databases in trust or some simple md5 and use a different password. If you can't trust the machines on the other end, no amount of password mangling is going to help hide the passwords. You have to move to some other form of authentication or use passwords you don't care about.
On 02/05/2013 02:58 PM, Scott Marlowe wrote: > Why are you using LDAP and passing passwords for access to insecure > systems? We're trying not to. That's kind of my point. Now, I'd love to spend another few days learning yet another auth mechanism (kerberos) but I was trying to avoid it. As it stands now, I followed a couple "step by step" things I found via google, and all I get is this when trying to use kerberos: psql: GSSAPI continuation error: Unspecified GSS failure. Minor code may provide more information GSSAPI continuation error: Server not found in Kerberos database Not extremely useful. Here's what I don't get... If I'm my own user, and I call "kinit", it sets up my kerberos cache, having obtained it from our AD server. Presumably, that means kerberos is a service that can forward requests to another server (AD) if it gets a request that isn't in its local cache. If it gets a response, that response is added to the local cache for the next request. If not, I'm missing the benefit of kerberos. IMO, telling PG to use gss/kerberos should be as simple as turning it on, so whatever installed handling mechanism is consulted, ala PAM. Clearly I'm missing something. I'm going to read some docs to figure out the stack, but I've never seen this particular thing before. > If you can't trust the machines on the other end, no amount of > password mangling is going to help hide the passwords. You have to > move to some other form of authentication or use passwords you don't > care about. Which was why until now, we've been using passwords that are only valid in the database, and superusers can only connect via local accounts via peer auth. And our prod systems *are* locked down much better than the dev ones, but the dev ones are the concern. People are bound to play loosey-goosey with the dev servers, and I don't want that to turn into some ridiculous exploit. It's obvious I should abandon LDAP. Fine. I tried PAM auth (which is configured to forward to AD), and that works graet, but has the same problem. If the user is presented with a PW prompt more than once in a row, something has failed. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 sthomas@optionshouse.com ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
* Shaun Thomas (sthomas@optionshouse.com) wrote:
> psql: GSSAPI continuation error: Unspecified GSS failure. Minor
> code may provide more information
> GSSAPI continuation error: Server not found in Kerberos database
>=20
> Not extremely useful.
You need to register the server w/ AD by creating a principal for it and
then exporting the princ (shared secret between the KDC and the server)
and then loading it on the server. Another option is to create a MIT or
Heimdal KDC and then set up a cross-realm trust. Which you want to use
really depends on how large your Unix environment is (if it exists at
all) and how much it needs to be independent from the Windows/AD
systems.
> Here's what I don't get... If I'm my own user, and I call "kinit",
> it sets up my kerberos cache, having obtained it from our AD server.
Yes, you get a ticket-granting-ticket from the AD.
> Presumably, that means kerberos is a service that can forward
> requests to another server (AD) if it gets a request that isn't in
> its local cache.
Yes, whenever you connect to another system, it will attempt to do
Kerberos-based authentication by asking the KDC for a ticket for the
service you're connecting to. For PostgreSQL, this would be something
like: POSTGRES/your.host.com@YOUR.DOMAIN. If the princ is coming off of
AD directly, I believe it has to be all uppercase and you'll need to
tell PG (in postgresql.conf) that the krb service is POSTGRES. Windows
clients should then be fine w/ connecting to it, but you'll have to
adjust your Unix-based psql config if you're using it.
> If it gets a response, that response is added to
> the local cache for the next request.
Yes, it will cache the service princ ticket, which is valid for a
limited timeframe.
> If not, I'm missing the benefit of kerberos.
If you'd like to secure your systems, I would strongly encourage you to
become familiar with Kerberos. It underpins all of AD and many, many
Unix environments. It is by far better than passing passwords around or
doing LDAP-style password-based authentication. It can also integrate
into stronger authentication systems (eg: PKI, HSPD12/CAC smart cards).
> IMO, telling PG to use gss/kerberos should be as simple as turning
> it on, so whatever installed handling mechanism is consulted, ala
> PAM. Clearly I'm missing something. I'm going to read some docs to
> figure out the stack, but I've never seen this particular thing
> before.
Funny, as it's what makes AD work.
> It's obvious I should abandon LDAP. Fine. I tried PAM auth (which is
> configured to forward to AD), and that works graet, but has the same
> problem. If the user is presented with a PW prompt more than once in
> a row, something has failed.
PAM is just as bad (if not worse, depending on how you have it set
up...) than using LDAP.
Thanks,
Stephen
On 02/05/2013 03:40 PM, Stephen Frost wrote: > You need to register the server w/ AD by creating a principal for it and > then exporting the princ (shared secret between the KDC and the server) > and then loading it on the server. That looks like something our Windows admins will have to do since they administer the AD setup and there's no service delegation so far as I know. Thanks for confirming my earlier suspicions. :) > Funny, as it's what makes AD work. You might think that, but so far as I've been concerned thus far, AD = LDAP. I'm just a DBA, after all. :) Thanks, again! -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 sthomas@optionshouse.com ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
* Shaun Thomas (sthomas@optionshouse.com) wrote:
> On 02/05/2013 03:40 PM, Stephen Frost wrote:
> >You need to register the server w/ AD by creating a principal for it and
> >then exporting the princ (shared secret between the KDC and the server)
> >and then loading it on the server.
>=20
> That looks like something our Windows admins will have to do since
> they administer the AD setup and there's no service delegation so
> far as I know.
Yes, they would need to handle it. If you're running PG on Linux/Unix
and/or have multiple Unix systems, I'd recommend that you strongly
consider decoupling the Kerberos-on-Unix setup from the Windows-AD
administration by having a Unix KDC and a cross-realm trust between the
two environments. If you have a Unix admin group, you might discuss it
with them..
> >Funny, as it's what makes AD work.
>=20
> You might think that, but so far as I've been concerned thus far, AD
> =3D LDAP. I'm just a DBA, after all. :)
Yeah, AD is actually LDAP+Kerberos. When you log in to your desktop
system (assuming it's a Windows system which is joined to your active
directory domain), you're actually authenticating via Kerberos.
Thanks,
Stephen
On 2013-02-05, Scott Mead <scottm@openscg.com> wrote: > I would love to see pgpass storing encrypted stuff here, that'd be great... > in the meantime... whatever it stores will, of necessity, be sufficient to grant access to the postgres database, libpq could be modified to use MD5 hashed passwords in that file salted with the username. But that won't make the databse any more secure. > Is there any way that you could move your 'root-fellas' to a 'sudo' model > so that they can have *most* of what they need, without allowing identity > switches ? I was trying to come up with something clever, but if they're > root, they're root. -- ââ 100% natural