Pavan Kumar <pavan.dba27@xxxxxxxxx> 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).
