pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPassword complexities in Postgres v14.6
12+ messages / 9 participants
[nested] [flat]
* Password complexities in Postgres v14.6
@ 2022-12-16 12:27 Daulat <daulat.dba@gmail.com>
0 siblings, 3 replies; 12+ messages in thread
From: Daulat @ 2022-12-16 12:27 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
Hello Team,
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
Thanks,
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Password complexities in Postgres v14.6
@ 2022-12-16 13:28 Pepe TD Vo <pepevo@yahoo.com>
parent: Daulat <daulat.dba@gmail.com>
2 siblings, 0 replies; 12+ messages in thread
From: Pepe TD Vo @ 2022-12-16 13:28 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>; Daulat <daulat.dba@gmail.com>
you can configure the password policy plugin in postgresql.conf.
p_policy.min_password_len = 8 # Set minimum Password lengthp_policy.min_special_chars = 2 # Set minimum number of special charactersp_policy.min_numbers = 2 # Set minimum number of numeric charactersp_policy.min_uppercase_letter = 2 # Set minimum number of upper case lettersp_policy.min_lowercase_letter = 2 # Set minimum number of lower case letters
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love, and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
On Friday, December 16, 2022 at 07:28:05 AM EST, Daulat <daulat.dba@gmail.com> wrote:
Hello Team,
Any idea, how we can set some Password complexities inpostgres 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
Thanks,
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Password complexities in Postgres v14.6
@ 2022-12-16 14:55 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Daulat <daulat.dba@gmail.com>
2 siblings, 1 reply; 12+ messages in thread
From: Laurenz Albe @ 2022-12-16 14:55 UTC (permalink / raw)
To: Daulat <daulat.dba@gmail.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Password complexities in Postgres v14.6
@ 2022-12-16 15:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 12+ messages in thread
From: Tom Lane @ 2022-12-16 15:16 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Daulat <daulat.dba@gmail.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
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 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Password complexities in Postgres v14.6
@ 2022-12-16 15:41 Magnus Hagander <magnus@hagander.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 12+ messages in thread
From: Magnus Hagander @ 2022-12-16 15:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Daulat <daulat.dba@gmail.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
On Fri, Dec 16, 2022 at 4:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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
> 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).
>
There are of course better places to farm out the problem to that don't
(necessarily) have this problem. Using GSSAPI for example will never need
to send the password in clear, and it's up to the GSSAPI implementation to
handle the password complexity rules.
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.
>
The parts about enforcing a certain level of password complexity aren't
necessarily obsolete, *if* it is end user passwords and end users change
their password directly in the db. This is not a very common scenario, but
it can certainly be the case with e.g. analytics people who query the db
directly.
The part about requiring repeated password changes is considered actively
harmful these days, so it's definitely obsolete. (Note that this is
different from the postgres setting for VALID UNTIL which is not about the
password being valid until, it's about the entire user being valid until
the specified time).
And of course in either case a proper solution like using gssapi/kerberos
is the better choice.
--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Password complexities in Postgres v14.6
@ 2022-12-16 16:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: Magnus Hagander <magnus@hagander.net>
1 sibling, 1 reply; 12+ messages in thread
From: Tom Lane @ 2022-12-16 16:17 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Daulat <daulat.dba@gmail.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
Magnus Hagander <magnus@hagander.net> writes:
> On Fri, Dec 16, 2022 at 4:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> You can fairly easily enforce password age limits in PG using the
>> ALTER USER ... VALID UNTIL option.
> The part about requiring repeated password changes is considered actively
> harmful these days, so it's definitely obsolete. (Note that this is
> different from the postgres setting for VALID UNTIL which is not about the
> password being valid until, it's about the entire user being valid until
> the specified time).
No, VALID UNTIL only applies to the password; you can log in via
non-password-based auth mechanisms regardless of that.
(I agree that forced password rotations are also an obsolete security
practice, but figured that one bit of push-back at a time was enough.)
> And of course in either case a proper solution like using gssapi/kerberos
> is the better choice.
Yeah, migrating to something like that would be best practice.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Password complexities in Postgres v14.6
@ 2022-12-16 16:25 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Scott Ribe @ 2022-12-16 16:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Magnus Hagander <magnus@hagander.net>; Laurenz Albe <laurenz.albe@cybertec.at>; Daulat <daulat.dba@gmail.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Dec 16, 2022, at 9:17 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> ...
> (I agree that forced password rotations are also an obsolete security
> practice, but figured that one bit of push-back at a time was enough.)
I believe that NIST is now on board with this opinion, aren't they?
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Password complexities in Postgres v14.6
@ 2022-12-16 16:28 Magnus Hagander <magnus@hagander.net>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 12+ messages in thread
From: Magnus Hagander @ 2022-12-16 16:28 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Laurenz Albe <laurenz.albe@cybertec.at>; Daulat <daulat.dba@gmail.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
On Fri, Dec 16, 2022 at 5:26 PM Scott Ribe <scott_ribe@elevated-dev.com>
wrote:
> > On Dec 16, 2022, at 9:17 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > ...
>
> > (I agree that forced password rotations are also an obsolete security
> > practice, but figured that one bit of push-back at a time was enough.)
>
> I believe that NIST is now on board with this opinion, aren't they?
>
They have been for years.
--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Password complexities in Postgres v14.6
@ 2022-12-16 18:13 Holger Jakobs <holger@jakobs.com>
parent: Magnus Hagander <magnus@hagander.net>
1 sibling, 1 reply; 12+ messages in thread
From: Holger Jakobs @ 2022-12-16 18:13 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org, Magnus Hagander <magnus@hagander.net>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Daulat <daulat.dba@gmail.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
(Note that this is different from the postgres setting for VALID UNTIL which is not about the password being valid until, it's about the entire user being valid until the specified time).
This isn't true. It's really just the password becoming invalid and has no effect on other authentication methods.
Regards,
Holger
Am 16. Dezember 2022 16:41:39 MEZ schrieb Magnus Hagander <magnus@hagander.net>:
>On Fri, Dec 16, 2022 at 4:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> 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
>> 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).
>>
>
>There are of course better places to farm out the problem to that don't
>(necessarily) have this problem. Using GSSAPI for example will never need
>to send the password in clear, and it's up to the GSSAPI implementation to
>handle the password complexity rules.
>
>
>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.
>>
>
>The parts about enforcing a certain level of password complexity aren't
>necessarily obsolete, *if* it is end user passwords and end users change
>their password directly in the db. This is not a very common scenario, but
>it can certainly be the case with e.g. analytics people who query the db
>directly.
>
>The part about requiring repeated password changes is considered actively
>harmful these days, so it's definitely obsolete. (Note that this is
>different from the postgres setting for VALID UNTIL which is not about the
>password being valid until, it's about the entire user being valid until
>the specified time).
>
>And of course in either case a proper solution like using gssapi/kerberos
>is the better choice.
>
>--
> Magnus Hagander
> Me: https://www.hagander.net/ <http://www.hagander.net/;
> Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Password complexities in Postgres v14.6
@ 2022-12-16 19:51 Magnus Hagander <magnus@hagander.net>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 0 replies; 12+ messages in thread
From: Magnus Hagander @ 2022-12-16 19:51 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>; Laurenz Albe <laurenz.albe@cybertec.at>; Daulat <daulat.dba@gmail.com>
On Fri, Dec 16, 2022 at 7:13 PM Holger Jakobs <holger@jakobs.com> wrote:
> (Note that this is different from the postgres setting for VALID UNTIL
> which is not about the password being valid until, it's about the entire
> user being valid until the specified time).
>
> This isn't true. It's really just the password becoming invalid and has no
> effect on other authentication methods.
>
Yeah, you're right. I got it confused with another system I've been hacking
on recently, where it's the other way around. Sorry about that!
//Magnus
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Password complexities in Postgres v14.6
@ 2022-12-16 21:52 raf <raf@raf.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 12+ messages in thread
From: raf @ 2022-12-16 21:52 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On Fri, Dec 16, 2022 at 10:16:46AM -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Password complexities in Postgres v14.6
@ 2022-12-17 06:34 Ganesh Korde <ganeshakorde@gmail.com>
parent: Daulat <daulat.dba@gmail.com>
2 siblings, 0 replies; 12+ messages in thread
From: Ganesh Korde @ 2022-12-17 06:34 UTC (permalink / raw)
To: Daulat <daulat.dba@gmail.com>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi,
passwordcheck module can help you upto certain extent.
Regards,
Ganesh Korde.
On Fri, 16 Dec 2022, 5:58 pm Daulat, <daulat.dba@gmail.com> wrote:
> Hello Team,
>
> 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
>
> Thanks,
>
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2022-12-17 06:34 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-12-16 12:27 Password complexities in Postgres v14.6 Daulat <daulat.dba@gmail.com>
2022-12-16 13:28 ` Pepe TD Vo <pepevo@yahoo.com>
2022-12-16 14:55 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-12-16 15:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-12-16 15:41 ` Magnus Hagander <magnus@hagander.net>
2022-12-16 16:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-12-16 16:25 ` Scott Ribe <scott_ribe@elevated-dev.com>
2022-12-16 16:28 ` Magnus Hagander <magnus@hagander.net>
2022-12-16 18:13 ` Holger Jakobs <holger@jakobs.com>
2022-12-16 19:51 ` Magnus Hagander <magnus@hagander.net>
2022-12-16 21:52 ` raf <raf@raf.org>
2022-12-17 06:34 ` Ganesh Korde <ganeshakorde@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox