Date: Thu, 5 Oct 2023 14:04:53 -0500
From: Nathan Bossart <nathandbossart@gmail.com>
To: Gurjeet Singh <gurjeet@singh.im>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	Stephen Frost <sfrost@snowman.net>,
	"Brindle, Joshua" <joshuqbr@amazon.com>,
	Jacob Champion <jchampion@timescale.com>,
	Jeff Davis <pgsql@j-davis.com>
Subject: Re: [PoC/RFC] Multiple passwords, interval expirations
Message-ID: <20231005190453.GB151257@nathanxps13>
References: <CAGB+Vh5Ed65gCmjJxCYguVoBoiDLePYDAeHMkb6-7PR-DFU-yQ@mail.gmail.com>
 <b951f0c9-b568-4889-8418-b01cf5a3cea2@timescale.com>
 <CAOuzzgpyFRFbskZW0uYwP4_r5NkFJywTurn0SQ+pkbcuuQchjw@mail.gmail.com>
 <CABwTF4Uih+xKc8o7W-Qmzo2Oc3BbvnY+JHzcZ-HG2vDzBQpf8w@mail.gmail.com>
 <20220701002034.GA9030@tamriel.snowman.net>
 <80684017-18c6-4116-9249-bb049ffe1ecf@amazon.com>
 <CAOuzzgq2j43X6vwF8LCPcPuKZ2gw0ZbF=CrsB6CmtEt1h2ci0g@mail.gmail.com>
 <CABwTF4XZO_429=QzZHt-1Q=KSCR2XjYG+xVy2p-PdPtun9tVdg@mail.gmail.com>
 <41a690ee7b030e6f41709bd39375641ef934e05f.camel@j-davis.com>
 <CABwTF4Xf5qJgeapUvT3fbU+Er9sVKBEihcj7pb9_A4c5u1NaZA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <CABwTF4Xf5qJgeapUvT3fbU+Er9sVKBEihcj7pb9_A4c5u1NaZA@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20231005190453.GB151257%40nathanxps13>
Precedence: bulk

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.

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

> In essence, we create a stack that can hold 2 passwords. Pushing an
> element when it's full will make it forget the bottom element. Popping
> the stack makes it forget the top element, and the only remaining
> element, if any, becomes the top.

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.

> -- If, for some reason, the user wants to get rid of the latest password added.
> -- Remove 'p2' (current password). The old password (p1), will be restored to
> -- rolpassword, along with its valid-until value.
> ALTER ROLE u1 PASSWORD NULL;
> -- This may come as a surprise to some users, because currently they expect the
> -- user to completely lose the ability to use passwords for login after this
> -- command. To get the old behavior, the user must now use the ALL PASSWORD
> -- NULL incantation; see below.
> -- Issuing this command one more time will remove even the restored password,
> -- hence leaving the user with no passwords.

Is it possible to remove the oldest password added without removing the
latest password added?

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com