Thread: scram-sha-256 encrypted password in pgpass

On 6/22/20 1:34 PM, Pavan Kumar wrote:
> Hello expertes,
> 
> scram-sha-256 encrypted passwords are supported in .pgpass file ? If yes 
> kindly provide us an example.
> 
> I am using below format and it is not working for me
> 
>
/|pglnx1|/:/|5432|/:pgbouncer:/|pgadmin|/:"SCRAM-SHA-256$4096:6IDsjfedwsdpymp0Za7jaMew==$rzSoYL4ZYsW1WJAj7Lt3JtNLNR73AVY7sfsauikweblk][=:Hxx/juPXJZHy5djPctI=*/"/*

You need to use the plain text version of the password like for md5. 
Supplying the password via .pgpass is no different from supplying it 
from the command line or script.

> 
> Please advise
> 
> -- 
> *Regards,
> 
> #!  Pavan Kumar
> ----------------------------------------------*-
> *Sr. Database Administrator..!*
> *NEXT GENERATION PROFESSIONALS, LLC*
> *Cell    #  267-799-3182 #  pavan.dba27 (Gtalk) *
> *India   # 9000459083*
> 
>     *Take Risks; if you win, you will be very happy. If you lose you
>     will be Wise *
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 6/22/20 1:34 PM, Pavan Kumar wrote:
> Hello expertes,
> 
> scram-sha-256 encrypted passwords are supported in .pgpass file ? If yes 
> kindly provide us an example.
> 
> I am using below format and it is not working for me
> 
>
/|pglnx1|/:/|5432|/:pgbouncer:/|pgadmin|/:"SCRAM-SHA-256$4096:6IDsjfedwsdpymp0Za7jaMew==$rzSoYL4ZYsW1WJAj7Lt3JtNLNR73AVY7sfsauikweblk][=:Hxx/juPXJZHy5djPctI=*/"/*

You need to use the plain text version of the password like for md5. 
Supplying the password via .pgpass is no different from supplying it 
from the command line or script.

> 
> Please advise
> 
> -- 
> *Regards,
> 
> #!  Pavan Kumar
> ----------------------------------------------*-
> *Sr. Database Administrator..!*
> *NEXT GENERATION PROFESSIONALS, LLC*
> *Cell    #  267-799-3182 #  pavan.dba27 (Gtalk) *
> *India   # 9000459083*
> 
>     *Take Risks; if you win, you will be very happy. If you lose you
>     will be Wise *
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 6/22/20 3:32 PM, Pavan Kumar wrote:
> Adrian, David,
> 
> Thank you so much for the quick response.
> 
> What would be the point of storing the encrypted password instead of the 
> plaintext one?
> As per our organization security policies, we can 't keep any  passwords 
> in plain text format.

But if you want to log in with encrypted password and someone can grab 
it from the file not sure what the difference is from grabbing the plain 
text one if they both end up logging the user in?



