On Mon, Feb 15, 2016 at 9:56 AM, Stephen Frost <sfrost@snowman.net> wrote:
> * Michael Paquier (michael.paquier@gmail.com) wrote:
>> We'd need as well to switch pg_shadow to have an array of elements
>> made of protocol:identifier instead of a single password field. There
>> can be only one valid identifier per protocol, user and valid_until
>> for a single point in time, and I can't believe that we should
>> recommend only one authentication protocol per single major version of
>> Postgres.
>
> Ugh, that sounds pretty grotty to me.
>
> Applications which consider these fields will need to be updated, one
> way or the other, and I'd much rather they be updated to work with
> reasonable structures rather than something we've hacked together in
> some faint hope that it'd be useful. An array in pg_shadow for a field
> which used to be a text field does *not* sound like a simpler solution
> to me, and I'd rather simply do away with those views entirely, or at
> least nuke the fields which are at issue, than try to come up with
> something between wholesale change and no change that ends up being
> worse than both.
It seems to me that applications are going to need a refresh anyway...
Among the other possibilities I can foresee:
- Add a column "protocol" to pg_shadow and produce one row per
protocol, so one user will be listed for all the protocol it has. Any
application could then filter out things with an additional WHERE
clause.
- Nuke passwd from pg_shadow and have a new view pg_shadow_verifiers
made of the user OID, the protocol and the verifier. This maps quite
well with pg_auth_verifiers.
- Give up and nuke pg_shadow, which is here for compatibility down to
8.1, and add a protocol column to pg_user, or even better create a new
view pg_user_verifiers that has all the data of all the protocols. If
we care a lot about backward-compatibility, pg_user could as well map
with pg_auth_verifiers with the md5 protocol.
I would go with the last one.
--
Michael