Re: Storing the password in .pgpass file in an encrypted format

Hi,

Is there a way to store the password in ".pgpass" file in an encrypted format (for example, to be used by pg_dump).

Even though, there are ways to set the permissions on .pgpass, to disallow any access to world or group, the security rules of many organizations disallow to hold any kind of passwords, as plain text.

If there is no existing way to do this, shall we take up this, as a patch?

Regards,
Firoz EV

On 21 February 2014 13:49, firoz e v <firoz(dot)ev(at)huawei(dot)com> wrote:

> Hi,
>
>
>
> Is there a way to store the password in “.pgpass” file in an encrypted
> format (for example, to be used by pg_dump).
>
>
>
> Even though, there are ways to set the permissions on .pgpass, to disallow
> any access to world or group, the security rules of many organizations
> disallow to hold any kind of passwords, as plain text.
>
>
>
> If there is no existing way to do this, shall we take up this, as a patch?
>
>
>
> Regards,
>
> Firoz EV
>
>
>

And where are you going to keep the passwords to decrypt these passwords
(for example to be used by pg_dump)?

regards,
Szymon

firoz e v wrote:
> Hi,
>
> Is there a way to store the password in ".pgpass" file in an encrypted format (for example, to be used by pg_dump).
>
> Even though, there are ways to set the permissions on .pgpass, to disallow any access to world or group, the security rules of many organizations disallow to hold any kind of passwords, as plain text.
>
> If there is no existing way to do this, shall we take up this, as a patch?

Maybe you can memfrob() the password to encrypt it before writing, and
then memfrob() it back before applying it. Would that be secure?

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Hi,

On 21/02/14 11:15, Alvaro Herrera wrote:
> Maybe you can memfrob() the password to encrypt it before writing, and
> then memfrob() it back before applying it. Would that be secure?

From `man memfrob`:

Note that this function is not a proper encryption routine as the XOR
constant is fixed, and is only suitable for hiding strings.

No, it is not secure. And I agree, encrypting .pgpass doesn't make
sense. Either you have a known key and then encryption is useless or
you have to provide a key at runtime and then .pgpass is useless.

Best regards,

--
Christian Kruse http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 21-02-2014 09:49, firoz e v wrote:
> Even though, there are ways to set the permissions on .pgpass, to disallow any access to world or group, the security rules of many organizations disallow to hold any kind of passwords, as plain text.
>
Is your goal hiding the password in .pgpass? You could add support to
accept md5... storage format as password.

--
Euler Taveira Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Euler Taveira wrote:
> On 21-02-2014 09:49, firoz e v wrote:
> > Even though, there are ways to set the permissions on .pgpass, to disallow any access to world or group, the security rules of many organizations disallow to hold any kind of passwords, as plain text.
> >
> Is your goal hiding the password in .pgpass? You could add support to
> accept md5... storage format as password.

How would that work? libpq needs the straight password to send to the
server, not an encrypted one. If you were to have a mechanism by which
libpq can store an md5'd password (or whatever hash) and send that md5
to the server and have the server accept it to grant a connection, then
the md5 has, in effect, become the unencrypted password which others can
capture from the file, and you're back at square one.

You could instead try to have an authentication agent that stores an
encrypted password or certificate and asks the user to supply the key to
decrypt it when trying to establish a connection; but that would force
you to require user intervention, which in many cases you don't want.

If there's policy that disallows storage of plain-text passwords, your
only choice appears to be not to use .pgpass in the first place.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 2014-02-21 12:04:47 -0300, Alvaro Herrera wrote:
> You could instead try to have an authentication agent that stores an
> encrypted password or certificate and asks the user to supply the key to
> decrypt it when trying to establish a connection; but that would force
> you to require user intervention, which in many cases you don't want.

Alternatively use something like kerberos.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Fri, Feb 21, 2014 at 7:49 AM, firoz e v <firoz(dot)ev(at)huawei(dot)com> wrote:

> Hi,
>
>
>
> Is there a way to store the password in ".pgpass" file in an encrypted
> format (for example, to be used by pg_dump).
>
>
>
> Even though, there are ways to set the permissions on .pgpass, to disallow
> any access to world or group, the security rules of many organizations
> disallow to hold any kind of passwords, as plain text.
>
>
>
> If there is no existing way to do this, shall we take up this, as a patch?
>