> I am working on postgres + pgbouncer setup, tested pgbouncer 1.14 where 
> we have support to use encrypted password in userlist,txt file. I am 
> surprised why  pgpass is not supporting encrypted passwords.
> 
> 
> 
> 
> On Mon, Jun 22, 2020 at 5:04 PM David G. Johnston 
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
> 
>     Please don't cross-post.
> 
>     On Mon, Jun 22, 2020 at 1:35 PM Pavan Kumar <pavan.dba27@gmail.com
>     <mailto:pavan.dba27@gmail.com>> wrote:
> 
>         scram-sha-256 encrypted passwords are supported in .pgpass file
>         ? If yes kindly provide us an example.
> 
>         I am using below format and it is not working for me
> 
>
/|pglnx1|/:/|5432|/:pgbouncer:/|pgadmin|/:"SCRAM-SHA-256$4096:6IDsjfedwsdpymp0Za7jaMew==$rzSoYL4ZYsW1WJAj7Lt3JtNLNR73AVY7sfsauikweblk][=:Hxx/juPXJZHy5djPctI=*/"/*
> 
>     The documentation doesn't say so one way or the other so I would go
>     with no.  The password in the pgpass file has to be the plaintext
>     password.  The client, upon speaking with the server, will decide
>     whether to send the plaintext password to the server or encrypt it
>     prior to transmission.
> 
>     What would be the point of storing the encrypted password instead of
>     the plaintext one?
> 
>     David J.
> 
> 
> 
> -- 
> *Regards,
> 
> #!  Pavan Kumar
> ----------------------------------------------*-
> *Sr. Database Administrator..!*
> *NEXT GENERATION PROFESSIONALS, LLC*
> *Cell    #  267-799-3182 #  pavan.dba27 (Gtalk) *
> *India   # 9000459083*
> 
>     *Take Risks; if you win, you will be very happy. If you lose you
>     will be Wise *
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 6/22/20 3:32 PM, Pavan Kumar wrote:
> Adrian, David,
> 
> Thank you so much for the quick response.
> 
> What would be the point of storing the encrypted password instead of the 
> plaintext one?
> As per our organization security policies, we can 't keep any  passwords 
> in plain text format.

But if you want to log in with encrypted password and someone can grab 
it from the file not sure what the difference is from grabbing the plain 
text one if they both end up logging the user in?



> I am working on postgres + pgbouncer setup, tested pgbouncer 1.14 where 
> we have support to use encrypted password in userlist,txt file. I am 
> surprised why  pgpass is not supporting encrypted passwords.
> 
> 
> 
> 
> On Mon, Jun 22, 2020 at 5:04 PM David G. Johnston 
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
> 
>     Please don't cross-post.
> 
>     On Mon, Jun 22, 2020 at 1:35 PM Pavan Kumar <pavan.dba27@gmail.com
>     <mailto:pavan.dba27@gmail.com>> wrote:
> 
>         scram-sha-256 encrypted passwords are supported in .pgpass file
>         ? If yes kindly provide us an example.
> 
>         I am using below format and it is not working for me
> 
>
/|pglnx1|/:/|5432|/:pgbouncer:/|pgadmin|/:"SCRAM-SHA-256$4096:6IDsjfedwsdpymp0Za7jaMew==$rzSoYL4ZYsW1WJAj7Lt3JtNLNR73AVY7sfsauikweblk][=:Hxx/juPXJZHy5djPctI=*/"/*
> 
>     The documentation doesn't say so one way or the other so I would go
>     with no.  The password in the pgpass file has to be the plaintext
>     password.  The client, upon speaking with the server, will decide
>     whether to send the plaintext password to the server or encrypt it
>     prior to transmission.
> 
>     What would be the point of storing the encrypted password instead of
>     the plaintext one?
> 
>     David J.
> 
> 
> 
> -- 
> *Regards,
> 
> #!  Pavan Kumar
> ----------------------------------------------*-
> *Sr. Database Administrator..!*
> *NEXT GENERATION PROFESSIONALS, LLC*
> *Cell    #  267-799-3182 #  pavan.dba27 (Gtalk) *
> *India   # 9000459083*
> 
>     *Take Risks; if you win, you will be very happy. If you lose you
>     will be Wise *
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Greetings,

* Pavan Kumar (pavan.dba27@gmail.com) wrote:
> > What would be the point of storing the encrypted password instead of the
> > plaintext one?
> As per our organization security policies, we can 't keep any  passwords in
> plain text format.

Then you need to *actually* encrypt the password in whatever file you'd
like, and then decrypt it using a key from somewhere when you go to
connect to PG and use it to connect to PG.

Anything that doesn't involve some key from somewhere being used to
decrypt it isn't actually meeting your organization's security policies,
certainly not anything that's just dumping whatever into .pgpass and
then allowing you to connect.

