Обсуждение: Password authorization

Am 20.01.22 um 10:56 schrieb Daulat:
> Hi Team,
>
> Do we have any facility for password authorisation in Postgres same as 
> in Oracle?
> In Oracle, a user can change their password based upon the 
> profile setting. What about in Postgres ?
>
> Is there any alternative in postgres to manage the user password 
> authorisation?
> What about LDAP with respect to this approach?
> Please suggest.
>
> Thanks.
>
>

https://www.postgresql.org/docs/14/sql-alterrole.html contains the sentence:

     Ordinary roles can only change their own password.


-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

Hi Holger,

Is there any change related to password management in Postgres14 with respect to previous releases. I don't see anything new.

Thanks

On Thu, Jan 20, 2022 at 3:36 PM Holger Jakobs <holger@jakobs.com> wrote:

Am 20.01.22 um 10:56 schrieb Daulat:
> Hi Team,
>
> Do we have any facility for password authorisation in Postgres same as
> in Oracle?
> In Oracle, a user can change their password based upon the
> profile setting. What about in Postgres ?
>
> Is there any alternative in postgres to manage the user password
> authorisation?
> What about LDAP with respect to this approach?
> Please suggest.
>
> Thanks.
>
>

https://www.postgresql.org/docs/14/sql-alterrole.html contains the sentence:

     Ordinary roles can only change their own password.

Yes, there is. The default password hashing method has changed in postgresql.conf:

Version 13:
#password_encryption = md5        # md5 or scram-sha-256

Version 14:
#password_encryption = scram-sha-256    # scram-sha-256 or md5

-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

On Thu, 2022-01-20 at 15:26 +0530, Daulat wrote:
> Do we have any facility for password authorisation in Postgres same as in Oracle?
> In Oracle, a user can change their password based upon the profile setting. What about in Postgres ? 
> 
> Is there any alternative in postgres to manage the user password authorisation? 
> What about LDAP with respect to this approach?
> Please suggest.

It is not quite clear what you mean, but I assume you want password complexity rules,
and you want to force users to change their password after a while.

You cannot do any of that with passwords in the database; the best you can
do is set an expiration time for the user's ability to log in.

With requirements as this, you should look into external password
authentication like Kerberos or LDAP - you'll have to look at that server's
documentation for more.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

On 21 Jan 2022, at 3:24, Daulat wrote:

> Yes, you are right, I am planning for password complexity rules and to, force users to change their password.
>
While you are in the planning stages you may wish to review current best practice, e.g., USA National Institute of
Standardsand Technology. 

For me the most interesting aspect of the revised standard is how forcing password changes and complexity rules often
leadsto reduced security in the real world. 

Refer:
https://pages.nist.gov/800-63-3/sp800-63-3.html
https://auth0.com/blog/dont-pass-on-the-new-nist-password-guidelines/ (for a more human readable version :)

Regards

Gavan Schneider
——
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a well-known solution to every human problem —
neat,plausible, and wrong. 
— H. L. Mencken, 1920

> On Jan 20, 2022, at 3:52 PM, Gavan Schneider <list.pg.gavan@pendari.org> wrote:
>
> On 21 Jan 2022, at 3:24, Daulat wrote:
>
>> Yes, you are right, I am planning for password complexity rules and to, force users to change their password.
>>
> While you are in the planning stages you may wish to review current best practice, e.g., USA National Institute of
Standardsand Technology. 
>
> For me the most interesting aspect of the revised standard is how forcing password changes and complexity rules often
leadsto reduced security in the real world. 
>
> Refer:
> https://pages.nist.gov/800-63-3/sp800-63-3.html
> https://auth0.com/blog/dont-pass-on-the-new-nist-password-guidelines/ (for a more human readable version :)
>
> Regards
>
> Gavan Schneider

Slightly off-topic, but I once ran into a system that would not allow kk1bsk#$ as a password because it contained a
dictionaryword. 

Still wondering what dictionary they were using...

It is not quite clear what you mean, but I assume you want password complexity rules,
and you want to force users to change their password after a while.

You cannot do any of that with passwords in the database; the best you can
do is set an expiration time for the user's ability to log in.

Actually, you can. There is the standard "passwordcheck" module described in the documentation:

https://www.postgresql.org/docs/current/passwordcheck.html

You would have to do some C programming, but it's very possible.

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

On Fri, 2022-01-21 at 09:10 -0500, Mladen Gogala wrote:
> On 1/20/22 09:25, Laurenz Albe wrote:
>  
> > It is not quite clear what you mean, but I assume you want password complexity rules,
> > and you want to force users to change their password after a while.
> > 
> > You cannot do any of that with passwords in the database; the best you can
> > do is set an expiration time for the user's ability to log in.

> Actually, you can. There is the standard "passwordcheck" module described in the documentation:
> https://www.postgresql.org/docs/current/passwordcheck.html
> You would have to do some C programming, but it's very possible.

That is no solution, because it cannot check hashed passwords
(except with cracklib).  I wrote it.

Laurenz Albe

Greetings,

* Daulat (daulat.dba@gmail.com) wrote:
> Is there any alternative in postgres to manage the user password
> authorisation?
> What about LDAP with respect to this approach?

PostgreSQL supports GSSAPI / Kerberos, which is a much better approach
in Active Directory and other environments where Kerberos is deployed.

Using LDAP exposes the user's password to the database server and
therefore isn't secure and should be strongly discouraged.

You can certainly have applications authenticate to PostgreSQL using
Kerberos too with very little overhead (unlike PG's LDAP authentication
option, which makes a synchronous call out to the LDAP server on each
and every login).

Thanks,

Stephen