Subject: Re: scram and \password
To: Michael Paquier <michael.paquier@gmail.com>
References: 
 <CAMkU=1wfBgFPbfAMYZQE78p=VhZX7nN86aWkp0QcCp=+KxZ=bg@mail.gmail.com>
 <CAB7nPqSELwmfwgOt8U_rK15sHWuGeLhsWYihRtq9ADC3MZ=HTg@mail.gmail.com>
 <82d23d50-ba5d-69c1-99c9-7f28d5504b2e@joeconway.com>
 <CAB7nPqR0h4ZoLvYdXme-Ei7CJQu_AQOLpPbf1DFFXZCUAmaZWg@mail.gmail.com>
Cc: Jeff Janes <jeff.janes@gmail.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
From: Joe Conway <mail@joeconway.com>
Openpgp: id=4EA5B4659ED59CC9538139A5332B7E68B699434A
Message-ID: <75873498-ae3c-9b4f-e462-dc255cf14556@joeconway.com>
Date: Sat, 11 Mar 2017 15:04:10 -0800
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.7.0
MIME-Version: 1.0
In-Reply-To: 
 <CAB7nPqR0h4ZoLvYdXme-Ei7CJQu_AQOLpPbf1DFFXZCUAmaZWg@mail.gmail.com>
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="WStTnEr2jnwPvQMVBg7dgecOX4flE7D3t"
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--WStTnEr2jnwPvQMVBg7dgecOX4flE7D3t
From: Joe Conway <mail@joeconway.com>
To: Michael Paquier <michael.paquier@gmail.com>
Cc: Jeff Janes <jeff.janes@gmail.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
Message-ID: <75873498-ae3c-9b4f-e462-dc255cf14556@joeconway.com>
Subject: Re: [HACKERS] scram and \password
References: <CAMkU=1wfBgFPbfAMYZQE78p=VhZX7nN86aWkp0QcCp=+KxZ=bg@mail.gmail.com>
 <CAB7nPqSELwmfwgOt8U_rK15sHWuGeLhsWYihRtq9ADC3MZ=HTg@mail.gmail.com>
 <82d23d50-ba5d-69c1-99c9-7f28d5504b2e@joeconway.com>
 <CAB7nPqR0h4ZoLvYdXme-Ei7CJQu_AQOLpPbf1DFFXZCUAmaZWg@mail.gmail.com>
In-Reply-To: <CAB7nPqR0h4ZoLvYdXme-Ei7CJQu_AQOLpPbf1DFFXZCUAmaZWg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

On 03/11/2017 02:21 PM, Michael Paquier wrote:
> On Sun, Mar 12, 2017 at 5:35 AM, Joe Conway <mail@joeconway.com> wrote:
>> So if the password is not already set, \password uses
>> password_encryption to determine which format to use, and if the
>> password is already set, then the current method is assumed.
>=20
> Yeah, the problem here being that this routine does not need a live
> connection to work, and we surely don't want to make that mandatory,
> that's why I am suggesting something like the above. Another approach
> would be to switch to SCRAM once password_encryption does this switch
> as well... There is no perfect scenario here.

You might extend PQencryptPassword() to take a method. Or create a new
function that does? Certainly psql has a connection available to run the
ALTER ROLE command that it crafts.

I guess a related problem might be, do we have a SQL visible way to
determine what method is used by the current password for a given role?

Joe

--=20
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development


--WStTnEr2jnwPvQMVBg7dgecOX4flE7D3t
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2

iQIcBAEBCAAGBQJYxIJqAAoJEDfy90M199hlsEQP/RJk1fZ5vCSfJDzGYpYOXp+E
v2EBJ2AACtmL9xgwx7E1HqutQYyQCBfHSEWGybGytwS7EbrgYrVaARE+RgHNcexk
Y7MKJPTzidn1k77B9hFJrecRTzmVP6Jp0Cws6ttuyPFS81REdIyc/56vm17epl6z
SMA62+FmwHvTw9r8eRgTHHSHviz1WDZrp5RB2f2PogDh6ebuMgnYfPACJFkSkcaq
/pDpGVWspuFqRD7shodCueRTI7f9NcVYYBDORLVOY0lYHafliTaj5Zq7TrfSmHi/
FI0ToYNiMdpT9o0HrzFTEn+ZqdqQc7sDYrcQ/Wt9Wf8FLKw1DpTx50fVJsQmXlWl
+hL94vtPkeY5oMUyfNvjksFzlPC+U1mV2CgJCdtpdmQicrWgwZrduiOb/nbABy8O
sS9REBd+w6bqXGW3+hMi5c2RDlpu4X0SNcD1hHvYqy0u+SyDCPA0NJ7dPRdwvHy3
J2sG4Ca9XRNB34RwJu7HNimfpr/HBj9V/AelI0xmWqPWUvDeCeCvDpagFLQdfOWb
jeVGvGFISzxV+FpRIOcl2iEiPlu5pO0VVAXN0K3Rhxn1Nf10JU7V4i018XJS0r11
ewOIMcclD0u5rEEK6DTchEDDEpDFc4lYnz8/Uf0ihfFrfCbESa6WtdlLJXiykZeM
STO+filTyoewD0OUhIAV
=eEIw
-----END PGP SIGNATURE-----

--WStTnEr2jnwPvQMVBg7dgecOX4flE7D3t--