> I am working on postgres + pgbouncer setup, tested pgbouncer 1.14 where we
> have support to use encrypted password in userlist,txt file. I am
> surprised why  pgpass is not supporting encrypted passwords.

I'm not sure what you mean here, but I'm pretty confident it's not
actually what you think.  If you can directly connect with it, without
providing some kind of additional key, then it's, pretty much by
definition, not encrypted.

Thanks,

Stephen

Greetings,

* Pavan Kumar (pavan.dba27@gmail.com) wrote:
> > What would be the point of storing the encrypted password instead of the
> > plaintext one?
> As per our organization security policies, we can 't keep any  passwords in
> plain text format.

Then you need to *actually* encrypt the password in whatever file you'd
like, and then decrypt it using a key from somewhere when you go to
connect to PG and use it to connect to PG.

Anything that doesn't involve some key from somewhere being used to
decrypt it isn't actually meeting your organization's security policies,
certainly not anything that's just dumping whatever into .pgpass and
then allowing you to connect.

> I am working on postgres + pgbouncer setup, tested pgbouncer 1.14 where we
> have support to use encrypted password in userlist,txt file. I am
> surprised why  pgpass is not supporting encrypted passwords.

I'm not sure what you mean here, but I'm pretty confident it's not
actually what you think.  If you can directly connect with it, without
providing some kind of additional key, then it's, pretty much by
definition, not encrypted.

Thanks,

Stephen

> 
> But if you want to log in with encrypted password and someone can grab 
> it from the file not sure what the difference is from grabbing the plain 
> text one if they both end up logging the user in?

Exactly.  saved me the trouble of typing this.

> 
> But if you want to log in with encrypted password and someone can grab 
> it from the file not sure what the difference is from grabbing the plain 
> text one if they both end up logging the user in?

Exactly.  saved me the trouble of typing this.

On 6/22/20 3:54 PM, Stephen Frost wrote:
> Greetings,
> 
> * Pavan Kumar (pavan.dba27@gmail.com) wrote:
>>> What would be the point of storing the encrypted password instead of the
>>> plaintext one?
>> As per our organization security policies, we can 't keep any  passwords in
>> plain text format.
> 
> Then you need to *actually* encrypt the password in whatever file you'd
> like, and then decrypt it using a key from somewhere when you go to
> connect to PG and use it to connect to PG.
> 
> Anything that doesn't involve some key from somewhere being used to
> decrypt it isn't actually meeting your organization's security policies,
> certainly not anything that's just dumping whatever into .pgpass and
> then allowing you to connect.
> 
>> I am working on postgres + pgbouncer setup, tested pgbouncer 1.14 where we
>> have support to use encrypted password in userlist,txt file. I am
>> surprised why  pgpass is not supporting encrypted passwords.
> 
> I'm not sure what you mean here, but I'm pretty confident it's not
> actually what you think.  If you can directly connect with it, without
> providing some kind of additional key, then it's, pretty much by
> definition, not encrypted.

The relevant section is:

http://www.pgbouncer.org/config.html#authentication-file-format

and it has quite a few caveats wrt SCRAM.

> 
> Thanks,
> 
> Stephen
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 6/22/20 3:54 PM, Stephen Frost wrote:
> Greetings,
> 
> * Pavan Kumar (pavan.dba27@gmail.com) wrote:
>>> What would be the point of storing the encrypted password instead of the
>>> plaintext one?
>> As per our organization security policies, we can 't keep any  passwords in
>> plain text format.
> 
> Then you need to *actually* encrypt the password in whatever file you'd
> like, and then decrypt it using a key from somewhere when you go to
> connect to PG and use it to connect to PG.
> 
> Anything that doesn't involve some key from somewhere being used to
> decrypt it isn't actually meeting your organization's security policies,
> certainly not anything that's just dumping whatever into .pgpass and
> then allowing you to connect.
> 
>> I am working on postgres + pgbouncer setup, tested pgbouncer 1.14 where we
>> have support to use encrypted password in userlist,txt file. I am
>> surprised why  pgpass is not supporting encrypted passwords.
> 
> I'm not sure what you mean here, but I'm pretty confident it's not
> actually what you think.  If you can directly connect with it, without
> providing some kind of additional key, then it's, pretty much by
> definition, not encrypted.