As observed by others, storing the password in encrypted form in .pgpass
merely means that you need to store the password to decrypt .pgpass in
still another file that would, again, run afoul of such security policies.
There is no appetite in the community to do implementation work that is
provably useless as it cannot accomplish what people imagine to accomplish.

The thing you could do instead that would *look* like it is encrypted is to
use a certificate (e.g. - SSL). The certificate that you'd need to put on
the client still needs to be in something that is effectively plain text
(however much it looks like nonsensical encrypted text).
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

On Fri, Feb 21, 2014 at 7:04 AM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>wrote:

> Euler Taveira wrote:
> > On 21-02-2014 09:49, firoz e v wrote:
> > > Even though, there are ways to set the permissions on .pgpass, to
> disallow any access to world or group, the security rules of many
> organizations disallow to hold any kind of passwords, as plain text.
> > >
> > Is your goal hiding the password in .pgpass? You could add support to
> > accept md5... storage format as password.
>
> How would that work? libpq needs the straight password to send to the
> server, not an encrypted one.

It looks like that is the way it is currently written, but it does not have
to be that way, at least for "md5" rather than "password" authentication.

> If you were to have a mechanism by which
> libpq can store an md5'd password (or whatever hash) and send that md5
> to the server and have the server accept it to grant a connection, then
> the md5 has, in effect, become the unencrypted password which others can
> capture from the file, and you're back at square one.
>

The string in .pgpass would be enough for people to log into postgresql,
true. But it would not work to log onto other things which share the same
clear-text password but don't share the same salting mechanism.

Cheers,

Jeff

Jeff Janes escribió:
> On Fri, Feb 21, 2014 at 7:04 AM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>wrote:

> > If you were to have a mechanism by which
> > libpq can store an md5'd password (or whatever hash) and send that md5
> > to the server and have the server accept it to grant a connection, then
> > the md5 has, in effect, become the unencrypted password which others can
> > capture from the file, and you're back at square one.
>
> The string in .pgpass would be enough for people to log into postgresql,
> true. But it would not work to log onto other things which share the same
> clear-text password but don't share the same salting mechanism.

That's true. Patches welcome to improve that. Maybe we can define that
if the stored password string starts with $1$md5$ and has a just the
right length then it's a md5 hash rather than cleartext, or something
like that.

I do fear that people are going to look at the file and say "hey, it's
encrypted [sic] so it's secure! I can share the file with the world!".

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 02/22/2014 12:20 AM, Alvaro Herrera wrote:
> Jeff Janes escribió:
>> On Fri, Feb 21, 2014 at 7:04 AM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>wrote:
>
>>> If you were to have a mechanism by which
>>> libpq can store an md5'd password (or whatever hash) and send that md5
>>> to the server and have the server accept it to grant a connection, then
>>> the md5 has, in effect, become the unencrypted password which others can
>>> capture from the file, and you're back at square one.
>>
>> The string in .pgpass would be enough for people to log into postgresql,
>> true. But it would not work to log onto other things which share the same
>> clear-text password but don't share the same salting mechanism.
>
> That's true. Patches welcome to improve that. Maybe we can define that
> if the stored password string starts with $1$md5$ and has a just the
> right length then it's a md5 hash rather than cleartext, or something
> like that.

Frankly, that it's possible to just replay the md5 password says that
"md5" isn't really meaningfully better than cleartext, just marginally
less convenient.

It should really involve a handshake, along the broad lines of:

- Server sends random cookie
- Client hashes password cleartext with random cookie from server
- Server hashes stored (cleartext) password with random cookie
- Server compares values

like in the RFC 2617 DIGEST-MD5 authentication method used in SASL, or
even CRAM-MD5 (RFC 2195). Both of which are imperfect, but at least not
trivially replayable.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 02/21/2014 11:52 PM, Christopher Browne wrote:
>
> The thing you could do instead that would *look* like it is encrypted is
> to use a certificate (e.g. - SSL). The certificate that you'd need to
> put on the client still needs to be in something that is effectively
> plain text (however much it looks like nonsensical encrypted text).

Yep, though the certificate private key may well be stored encrypted
with a passphrase that must be entered via direct user interaction.

