pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPassword authorization
13+ messages / 8 participants
[nested] [flat]
* Password authorization
@ 2022-01-20 09:56 Daulat <daulat.dba@gmail.com>
2022-01-20 10:06 ` Re: Password authorization Holger Jakobs <holger@jakobs.com>
2022-01-20 14:25 ` Re: Password authorization Laurenz Albe <laurenz.albe@cybertec.at>
2022-01-21 19:50 ` Re: Password authorization Stephen Frost <sfrost@snowman.net>
0 siblings, 3 replies; 13+ messages in thread
From: Daulat @ 2022-01-20 09:56 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Password authorization
2022-01-20 09:56 Password authorization Daulat <daulat.dba@gmail.com>
@ 2022-01-20 10:06 ` Holger Jakobs <holger@jakobs.com>
2022-01-20 11:11 ` Re: Password authorization Daulat <daulat.dba@gmail.com>
2 siblings, 1 reply; 13+ messages in thread
From: Holger Jakobs @ 2022-01-20 10:06 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
Attachments:
OpenPGP_signature (application/pgp-signature, 202B) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Password authorization
2022-01-20 09:56 Password authorization Daulat <daulat.dba@gmail.com>
2022-01-20 10:06 ` Re: Password authorization Holger Jakobs <holger@jakobs.com>
@ 2022-01-20 11:11 ` Daulat <daulat.dba@gmail.com>
2022-01-20 11:15 ` Re: Password authorization Holger Jakobs <holger@jakobs.com>
0 siblings, 1 reply; 13+ messages in thread
From: Daulat @ 2022-01-20 11:11 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
>
>
> --
> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Password authorization
2022-01-20 09:56 Password authorization Daulat <daulat.dba@gmail.com>
2022-01-20 10:06 ` Re: Password authorization Holger Jakobs <holger@jakobs.com>
2022-01-20 11:11 ` Re: Password authorization Daulat <daulat.dba@gmail.com>
@ 2022-01-20 11:15 ` Holger Jakobs <holger@jakobs.com>
2022-01-20 11:38 ` Re: Password authorization Daulat <daulat.dba@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Holger Jakobs @ 2022-01-20 11:15 UTC (permalink / raw)
To: Daulat <daulat.dba@gmail.com>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
Am 20.01.22 um 12:11 schrieb Daulat:
> 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
Attachments:
OpenPGP_signature (application/pgp-signature, 202B) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Password authorization
2022-01-20 09:56 Password authorization Daulat <daulat.dba@gmail.com>
2022-01-20 10:06 ` Re: Password authorization Holger Jakobs <holger@jakobs.com>
2022-01-20 11:11 ` Re: Password authorization Daulat <daulat.dba@gmail.com>
2022-01-20 11:15 ` Re: Password authorization Holger Jakobs <holger@jakobs.com>
@ 2022-01-20 11:38 ` Daulat <daulat.dba@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Daulat @ 2022-01-20 11:38 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
Thanks Holger,
Yes I know about this change.
But I am looking to work on 'Postgres Database User Authentication' . But
it looks like postgres has some limitations related to user authentication
and it's expiration.
So I am looking for a good approach in postgres , like in Oracle we have !
Thanks.
On Thu, Jan 20, 2022 at 4:45 PM Holger Jakobs <holger@jakobs.com> wrote:
> Am 20.01.22 um 12:11 schrieb Daulat:
>
> 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
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Password authorization
2022-01-20 09:56 Password authorization Daulat <daulat.dba@gmail.com>
@ 2022-01-20 14:25 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-01-20 16:24 ` Re: Password authorization Daulat <daulat.dba@gmail.com>
2022-01-21 14:10 ` Re: Password authorization Mladen Gogala <gogala.mladen@gmail.com>
2 siblings, 2 replies; 13+ messages in thread
From: Laurenz Albe @ 2022-01-20 14:25 UTC (permalink / raw)
To: Daulat <daulat.dba@gmail.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Password authorization
2022-01-20 09:56 Password authorization Daulat <daulat.dba@gmail.com>
2022-01-20 14:25 ` Re: Password authorization Laurenz Albe <laurenz.albe@cybertec.at>
@ 2022-01-20 16:24 ` Daulat <daulat.dba@gmail.com>
2022-01-20 22:52 ` Re: Password authorization Gavan Schneider <list.pg.gavan@pendari.org>
1 sibling, 1 reply; 13+ messages in thread
From: Daulat @ 2022-01-20 16:24 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
Thanks for your advice.
Yes, you are right, I am planning for password complexity rules and to
force users to change their password.
Will there be any performance impact using LDAP for postgres connection?
Also, please do suggest the following, which one will be the best option.
1) Have the application authenticate via standard Postgres
Authentication
2) Have other users authenticated via LDAP ?
Or could we have ALL users authenticate via LDAP to Postgres ? even the
application.
Thanks,
On Thu, Jan 20, 2022 at 7:55 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> 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
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Password authorization
2022-01-20 09:56 Password authorization Daulat <daulat.dba@gmail.com>
2022-01-20 14:25 ` Re: Password authorization Laurenz Albe <laurenz.albe@cybertec.at>
2022-01-20 16:24 ` Re: Password authorization Daulat <daulat.dba@gmail.com>
@ 2022-01-20 22:52 ` Gavan Schneider <list.pg.gavan@pendari.org>
2022-01-20 23:12 ` Re: Password authorization Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 13+ messages in thread
From: Gavan Schneider @ 2022-01-20 22:52 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Daulat <daulat.dba@gmail.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 Standards and Technology.
For me the most interesting aspect of the revised standard is how forcing password changes and complexity rules often leads to 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Password authorization
2022-01-20 09:56 Password authorization Daulat <daulat.dba@gmail.com>
2022-01-20 14:25 ` Re: Password authorization Laurenz Albe <laurenz.albe@cybertec.at>
2022-01-20 16:24 ` Re: Password authorization Daulat <daulat.dba@gmail.com>
2022-01-20 22:52 ` Re: Password authorization Gavan Schneider <list.pg.gavan@pendari.org>
@ 2022-01-20 23:12 ` Scott Ribe <scott_ribe@elevated-dev.com>
2022-01-21 08:26 ` Re: Password authorization Дмитрий Иванов <firstdismay@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Scott Ribe @ 2022-01-20 23:12 UTC (permalink / raw)
To: Gavan Schneider <list.pg.gavan@pendari.org>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
> 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 Standards and Technology.
>
> For me the most interesting aspect of the revised standard is how forcing password changes and complexity rules often leads to 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 dictionary word.
Still wondering what dictionary they were using...
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Password authorization
2022-01-20 09:56 Password authorization Daulat <daulat.dba@gmail.com>
2022-01-20 14:25 ` Re: Password authorization Laurenz Albe <laurenz.albe@cybertec.at>
2022-01-20 16:24 ` Re: Password authorization Daulat <daulat.dba@gmail.com>
2022-01-20 22:52 ` Re: Password authorization Gavan Schneider <list.pg.gavan@pendari.org>
2022-01-20 23:12 ` Re: Password authorization Scott Ribe <scott_ribe@elevated-dev.com>
@ 2022-01-21 08:26 ` Дмитрий Иванов <firstdismay@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Дмитрий Иванов @ 2022-01-21 08:26 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Gavan Schneider <list.pg.gavan@pendari.org>; pgsql-admin <pgsql-admin@lists.postgresql.org>
In general, it seems to me that many information security professionals
strengthen security for the sake of security itself, forgetting for a long
time about its original purpose. I have seen situations where password
checking rules were so complex that employees had to collectively come up
with predictable password generation schemes, which meant that passwords
were not only predictable in the historical context of the individual user,
but of the group as a whole.
--
Regards, Dmitry!
пт, 21 янв. 2022 г. в 04:12, Scott Ribe <scott_ribe@elevated-dev.com>:
> > 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 Standards and Technology.
> >
> > For me the most interesting aspect of the revised standard is how
> forcing password changes and complexity rules often leads to 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 dictionary word.
>
> Still wondering what dictionary they were using...
>
>
>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Password authorization
2022-01-20 09:56 Password authorization Daulat <daulat.dba@gmail.com>
2022-01-20 14:25 ` Re: Password authorization Laurenz Albe <laurenz.albe@cybertec.at>
@ 2022-01-21 14:10 ` Mladen Gogala <gogala.mladen@gmail.com>
2022-01-21 14:18 ` Re: Password authorization Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 13+ messages in thread
From: Mladen Gogala @ 2022-01-21 14:10 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
Regards
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Password authorization
2022-01-20 09:56 Password authorization Daulat <daulat.dba@gmail.com>
2022-01-20 14:25 ` Re: Password authorization Laurenz Albe <laurenz.albe@cybertec.at>
2022-01-21 14:10 ` Re: Password authorization Mladen Gogala <gogala.mladen@gmail.com>
@ 2022-01-21 14:18 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 13+ messages in thread
From: Laurenz Albe @ 2022-01-21 14:18 UTC (permalink / raw)
To: Mladen Gogala <gogala.mladen@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Password authorization
2022-01-20 09:56 Password authorization Daulat <daulat.dba@gmail.com>
@ 2022-01-21 19:50 ` Stephen Frost <sfrost@snowman.net>
2 siblings, 0 replies; 13+ messages in thread
From: Stephen Frost @ 2022-01-21 19:50 UTC (permalink / raw)
To: Daulat <daulat.dba@gmail.com>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2022-01-21 19:50 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-01-20 09:56 Password authorization Daulat <daulat.dba@gmail.com>
2022-01-20 10:06 ` Holger Jakobs <holger@jakobs.com>
2022-01-20 11:11 ` Daulat <daulat.dba@gmail.com>
2022-01-20 11:15 ` Holger Jakobs <holger@jakobs.com>
2022-01-20 11:38 ` Daulat <daulat.dba@gmail.com>
2022-01-20 14:25 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-01-20 16:24 ` Daulat <daulat.dba@gmail.com>
2022-01-20 22:52 ` Gavan Schneider <list.pg.gavan@pendari.org>
2022-01-20 23:12 ` Scott Ribe <scott_ribe@elevated-dev.com>
2022-01-21 08:26 ` Дмитрий Иванов <firstdismay@gmail.com>
2022-01-21 14:10 ` Mladen Gogala <gogala.mladen@gmail.com>
2022-01-21 14:18 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-01-21 19:50 ` Stephen Frost <sfrost@snowman.net>
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