The relevant section is:

http://www.pgbouncer.org/config.html#authentication-file-format

and it has quite a few caveats wrt SCRAM.

> 
> Thanks,
> 
> Stephen
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Pavan Kumar <pavan.dba27@gmail.com> writes:

> Adrian, David,
>
> Thank you so much for the quick response.
>
> What would be the point of storing the encrypted password instead of the
> plaintext one?
> As per our organization security policies, we can 't keep any  passwords in
> plain text format.
> I am working on postgres + pgbouncer setup, tested pgbouncer 1.14 where we
> have support to use encrypted password in userlist,txt file. I am
> surprised why  pgpass is not supporting encrypted passwords.
>
>

I suspect part of the issue is that the only way you can do this is to
encrypt the .pgpass file. Part of the problem is that people think that
scram-sha-256 is encrypting passwords when in fact it is a one-way hash
- you cannot derive the original password from the hash. A hash only
goes in one direction. Encryption on the other hand is generally 2-way.
You have a key which encrypts the data and a key which decrypts it back
to plain text.

So, having protected passwords in .pgpass is not as simple as just
copying the scram-sha-256 hash value into the password field. PG needs
to hash the provided value and compare it to the stored scram-sha-256
value to know the original password was supplied and there is no way to
get the original password from the sha'd version.

If you need to use a password in a command line scenario (i.e. with a
script), then one way to get around the issue of not storing plain text
passwords is to use GPG. The basic model is

- Create a GPG key and store it in a secure place, such as a keystore
- Use that GPG key to encrypt your password in a file e.g. my-secret.gpg
- In your script, you can have something like

PWD = `gpg -q --for-your-eyes-only --no-tty -d ~/.secure/my-secret.gpg`

The above line will use the key you stored in the keyring/keystore to
encrypt my-secret.gpg to decrypt that file and send the contents to
stdout, which in turn gets assigned to the PWD variable.

Using GPG is a pretty reliable and portable solution. These days, there
are specific programs, essentially password safes, designed for sys
admin purposes which essentially do the same thing, but at a higher
level. For larger organisations with lots of sys admins and complex
security policies etc, investment in one of these 'enterprise' solutions
is usually a good idea (provides lots of other features, such as never
allowing sys admins to actually know passwords/keys so that when one
leaves, you don't have to go around changing credentials on the all the
systems they may have had access to. They work in a similar way to how
things like lastpass or password1 work in user land).

I suspect it is unlikely you will ever see a .pgpass solution which
supports encryption. There are just too many 'chicken and egg' problems
- you need a key to encrypt the .pgpass file, but now you need to store
the key securely. Problem made more difficult because different
platforms all do this in different ways and with different levels of
sophistication. While it could be done, the amount of work required is
probably more than the desire for anyone to implement it (not a big
enough itch).

Greetings,

* Tim Cross (theophilusx@gmail.com) wrote:
> I suspect it is unlikely you will ever see a .pgpass solution which
> supports encryption. There are just too many 'chicken and egg' problems
> - you need a key to encrypt the .pgpass file, but now you need to store
> the key securely. Problem made more difficult because different
> platforms all do this in different ways and with different levels of
> sophistication. While it could be done, the amount of work required is
> probably more than the desire for anyone to implement it (not a big
> enough itch).

I generally agree with most of what you had here, but to this point I
disagree- it'd actually be quite useful for libpq to gain capabilities
in this regard, as it's something that developers these days are clearly
interesting in having provided by a library (up to and including vault
solution integration, which is becoming more and more a standardized
thing, in order to get the needed key), so I dislike the implication
that we won't do that or that we'd look down on a patch which moved us
towards such a solution.  There's certainly some of us in this community
who would very much look positively on such a patch.

