On Fri, Dec 16, 2022 at 10:16:46AM -0500, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
> Laurenz Albe <laurenz.albe@xxxxxxxxxxx> writes:
> > On Fri, 2022-12-16 at 17:57 +0530, Daulat wrote:
> >> Any idea, how we can set some Password complexities in postgres for user password. Like, we can create profiles in Oracle.
> >> I am looking to set the Password complexities (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 ~" &_-+='! (){}[):;"'<>,.?/ !@#$%*
> >> Password Minimum Length 8 character
>
> > There is no reliable way to do this in PostgreSQL, since the server typically
> > 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
Just in case anyone still thinks that the decades old
advice on password complexity has any validity, here's
an article that explains why it's awful (short answer:
given a set of rules, we all do very similar things,
resulting in a password search space that is a lot
smaller than you would think, so it makes password
hashes easier to crack).
https://www.rapid7.com/blog/post/2018/06/12/password-tips-from-a-pen-tester-common-patterns-exposed/
This isn't the first article on this topic, but it's
the one that came up first when googling for "password
common patterns". There's a more detailed earlier one
somewhere (Mozilla? OWASP?) that lists the 100 most
common password patterns, the most common one being
used by about 12% of people when forced to follow
typical password complexity rules. The old rules really
do make security worse.
cheers,
raf
