Re: [PoC/RFC] Multiple passwords, interval expirations - Mailing list pgsql-hackers

On Thu, 2023-10-05 at 14:04 -0500, Nathan Bossart wrote:
> IMHO neither of these problems seems insurmountable.  Besides
> advising
> against using hints as names, we could also automatically generate
> safe
> names, or even disallow user-provided names entirely.  

I'd like to see what this looks like as a user-interface. Using a name
seems weird because of the reasons Gurjeet mentioned.

Using a number seems weird to me because either:

 (a) if the number is always increasing you'd have to look to find the
number of the new slot to add and the old slot to remove; or
 (b) if switched between two numbers (say 0 and 1), it would be error
prone because you'd have to remember which is the old one that can be
safely replaced

Maybe a password is best described by its validity period rather than a
name? But what about passwords that don't expire?

> And adding
> observability for passwords seems worthwhile anyway.

That might be useful just to know whether a user's password is even
being used -- in case the admin makes a mistake and some other auth
method is being used. Also it would help to know when a password can
safely be removed.
>

>   That way, we needn't restrict this feature to 2 passwords for
> everyone.  Perhaps 2 should be the default, but in any case, IMO we
> shouldn't design to only support 2.

Are there use cases for lots of passwords, or is it just a matter of
not introducing an artificial limitation?

Would it ever make sense to have a role that has two permanent
passwords, or would that be an abuse of this feature? Any use cases I
can think of would be better solved with multiple user that are part of
the same group.

>
> I think this would be mighty confusing to users since it's not clear
> that
> adding a password will potentially invalidate a current password
> (which
> might be actively in use), but only if there are already 2 in the
> stack.  I
> worry that such a desіgn might be too closely tailored to the
> implementation details.  If we proceed with this design, perhaps we
> should
> consider ERROR-ing if a user tries to add a third password.

I agree that the proposed language is confusing, especially because ADD
causes a password to be added and another one to be removed. But
perhaps there are some non-confusing ways to expose a similar idea.

The thing I like about Gurjeet's proposal is that it's well-targeted at
a specific use case rather than trying to be too general. That makes it
a little easier to avoid certain problems like having a process that
adds passwords and never removes the old ones (leading to weird
problems like 47000 passwords for one user).

But it also feels strange to be limited to two -- perhaps the password
rotation schedule or policy just doesn't work with a limit of two, or
perhaps that introduces new kinds of mistakes.

Another idea: what if we introduce the notion of deprecating a
password? To remove a password, it would have to be deprecated first,
and maybe that would cause a LOG or WARNING message to be emitted when
used, or show up differently in some system view. And perhaps you could
have at most one non-deprecated password. That would give a workflow
something like (I'm not proposing these exact keywords):

  CREATE USER foo PASSWORD 'secret1';
  ALTER USER foo DEPRECATE PASSWORD; -- 'secret1' still works
  ALTER USER foo PASSWORD 'secret2'; -- 'secret1' or 'secret2' works
  ... fix some applications
  SET log_deprecated_password_use = WARNING;

  ...
  WARNING: deprecated password used for user 'foo'
  ... fix some applications you forgot about
  ... warnings quiet down
  ALTER USER foo DROP DEPRECATED PASSWORD; -- only 'secret2' works

If the user wants to un-deprecate a password (before they drop it, of
course), they can do something like:

  ALTER USER foo PASSWORD NULL; -- 'secret2' removed
  ALTER USER foo UNDEPRECATE PASSWORD; -- 'secret1' restored

if we allow multiple deprecated passwords, we'd still have to come up
with some way to address them (names, numbers, validity period,
something). But by isolating the problem to deprecated passwords only,
it feels like the system is still being restored to a clean state with
at most one single current password. The awkwardness is contained to
old passwords which will hopefully go away soon anyway and not
represent permanent clutter.

Regards,
    Jeff Davis