Thread: Storing the password in .pgpass file in an encrypted format
<div class="WordSection1"><p class="MsoNormal">Hi,<p class="MsoNormal"> <p class="MsoNormal">Is there a way to store thepassword in “.pgpass” file in an encrypted format (for example, to be used by pg_dump).<p class="MsoNormal"> <p class="MsoNormal">Eventhough, 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.<p class="MsoNormal"> <pclass="MsoNormal">If there is no existing way to do this, shall we take up this, as a patch?<p class="MsoNormal"> <pclass="MsoNormal">Regards,<p class="MsoNormal">Firoz EV<p class="MsoNormal"> </div>
On 21 February 2014 13:49, firoz e v <firoz.ev@huawei.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,
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 rulesof 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 XORconstant 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 rulesof 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, Suporte24x7 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 securityrules 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.ev@huawei.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?"
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@2ndquadrant.com> wrote:
Euler Taveira wrote:How would that work? libpq needs the straight password to send to the
> 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.
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@2ndquadrant.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@2ndquadrant.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.ev@huawei.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 matchingrecord? (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@2ndquadrant.com> wrote:
On 02/22/2014 12:20 AM, Alvaro Herrera wrote:Frankly, that it's possible to just replay the md5 password says that
> Jeff Janes escribió:
>> On Fri, Feb 21, 2014 at 7:04 AM, Alvaro Herrera <alvherre@2ndquadrant.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.
"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@2ndquadrant.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@heroku.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@mit.edu> wrote: > On Fri, Feb 21, 2014 at 10:18 PM, Daniel Farina <daniel@heroku.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)