It looks like doing it with OpenSSL for libpq you might be able to set a
passphrase callback routine to prompt the user to decrypt a client
certificate. With PgJDBC you use JSSE's keystore support.

Client certificates are a *much* stronger way to do this. Another good
option can be Kerberos. Either way, encrypting .pgpass seems utterly
pointless.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Hi,

On 21 Únor 2014, 16:52, Christopher Browne wrote:
> On Fri, Feb 21, 2014 at 7:49 AM, firoz e v <firoz(dot)ev(at)huawei(dot)com> wrote:
>
>> Hi,
>>
>>
>>
>> Is there a way to store the password in ".pgpass" file in an encrypted
>> format (for example, to be used by pg_dump).
>>
>>
>>
>> Even though, there are ways to set the permissions on .pgpass, to
>> disallow
>> any access to world or group, the security rules of many organizations
>> disallow to hold any kind of passwords, as plain text.
>>
>>
>>
>> If there is no existing way to do this, shall we take up this, as a
>> patch?
>>
>
> As observed by others, storing the password in encrypted form in .pgpass
> merely means that you need to store the password to decrypt .pgpass in
> still another file that would, again, run afoul of such security policies.
> There is no appetite in the community to do implementation work that is
> provably useless as it cannot accomplish what people imagine to
> accomplish.

Sure. If you want to log-in without any user interaction, then the
password needs to be stored is a form equal to cleartext (e.g. with a
key). It's mostly security by obscurity.

What I think might be useful and safe at the same time is encrypted
.pgpass with tools asking for the encryption key. Think of it as a simple
passord wallet - not really useful if you're connecting to a single
database, very useful if you have many as you only need to remember the
single password.

If the encrypted passwords were stored in a separate file (say
.pgpass.wallet) then this should not break the current tools. The tools
would do this:

1) exists .pgpass?
1.a) read .pgpass -> is there a matching record? (yes -> stop)
2) exists .pgpass.wallet?
2.a) ask for encryption key
2.b) read .pgpass using the decryption key
2.c) is there a matching record? (yes -> stop)
3) ask for connection info directly

BTW yes, I know what kerberos is, but many of us are dealing with
companies that don't use it.

regards
Tomas

On Fri, Feb 21, 2014 at 8:42 AM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:

> On 02/22/2014 12:20 AM, Alvaro Herrera wrote:
> > Jeff Janes escribió:
> >> On Fri, Feb 21, 2014 at 7:04 AM, Alvaro Herrera <
> alvherre(at)2ndquadrant(dot)com>wrote:
> >
> >>> If you were to have a mechanism by which
> >>> libpq can store an md5'd password (or whatever hash) and send that md5
> >>> to the server and have the server accept it to grant a connection, then
> >>> the md5 has, in effect, become the unencrypted password which others
> can
> >>> capture from the file, and you're back at square one.
> >>
> >> The string in .pgpass would be enough for people to log into postgresql,
> >> true. But it would not work to log onto other things which share the
> same
> >> clear-text password but don't share the same salting mechanism.
> >
> > That's true. Patches welcome to improve that. Maybe we can define that
> > if the stored password string starts with $1$md5$ and has a just the
> > right length then it's a md5 hash rather than cleartext, or something
> > like that.
>
> Frankly, that it's possible to just replay the md5 password says that
> "md5" isn't really meaningfully better than cleartext, just marginally
> less convenient.
>
> It should really involve a handshake, along the broad lines of:
>
> - Server sends random cookie
> - Client hashes password cleartext with random cookie from server
> - Server hashes stored (cleartext) password with random cookie
> - Server compares values
>

I think that is what it does, except both the client and server use a hash
of password to add the cookie to, not directly the cleartext password. The
server can optionally store the 1st level hash rather than the cleartext,
and then skip the first hash step (but not the second hash step). The
client does not have a mechanism to start out with the hash, it currently
always starts with the cleartext, but that is just an implementation detail.

So it is not replayable if you just see what goes over the wire. If you
see what the client starts with, then it is "replayable" but that is not
really the right word for it.

Cheers,

Jeff

I think this thread deserves more attention:

