Subject: Re: scram-sha-256 encrypted password in pgpass
To: Stephen Frost <sfrost@snowman.net>, Pavan Kumar <pavan.dba27@gmail.com>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
 Pgsql-admin <pgsql-admin@lists.postgresql.org>,
 "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: 
 <CA+M0sHEJC6whG8VfNk=393uoDsedQg+tVZ9vy7GLdWBZR5BC0A@mail.gmail.com>
 <CAKFQuwZHLq1o55-sSaeuF8hU=9nYXN8MNTdL50A7W7Zf_6ZjHQ@mail.gmail.com>
 <CA+M0sHE=wMp3Q251AaHcz612D14qY7X3xgGvzuM8npAY_B6FxA@mail.gmail.com>
 <20200622225437.GK3125@tamriel.snowman.net>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <bd8bb482-11b7-6987-0a3c-bee5ba895019@aklaver.com>
Date: Mon, 22 Jun 2020 17:19:22 -0700
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.9.0
MIME-Version: 1.0
In-Reply-To: <20200622225437.GK3125@tamriel.snowman.net>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

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