References: 
 <CA+M0sHEJC6whG8VfNk=393uoDsedQg+tVZ9vy7GLdWBZR5BC0A@mail.gmail.com>
 <CAKFQuwZHLq1o55-sSaeuF8hU=9nYXN8MNTdL50A7W7Zf_6ZjHQ@mail.gmail.com>
 <CA+M0sHE=wMp3Q251AaHcz612D14qY7X3xgGvzuM8npAY_B6FxA@mail.gmail.com>
User-agent: mu4e 1.5.3; emacs 27.0.91
From: Tim Cross <theophilusx@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: scram-sha-256 encrypted password in pgpass
In-reply-to: 
 <CA+M0sHE=wMp3Q251AaHcz612D14qY7X3xgGvzuM8npAY_B6FxA@mail.gmail.com>
Message-ID: <87mu4ud3vj.fsf@gmail.com>
Date: Tue, 23 Jun 2020 10:26:08 +1000
MIME-Version: 1.0
Content-Type: text/plain
Precedence: bulk


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