http://www.postgresql.org/message-id/CAAZKuFaJUfdDFp1_vGHbDfYRu0Sj6mSOVvKRp87aCQ53ov6iwA@mail.gmail.com

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Fri, Feb 21, 2014 at 10:42 AM, Alvaro Herrera
<alvherre(at)2ndquadrant(dot)com> wrote:
> I think this thread deserves more attention:
>
> http://www.postgresql.org/message-id/CAAZKuFaJUfdDFp1_vGHbDfYRu0Sj6mSOVvKRp87aCQ53ov6iwA@mail.gmail.com

(I wrote that mail)

I'm still in interested in this idea and haven't found a good reason
to rescind the general thinking there.

Some of my colleagues are thinking along similar lines outside the
Postgres context. They seem happy with how that is shaping up.

So, if there is some will for revival, that would be grand.

On 02/21/2014 09:11 AM, Tomas Vondra wrote:
> What I think might be useful and safe at the same time is encrypted
> .pgpass with tools asking for the encryption key. Think of it as a simple
> passord wallet - not really useful if you're connecting to a single
> database, very useful if you have many as you only need to remember the
> single password.

Sounds interesting, but probably better as an external utility than as
part of PostgreSQL. Call it pgWallet.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 22.2.2014 00:02, Josh Berkus wrote:
> On 02/21/2014 09:11 AM, Tomas Vondra wrote:
>> What I think might be useful and safe at the same time is encrypted
>> .pgpass with tools asking for the encryption key. Think of it as a simple
>> passord wallet - not really useful if you're connecting to a single
>> database, very useful if you have many as you only need to remember the
>> single password.
>
> Sounds interesting, but probably better as an external utility than
> as part of PostgreSQL. Call it pgWallet.

Depends on how you define external utility. It certainly needs to be
somehow integrated with the tools using .pgpass. Do you have something
particular in mind?

While libsecret may look like a good choice, it kinda requires Gnome or
KDE (or some other desktop environment supporting it) running, as it's
just a proxy to the services provides by these environments. I'd bet
most server installations won't have that installed, and in such cases
it's pointless.

Maybe it can be forwarded to the original machine somehow (something
like what 'ssh -A' does), I'm not sure.

I would prefer something self-contained, not requiring a lot of other
stuff installed.

What I envisioned is a simple wallet (basically encrypted .pgpass) with
a simple management command-line tool. Let's call that 'pgpass', with
these options

pgpass list
pgpass add
pgpass rm

I'm fully aware that writing a good / reliable / secure tool for storing
passwords is tricky, and if there's something implemented and usable,
let's use that.

I'm also wondering how well will the existing solutions support the
host/database/user/password model, with wildcards for some of the
fields. I'd guess most of them use simple username/password pairs.

regards
Tomas

On Fri, Feb 21, 2014 at 10:18 PM, Daniel Farina <daniel(at)heroku(dot)com> wrote:
> I'm still in interested in this idea and haven't found a good reason
> to rescind the general thinking there.

It's an interesting idea. I wonder if it would be possible to make it
compatible with existing tools like ssh-agent instead of inventing our
own?

--
greg

On 02/21/2014 03:54 PM, Tomas Vondra wrote:
> Depends on how you define external utility. It certainly needs to be
> somehow integrated with the tools using .pgpass. Do you have something
> particular in mind?

Yeah, I was thinking that the ideal would to be to make this generically
pluggable, like giving the ability to use a unix socket or executable
call for pgpass instead of only looking at a file. I don't think we
should implement any particular wallet technology, just make it possible
to call an external application. I think implementing our own wallet
would be a big mistake.

I'm not sure how broad the actual use case for this is -- most folks
with sophisticated password needs use AD or LDAP -- but if someone wants
to write the code, I'd be for accepting it.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Fri, Feb 21, 2014 at 6:15 PM, Greg Stark <stark(at)mit(dot)edu> wrote:
> On Fri, Feb 21, 2014 at 10:18 PM, Daniel Farina <daniel(at)heroku(dot)com> wrote:
>> I'm still in interested in this idea and haven't found a good reason
>> to rescind the general thinking there.
>
> It's an interesting idea. I wonder if it would be possible to make it
> compatible with existing tools like ssh-agent instead of inventing our
> own?

I don't understand what you mean: the aesthetic of that proposal was
to act as pure delegation insomuch as possible to integrate with other
programs, and the supplementary programs provided that I wrote just
for the purposes of demonstration are short.

(https://github.com/fdr/pq-resolvers, if you want to read the program texts)