Thread: [GENERAL] USER Profiles for PostgreSQL
Hi All,
Good Morning.
We are looking for User profiles in ope source PostgreSQL.
For example, If a user password failed n+ times while login ,the user access has to be blocked few seconds.
Please let us know, is there any plan to implement user profiles in feature releases?.
Thanks,
Chiranjeevi
chiru r <chirupg@gmail.com> writes:
> We are looking for User profiles in ope source PostgreSQL.
> For example, If a user password failed n+ times while login ,the user
> access has to be blocked few seconds.
> Please let us know, is there any plan to implement user profiles in feature
> releases?.
Not particularly. You can do that sort of thing already via PAM,
for example.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Tom, * Tom Lane (tgl@sss.pgh.pa.us) wrote: > chiru r <chirupg@gmail.com> writes: > > We are looking for User profiles in ope source PostgreSQL. > > For example, If a user password failed n+ times while login ,the user > > access has to be blocked few seconds. > > Please let us know, is there any plan to implement user profiles in feature > > releases?. > > Not particularly. You can do that sort of thing already via PAM, > for example. Ugh, hardly and it's hokey and a huge pain to do, and only works on platforms that have PAM. Better is to use an external authentication system (Kerberos, for example) which can deal with this, but I do think this is also something we should be considering for core, especially now that we've got a reasonable password-based authentication method with SCRAM. Thanks! Stephen
On Tue, Sep 19, 2017 at 1:28 PM, Stephen Frost <sfrost@snowman.net> wrote:
Tom,
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> chiru r <chirupg@gmail.com> writes:
> > We are looking for User profiles in ope source PostgreSQL.
> > For example, If a user password failed n+ times while login ,the user
> > access has to be blocked few seconds.
> > Please let us know, is there any plan to implement user profiles in feature
> > releases?.
>
> Not particularly. You can do that sort of thing already via PAM,
> for example.
Ugh, hardly and it's hokey and a huge pain to do, and only works on
platforms that have PAM.
Better is to use an external authentication system (Kerberos, for
example) which can deal with this, but I do think this is also something
we should be considering for core, especially now that we've got a
reasonable password-based authentication method with SCRAM.
Thanks!
Stephen
Perhaps, as an alternative, although not currently supported, connection attempts can be added in the future to "Event Triggers"?
Users could then create a trigger function to enable/disable logins.
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On Tue, Sep 19, 2017 at 01:28:11PM -0400, Stephen Frost wrote: > Tom, > > * Tom Lane (tgl@sss.pgh.pa.us) wrote: > > chiru r <chirupg@gmail.com> writes: > > > We are looking for User profiles in ope source PostgreSQL. > > > For example, If a user password failed n+ times while login ,the user > > > access has to be blocked few seconds. > > > Please let us know, is there any plan to implement user profiles in feature > > > releases?. > > > > Not particularly. You can do that sort of thing already via PAM, > > for example. > > Ugh, hardly and it's hokey and a huge pain to do, and only works on > platforms that have PAM. > > Better is to use an external authentication system (Kerberos, for > example) which can deal with this, but I do think this is also something > we should be considering for core, especially now that we've got a > reasonable password-based authentication method with SCRAM. Does LDAP do this too? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Yes, LDAP will do. However we need to sync the user accounts and groups between AD and PG servers.and then AD profiles will apply to PG user accounts for authentication.
It is good if we have user profiles in core PostgreSQL database system. So it will add more security.
Thanks,
Chiranjeevi
On Tue, Sep 19, 2017 at 3:09 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Tue, Sep 19, 2017 at 01:28:11PM -0400, Stephen Frost wrote:
> Tom,
>
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> > chiru r <chirupg@gmail.com> writes:
> > > We are looking for User profiles in ope source PostgreSQL.
> > > For example, If a user password failed n+ times while login ,the user
> > > access has to be blocked few seconds.
> > > Please let us know, is there any plan to implement user profiles in feature
> > > releases?.
> >
> > Not particularly. You can do that sort of thing already via PAM,
> > for example.
>
> Ugh, hardly and it's hokey and a huge pain to do, and only works on
> platforms that have PAM.
>
> Better is to use an external authentication system (Kerberos, for
> example) which can deal with this, but I do think this is also something
> we should be considering for core, especially now that we've got a
> reasonable password-based authentication method with SCRAM.
Does LDAP do this too?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
On 9/19/2017 12:33 PM, chiru r wrote: > Yes, LDAP will do. However we need to sync the user accounts and > groups between AD and PG servers.and then AD profiles will apply to > PG user accounts for authentication. > if you're using LDAP from the AD servers to authenticate, whats to sync? my database servers, the only 'users' connecting to them directly are the database administrators... the applications connect with application accounts, and if more security is required, these use certificates, or they use unix 'ident' local connections. -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Hi John,
How those application accounts get recognized in database?
Let say App_user1 authenticated through application ,after that how the App_user1 get access to DB?
can you please provide more information ,how the app users are accessing database ?
Thanks,
Chiranjeevi
On Tue, Sep 19, 2017 at 3:45 PM, John R Pierce <pierce@hogranch.com> wrote:
On 9/19/2017 12:33 PM, chiru r wrote:Yes, LDAP will do. However we need to sync the user accounts and groups between AD and PG servers.and then AD profiles will apply to PG user accounts for authentication.
if you're using LDAP from the AD servers to authenticate, whats to sync?
my database servers, the only 'users' connecting to them directly are the database administrators... the applications connect with application accounts, and if more security is required, these use certificates, or they use unix 'ident' local connections.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 9/19/2017 3:32 PM, chiru r wrote: > How those application accounts get recognized in database? > > Let say App_user1 authenticated through application ,after that how > the App_user1 get access to DB? > > can you please provide more information ,how the app users are > accessing database ? the user isn't accessing the database, the application itself is accessing the database. a web app might have 10s of 1000s of unique users, but a web app typically only uses a single application account to access the database. -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 19/09/2017 20:09, Tom Lane wrote: > chiru r <chirupg@gmail.com> writes: >> We are looking for User profiles in ope source PostgreSQL. >> For example, If a user password failed n+ times while login ,the user >> access has to be blocked few seconds. >> Please let us know, is there any plan to implement user profiles in feature >> releases?. > Not particularly. You can do that sort of thing already via PAM, > for example. Or LDAP as in our case. > > regards, tom lane > > -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Bruce, * Bruce Momjian (bruce@momjian.us) wrote: > On Tue, Sep 19, 2017 at 01:28:11PM -0400, Stephen Frost wrote: > > * Tom Lane (tgl@sss.pgh.pa.us) wrote: > > > chiru r <chirupg@gmail.com> writes: > > > > We are looking for User profiles in ope source PostgreSQL. > > > > For example, If a user password failed n+ times while login ,the user > > > > access has to be blocked few seconds. > > > > Please let us know, is there any plan to implement user profiles in feature > > > > releases?. > > > > > > Not particularly. You can do that sort of thing already via PAM, > > > for example. > > > > Ugh, hardly and it's hokey and a huge pain to do, and only works on > > platforms that have PAM. > > > > Better is to use an external authentication system (Kerberos, for > > example) which can deal with this, but I do think this is also something > > we should be considering for core, especially now that we've got a > > reasonable password-based authentication method with SCRAM. > > Does LDAP do this too? Active Directory does this, with Kerberos as the authentication mechanism. Straight LDAP might also support it, but I wouldn't recommend it because it's really insecure as the PG server will see the user's password in the cleartext (and it may be sent in cleartext across the network too unless careful steps are taken to make sure that the client only ever connects over SSL to a known trusted and verified server). Thanks! Stephen
John, * John R Pierce (pierce@hogranch.com) wrote: > On 9/19/2017 3:32 PM, chiru r wrote: > >How those application accounts get recognized in database? > > > >Let say App_user1 authenticated through application ,after that > >how the App_user1 get access to DB? > > > >can you please provide more information ,how the app users are > >accessing database ? > > the user isn't accessing the database, the application itself is > accessing the database. a web app might have 10s of 1000s of > unique users, but a web app typically only uses a single application > account to access the database. While that's one approach, it's certainly not the only one (and not necessairly considered a good approach either..). If AD is in the mix here, then there's no need to have things happening at the database level when it comes to passwords- configure PG to use Kerberos and create a princ in AD and put that on the database server and then users can authenticate that way. Further, applications can be configured to accept and proxy Kerberos credentials from the user to authenticate with to the database, allowing the application to actually be logged in as the user for that session, with only those rights the user has. Group/Role membership does still have to be sync'd between the AD/LDAP directory and the database for those permissions to be set up, but that isn't all that hard to do using a cronjob and ldap_fdw, or similar, people just have to realize that there's a bit of lag. The same goes for creating accounts in the first place in the database, of course. Thanks! Stephen
On 9/20/2017 6:55 AM, Stephen Frost wrote: > If AD is in the mix here, then there's no need to have things happening > at the database level when it comes to passwords- configure PG to use > Kerberos and create a princ in AD and put that on the database server > and then users can authenticate that way. for web apps? how does a web browser do kerberos over http ? -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Greetings John, * John R Pierce (pierce@hogranch.com) wrote: > On 9/20/2017 6:55 AM, Stephen Frost wrote: > >If AD is in the mix here, then there's no need to have things happening > >at the database level when it comes to passwords- configure PG to use > >Kerberos and create a princ in AD and put that on the database server > >and then users can authenticate that way. > > for web apps? how does a web browser do kerberos over http ? Yes, and using SPNEGO, which the major browsers have all supported for a very long time. Thanks! Stephen