Thanks,

Stephen

Stephen Frost <sfrost@snowman.net> writes:

> Greetings,
>
> * Tim Cross (theophilusx@gmail.com) wrote:
>> I suspect it is unlikely you will ever see a .pgpass solution which
>> supports encryption. There are just too many 'chicken and egg' problems
>> - you need a key to encrypt the .pgpass file, but now you need to store
>> the key securely. Problem made more difficult because different
>> platforms all do this in different ways and with different levels of
>> sophistication. While it could be done, the amount of work required is
>> probably more than the desire for anyone to implement it (not a big
>> enough itch).
>
> I generally agree with most of what you had here, but to this point I
> disagree- it'd actually be quite useful for libpq to gain capabilities
> in this regard, as it's something that developers these days are clearly
> interesting in having provided by a library (up to and including vault
> solution integration, which is becoming more and more a standardized
> thing, in order to get the needed key), so I dislike the implication
> that we won't do that or that we'd look down on a patch which moved us
> towards such a solution.  There's certainly some of us in this community
> who would very much look positively on such a patch.
>

I certainly didn't mean to imply anyone would 'look down on a patch'.
However, I am sceptical about such a feature being added to PG and
supported on all supported platforms. The amount of work is non-trivial,
complex and difficult to get right. I'm also not sure trying to provide
this functionality at the PG level is the correct way to go. Adding
functionality within PG to support external solutions would be
beneficial and more achievable, but implementing a full solution less
so.

The biggest challenge for security is complexity. In environments where
you find formal security policies, the environment is typically complex
with multiple systems, not just PG, requiring secure 'vaults' for
passwords and keys. The last thing you want is multiple separate
solutions. You want one solution which works across all your systems, is
easy to maintain and keep secure and easy to audit/monitor etc. Adding
multiple different solutions only adds to complexity. You don't want one
system for managing PG credentials, another system for managing web
credentials, another system for managing server credentials etc. You
want one solution. I know some will argue this is bad because it puts
all your eggs in one basket and this is a risk. However, the reality is,
most places simply don't have sufficient resources to manage multiple
baskets in a secure manner and often, once one basket is compromised,
the others will soon follow. There are two big challenges in security.
The first is preventing compromise and it tends to get a lot of
attention. The second and just as important, is monitoring and becoming
aware of compromise. This is often overlooked and when you examine the
history of data breaches, you notice that in all of the most sever
examples, a common thread is the organisation was unaware of the
compromise for some time. Having multiple baskets creates policy and
process complexity, increases the amount of monitoring and auditing
required and will generally reduce overall security.

Providing additional APIs and facilities in libpq and other areas of PG
to support external vaults would be useful. Adding secure vault
implementations to PG less so. 

-- 
Tim Cross

On 2020-Jun-23, Tim Cross wrote:

> If you need to use a password in a command line scenario (i.e. with a
> script), then one way to get around the issue of not storing plain text
> passwords is to use GPG. The basic model is
> 
> - Create a GPG key and store it in a secure place, such as a keystore
> - Use that GPG key to encrypt your password in a file e.g. my-secret.gpg
> - In your script, you can have something like
> 
> PWD = `gpg -q --for-your-eyes-only --no-tty -d ~/.secure/my-secret.gpg`

Perhaps the way to implement this is to have .pgpass be a named pipe,
and you have a program that produces lines from encrypted input after
requesting a passphrase from the user -- perhaps using gpg underneath.
I have vague recollections of this being discussed in the past.

For example, see this thread from 2013
https://www.postgresql.org/message-id/CAAZKuFaJUfdDFp1_vGHbDfYRu0Sj6mSOVvKRp87aCQ53ov6iwA@mail.gmail.com

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