scram-sha-256 encrypted password in pgpass

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

scram-sha-256 encrypted password in pgpass

Pavan Kumar-3
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="
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  
Reply | Threaded
Open this post in threaded view
|

Re: scram-sha-256 encrypted password in pgpass

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: scram-sha-256 encrypted password in pgpass

David G Johnston
In reply to this post by Pavan Kumar-3
Please don't cross-post.

On Mon, Jun 22, 2020 at 1:35 PM Pavan Kumar <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: scram-sha-256 encrypted password in pgpass

Pavan Kumar-3
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.


 

On Mon, Jun 22, 2020 at 5:04 PM David G. Johnston <[hidden email]> wrote:
Please don't cross-post.

On Mon, Jun 22, 2020 at 1:35 PM Pavan Kumar <[hidden email]> 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  
Reply | Threaded
Open this post in threaded view
|

Re: scram-sha-256 encrypted password in pgpass

Adrian Klaver-4
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
> <[hidden email] <mailto:[hidden email]>> wrote:
>
>     Please don't cross-post.
>
>     On Mon, Jun 22, 2020 at 1:35 PM Pavan Kumar <[hidden email]
>     <mailto:[hidden email]>> 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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: scram-sha-256 encrypted password in pgpass

David G Johnston
In reply to this post by Pavan Kumar-3
On Mon, Jun 22, 2020 at 3:32 PM Pavan Kumar <[hidden email]> 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. 
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.


Just use a long string of random letters, numbers, and symbols and say its encrypted...

David J.
Reply | Threaded
Open this post in threaded view
|

Re: scram-sha-256 encrypted password in pgpass

Stephen Frost
In reply to this post by Pavan Kumar-3
Greetings,

* Pavan Kumar ([hidden email]) 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

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: scram-sha-256 encrypted password in pgpass

Ravi Krishna-17
In reply to this post by Adrian Klaver-4

>
> 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.



Reply | Threaded
Open this post in threaded view
|

Re: scram-sha-256 encrypted password in pgpass

Adrian Klaver-4
In reply to this post by Stephen Frost
On 6/22/20 3:54 PM, Stephen Frost wrote:

> Greetings,
>
> * Pavan Kumar ([hidden email]) 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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: scram-sha-256 encrypted password in pgpass

Tim Cross
In reply to this post by Pavan Kumar-3

Pavan Kumar <[hidden email]> 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).


Reply | Threaded
Open this post in threaded view
|

Re: scram-sha-256 encrypted password in pgpass

Stephen Frost
Greetings,

* Tim Cross ([hidden email]) 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

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: scram-sha-256 encrypted password in pgpass

Tim Cross

Stephen Frost <[hidden email]> writes:

> Greetings,
>
> * Tim Cross ([hidden email]) 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


Reply | Threaded
Open this post in threaded view
|

Re: scram-sha-256 encrypted password in pgpass

Alvaro Herrera-9
In reply to this post by 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@...

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


Reply | Threaded
Open this post in threaded view
|

Re: scram-sha-256 encrypted password in pgpass

Magnus Hagander-2


On Tue, Jun 23, 2020 at 3:53 AM Alvaro Herrera <[hidden email]> wrote:
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@...


libpq in 13 adds PQsetSSLKeyPassHook_*() which allows a low level interface for doing this for SSL. There is no fundamental reason not to have a similar hook for regular passwords, to begin with. Then on top of that we could provide built-in hooks and a way to activate them to use for example a named pipe, calling a shell, reading from terminal etc -- but then to make it possible to re-use that for both passwords and passphrases and possibly more.

--