Re: [PoC/RFC] Multiple passwords, interval expirations

Greetings,

* Nathan Bossart (nathandbossart@gmail.com) wrote:
> On Wed, Oct 04, 2023 at 10:41:15PM -0700, Gurjeet Singh wrote:
> > The patches posted in this thread so far attempt to add the ability to
> > allow the user to have an arbitrary number of passwords. I believe
> > that allowing arbitrary number of passwords is not only unnecessary,
> > but the need to name passwords, the need to store them in a shared
> > catalog, etc. may actually create problems in the field. The
> > users/admins will have to choose names for passwords, which they
> > didn't have to previously. The need to name them may also lead to
> > users storing password-hints in the password names (e.g. 'mom''s
> > birthday', 'ex''s phone number', 'third password'), rendering the
> > passwords weak.
> >
> > Moreover, allowing an arbitrarily many number of passwords will
> > require us to provide additional infrastructure to solve problems like
> > observability (which passwords are currently in use, and which ones
> > have been effectively forgotten by applications), or create a nuisance
> > for admins that can create more problems than it solves.
>
> 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.  And adding
> observability for passwords seems worthwhile anyway.

Agreed, particularly on adding observability for password use.
Regardless of what we do, I feel pretty strongly that we need that.
That said, having this handled in a separate catalog feels like a just
generally better idea than shoving it all into pg_authid as we extend
things to include information like "last used date", "last used source
IP", etc.

Providing this observability purely through logging strikes me as a
terrible idea.

I don't find the concern about names as 'hints' to be at all compelling.
Having a way to avoid having names may have some value, but only if we
can come up with something reasonable.

> > So I propose that the feature should allow no more than 2 passwords
> > for a role, each with their own validity periods. This eliminates the
> > need to name passwords, because at any given time there are no more
> > than 2 passwords; current one, and old one. This also eliminates the
> > need for a shared catalog to hold passwords, because with the limit of
> > 2 imposed, we can store the old password and its validity period in
> > additional columns in the pg_authid table.
>
> Another approach could be to allow an abritrary number of passwords but to
> also allow administrators to limit how many passwords can be associated to
> each role.  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.

Agreed that it's a bad idea to design to support 2 and only 2.  If
nothing else, there's the very simple case that the user needs to do
another password rotation ... and they look and discover that the old
password is still being used and that if they took it away, things would
break, but they need time to run down which system is still using it
while still needing to perform the migration for the other systems that
are correctly being updated- boom, need 3 for that case.  There's other
use-cases that could be interesting though- presumably we'd log which
password is used to authenticate and then users could have a fleet of
web servers which each have their own password but log into the same PG
user and they could happily rotate the passwords independently for all
of those systems.

I don't propose this as some use-case just for the purpose of argument-
not sharing passwords across a bunch of systems is absolutely a good
stance when it comes to security, and due to the way permissions and
roles work in PG, being able to have both distinct passwords with
explicitly logged indication of which system used what password to log
in while not having to deal with possibly permission differences due to
using actually independent roles is valuable.  That is, each server
using a separate role to log in could lead to some servers having access
to something or other while others don't pretty easily- if they're all
logging in with the same role and just a different password, that's not
going to happen.

* Jeff Davis (pgsql@j-davis.com) wrote:
> 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

Yeah, a number doesn't strike me as very good either.

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

The validity idea is interesting but falls down when you want multiple
passwords that have the same validity period (or which all don't have
any expiration).

Giving users the option of not having to specify a name and letting the
system come up with one (similar to what we do for indexes and such)
could work out pretty decently, imv.  I'd have that be optional though-
if the user wants to specify a name, then they should be allowed to do
so.

> > 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.

Yup, +100 on this.

> 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):

I don't see logs or warnings as being at all useful for this kind of
thing.  Making it available through a view would work- but I don't think
we need to go into the deprecation language ourselves as part of the
grammer and instead should let users write their own queries against the
views we provide to see what passwords are being used and what aren't.

* Nathan Bossart (nathandbossart@gmail.com) wrote:
> On Thu, Oct 05, 2023 at 01:09:36PM -0700, Jeff Davis wrote:
> > On Thu, 2023-10-05 at 14:04 -0500, Nathan Bossart wrote:
> >> 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?
>
> I guess it's more of the latter.  Perhaps one potential use case would be
> short-lived credentials that are created on demand.  Such a password might
> only be valid for something like 15 minutes, and many users might have the
> ability to request a password for the database role.  I don't know whether
> there is a ton of demand for such a use case, and it might already be
> solvable by just creating separate roles.  In any case, if there's general
> agreement that we only want to target the rotation use case, that's fine by
> me.

Agreed, this seems like another good example of why we shouldn't design
this for just 2.

* Jeff Davis (pgsql@j-davis.com) wrote:
> The basic problem, as I see it, is: how do we keep users from
> accidentally dropping the wrong password? Generated unique names or
> numbers don't solve that problem. Auto-incrementing or a created-at
> timestamp solves it in the sense that you can at least look at a system
> view and see if there's a newer one, but it's a little awkward. A
> validity period is a good fit if all passwords have a validity period
> and we don't change it, but gets awkward otherwise.

Providing admins a way of seeing which passwords have been recently used
seems like a clear way to minimize, at least, the risk of the wrong
password being dropped.  Allowing them to control the name feels like
it's another good way to minimize the risk since it's something they can
define and they can include in the name whatever info they need to
figure out if it's the one that's no longer being used, or not.  Forcing
the use of numbers or of validation periods seems like it'd make it
harder on users to avoid this risk, not easier.

Allowing per-password expiration is another way to address the issue of
the wrong password being removed by essentially taking the password away
and seeing what breaks while having an easy way to bring it back if
something important stops working.

> I'm also worried about two kinds of clutter:
>
> * old passwords not being garbage-collected

I'm not terribly concerned with this if the password's validity has
passed and therefore it can't be used any longer.  I could agree with
the concern about it being an issue if the passwords all stay valid
forever.  I'll point out that we arguably have this problem with roles
already today and it doesn't seem to be a huge issue- PG has no idea how
long that user is actually valid for, the admin needs to have something
external to PG to deal with this.

> * the identifier of the current password always changing (perhaps fine
> if it'a a "created at" ID?)

I'm not following what the issue is you're getting at here.

Thanks,

Stephen