From: David Steele <david@pgmasters.net>
To: Michael Paquier <michael.paquier@gmail.com>
Cc: PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
Cc: v.popov@postgrespro.ru
Subject: Re: Password identifiers, protocol aging and SCRAM protocol
Date: Wed, 16 Mar 2016 11:14:44 -0400
Message-ID: <56E97864.2070609@pgmasters.net> (raw)
In-Reply-To: <CAB7nPqSGm-9c4yFULt4GS9TzoSuz8XbO-K7TGGGw08sztfG2Uw@mail.gmail.com>
References: <CAB7nPqSMXU35g=W9X74HVeQp0uvgJxvYOuA4A-A3M+0wfEBv-w@mail.gmail.com>
<56E6D98F.1070701@pgmasters.net>
<CAB7nPqQYim-=PBHa19p04wSBbgGT=+ts1qWeQj2FG9HSmBX78Q@mail.gmail.com>
<CAB7nPqTssJpBUZ8g9oUTPJ6RPLCZYFtPypzdYgHL1gtuskxwkw@mail.gmail.com>
<56E848A9.1070203@pgmasters.net>
<CAB7nPqSGm-9c4yFULt4GS9TzoSuz8XbO-K7TGGGw08sztfG2Uw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 3/16/16 9:00 AM, Michael Paquier wrote:
> On Tue, Mar 15, 2016 at 6:38 PM, David Steele <david@pgmasters.net> wrote:
>
>> 1) I see that rolvaliduntil is still in pg_authid:
>> I think that's OK if we now define it to be "role validity" (it's still
>> password validity in the patched docs). I would also like to see a
>> validuntil column in pg_auth_verifiers so we can track password
>> expiration for each verifier separately. For now I think it's enough to
>> copy the same validity both places since there can only be one verifier.
>
> FWIW, this is an intentional change, and my goal is to focus on only
> the protocol aging for now. We will need to move rolvaliduntil to
> pg_auth_verifiers if we want to allow rolling updates of password
> verifiers for a given role, but that's a different patch, and we need
> to think about the SQL interface carefully. This infrastructure makes
> the move easier by the way to do that, and honestly I don't really see
> what we gain now by copying the same value to two different system
> catalogs.
Here's my thinking. If validuntil is moved to pg_auth_verifiers now
then people can start using it there. That will make it less traumatic
when/if validuntil in pg_authid is removed later. The field in
pg_authid could be deprecated in this release to let people know not to
use it.
Or, as I suggested it could be recast as role validity, which right now
happens to be the same as password validity.
>> 2) I don't think the column naming in pg_auth_verifiers is consistent
>> with other catalogs:
>> postgres=# select * from pg_auth_verifiers;
>> roleid | verimet | verival
>> --------+---------+-------------------------------------
>> 16387 | m | md505a671c66aefea124cc08b76ea6d30bb
>> 16388 | p | testu
>>
>> System catalogs generally use a 3 character prefix so I would expect the
>> columns to be (if we pick avr as a prefix):
>
> OK, this makes sense.
>
>> avrrole
>> avrmethod
>> avrverifier
>
> Assuming "ver" is the prefix, we get: verroleid, vermethod, vervalue.
> I kind of like those ones, more than with "avr" as prefix actually.
> Other ideas are of course welcome.
ver is fine as a prefix.
>> 3) rolpassword is still in pg_shadow even though it is not useful anymore:
>> postgres=# select usename, passwd, valuntil from pg_shadow;
>>
>> usename | passwd | valuntil
>> ---------+----------+------------------------
>> vagrant | ******** |
>> test | ******** |
>> testu | ******** | 2017-01-01 00:00:00+00
>>
>> If anyone is actually using this column in a meaningful way they are in
>> for a nasty surprise when trying use the value in passwd as a verifier.
>> I would prefer to drop the column entirely and produce a clear error.
>>
>> Perhaps a better option would be to drop pg_shadow entirely since it
>> seems to have no further purpose in life.
>
> We discussed that on the previous thread and the conclusion was to
> keep pg_shadow, but to clobber the password value with "***",
> explaining this choice:
> http://www.postgresql.org/message-id/6174.1455501497@sss.pgh.pa.us
Ah, I missed that one.
--
-David
david@pgmasters.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: david@pgmasters.net, michael.paquier@gmail.com, v.popov@postgrespro.ru
Subject: Re: Password identifiers, protocol aging and SCRAM protocol
In-Reply-To: <56E97864.2070609@pgmasters.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox