Thread: .pgpass and root: a problem

.pgpass and root: a problem

From
Shaun Thomas
Date:
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

Re: .pgpass and root: a problem

From
Scott Marlowe
Date:
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.

Re: .pgpass and root: a problem

From
"Joshua D. Drake"
Date:
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

Re: .pgpass and root: a problem

From
Scott Mead
Date:
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>
>

Re: .pgpass and root: a problem

From
Scott Marlowe
Date:
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.

Re: .pgpass and root: a problem

From
Shaun Thomas
Date:
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

Re: .pgpass and root: a problem

From
Michael Nolan
Date:
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

Re: .pgpass and root: a problem

From
Stephen Frost
Date:
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

Re: .pgpass and root: a problem

From
Scott Marlowe
Date:
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.

Re: .pgpass and root: a problem

From
Shaun Thomas
Date:
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

Re: .pgpass and root: a problem

From
Stephen Frost
Date:
* 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

Re: .pgpass and root: a problem

From
Shaun Thomas
Date:
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

Re: .pgpass and root: a problem

From
Stephen Frost
Date:
* 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

Re: .pgpass and root: a problem

From
Jasen Betts
Date:
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