From: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
cc: Daulat <daulat.dba@gmail.com>,
        pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Password complexities in Postgres v14.6
In-reply-to: <2192ed923b577ce4a8a90479c7ed1c97f7a6523d.camel@cybertec.at>
References: 
 <CALfOM6LjVBhpHg+6nVe6reOBYdPJZXvXhkp+dqquG9cVBsGPmQ@mail.gmail.com>
 <2192ed923b577ce4a8a90479c7ed1c97f7a6523d.camel@cybertec.at>
Comments: In-reply-to Laurenz Albe <laurenz.albe@cybertec.at>
	message dated "Fri, 16 Dec 2022 15:55:09 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <3675886.1671203806.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 16 Dec 2022 10:16:46 -0500
Message-ID: <3675887.1671203806@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3675887.1671203806%40sss.pgh.pa.us>
Precedence: bulk

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Fri, 2022-12-16 at 17:57 +0530, Daulat wrote:
>> Any idea, how we can set some Password complexities in postgres for use=
r password. Like, we can create profiles in Oracle. =

>> I am looking to set the Password complexities=C2=A0 (one parameter from=
 each line item has to be complied to):
>> Default password age for users: 90 days. =

>> Password first letter will be alphabetic in uppercase.
>> English uppercase characters (A through Z)
>> English lowercase characters (a through z)
>> Base 10 digits (0 through 9)
>> Non-alphabetic characters ~" &_-+=3D'! (){}[):;"'<>,.?/ !@#$%*
>> Password Minimum Length 8 character

> There is no reliable way to do this in PostgreSQL, since the server typi=
cally
> never sees the clear text password.
> You should consider using one of the other authentication methods like "=
ldap"
> and enforce the policy on the LDAP server.

Note that this approach typically leads to a net worsening of security.
Farming out the problem to LDAP means that the password has to be sent
in cleartext not only to the PG server, but then on to the LDAP server
(and in an awful lot of setups, that second hop isn't even done in an
encrypted connection).

You can fairly easily enforce password age limits in PG using the
ALTER USER ... VALID UNTIL option.  But for all this other stuff,
there is no way to enforce it at the server without sending passwords
in cleartext, which reduces security rather than increasing it.

In short: your security guidelines are obsolete and need an update.

			regards, tom lane