pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUser to get locked after three wrong login attempts.
9+ messages / 7 participants
[nested] [flat]
* User to get locked after three wrong login attempts.
@ 2018-09-04 07:48 Praneel Devisetty <devisettypraneel@gmail.com>
2018-09-04 14:06 ` Re: User to get locked after three wrong login attempts. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Praneel Devisetty @ 2018-09-04 07:48 UTC (permalink / raw)
To: pgsql-admin
Hi Team,
We have a requirement , where we require a user to get locked after three
wrong login attempts.
I have gone through several online documents, but couldn't find any
relevant information.
We are using postrges 10.4 on Rhel 7.5
Kindly suggest .
Thanks & Regards
Praneel
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: User to get locked after three wrong login attempts.
2018-09-04 07:48 User to get locked after three wrong login attempts. Praneel Devisetty <devisettypraneel@gmail.com>
@ 2018-09-04 14:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-09-05 17:48 ` Re: User to get locked after three wrong login attempts. Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2018-09-04 14:06 UTC (permalink / raw)
To: Praneel Devisetty <devisettypraneel@gmail.com>; +Cc: pgsql-admin
Praneel Devisetty <devisettypraneel@gmail.com> writes:
> We have a requirement , where we require a user to get locked after three
> wrong login attempts.
The usual recommendation is to configure Postgres to use PAM
authentication; then you can set up any weird requirements like
this one in the PAM configuration.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: User to get locked after three wrong login attempts.
2018-09-04 07:48 User to get locked after three wrong login attempts. Praneel Devisetty <devisettypraneel@gmail.com>
2018-09-04 14:06 ` Re: User to get locked after three wrong login attempts. Tom Lane <tgl@sss.pgh.pa.us>
@ 2018-09-05 17:48 ` Stephen Frost <sfrost@snowman.net>
2018-09-05 22:09 ` Re: User to get locked after three wrong login attempts. Tim Cross <theophilusx@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Stephen Frost @ 2018-09-05 17:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Praneel Devisetty <devisettypraneel@gmail.com>; pgsql-admin
Greetings,
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Praneel Devisetty <devisettypraneel@gmail.com> writes:
> > We have a requirement , where we require a user to get locked after three
> > wrong login attempts.
>
> The usual recommendation is to configure Postgres to use PAM
> authentication; then you can set up any weird requirements like
> this one in the PAM configuration.
Unfortunately, it's a pain to set up PAM and there's a lot of things in
the PAM stack which can't be used because PostgreSQL doesn't run as
root. We should really have a better solution to this pretty commonly
asked for capability; I'm hoping to find time soon to hack on that.
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: User to get locked after three wrong login attempts.
2018-09-04 07:48 User to get locked after three wrong login attempts. Praneel Devisetty <devisettypraneel@gmail.com>
2018-09-04 14:06 ` Re: User to get locked after three wrong login attempts. Tom Lane <tgl@sss.pgh.pa.us>
2018-09-05 17:48 ` Re: User to get locked after three wrong login attempts. Stephen Frost <sfrost@snowman.net>
@ 2018-09-05 22:09 ` Tim Cross <theophilusx@gmail.com>
2018-09-05 22:14 ` Re: User to get locked after three wrong login attempts. Craig James <cjames@emolecules.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tim Cross @ 2018-09-05 22:09 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Praneel Devisetty <devisettypraneel@gmail.com>; pgsql-admin
Stephen Frost <sfrost@snowman.net> writes:
> Greetings,
>
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> Praneel Devisetty <devisettypraneel@gmail.com> writes:
>> > We have a requirement , where we require a user to get locked after three
>> > wrong login attempts.
>>
>> The usual recommendation is to configure Postgres to use PAM
>> authentication; then you can set up any weird requirements like
>> this one in the PAM configuration.
>
> Unfortunately, it's a pain to set up PAM and there's a lot of things in
> the PAM stack which can't be used because PostgreSQL doesn't run as
> root. We should really have a better solution to this pretty commonly
> asked for capability; I'm hoping to find time soon to hack on that.
>
> Thanks!
>
> Stephen
These days, I think the better solution is to have this functionality in
a central system. Putting aside that it is an 'outdated' auditor
requirement, what the auditor really wants to see is that access to ALL
systems is locked after 3 failed authentication attempts (for a period
e.g. 5 minutes). Having a centralised system also has the benefit of
'same login', so your users have the same username and password across
all services in the organisation and 1 central and consistent place for
password management.
I would suggest looking at what can be achieved with oepnLDAP and/or
Active Directory/Kerberos.
Note that the tricky part with this approach in the era of multiple
devices is getting the parameters tweaked correctly. It is not as easy
as just saying 'after 3 failed logins, lock the account'. You need to
consider what happens when someone changes their password and has
multiple devices logged into different services (e.g. mail). As soon as
the password has changed, some of these devices will begin to fail and
this will happen before the user can open each device and change the
password. If the policy is to restrictive, by the time they do this,
their account is locked and they cannot change the password - now they
are caught in a vicious cycle. Most lockout mechanisms have parameters
you can set to avoid this issue.
Tim
--
Tim Cross
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: User to get locked after three wrong login attempts.
2018-09-04 07:48 User to get locked after three wrong login attempts. Praneel Devisetty <devisettypraneel@gmail.com>
2018-09-04 14:06 ` Re: User to get locked after three wrong login attempts. Tom Lane <tgl@sss.pgh.pa.us>
2018-09-05 17:48 ` Re: User to get locked after three wrong login attempts. Stephen Frost <sfrost@snowman.net>
2018-09-05 22:09 ` Re: User to get locked after three wrong login attempts. Tim Cross <theophilusx@gmail.com>
@ 2018-09-05 22:14 ` Craig James <cjames@emolecules.com>
2018-09-05 22:18 ` Re: User to get locked after three wrong login attempts. Ron <ronljohnsonjr@gmail.com>
2018-09-05 22:28 ` Re: User to get locked after three wrong login attempts. Tim Cross <theophilusx@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Craig James @ 2018-09-05 22:14 UTC (permalink / raw)
To: Tim Cross <theophilusx@gmail.com>; +Cc: Stephen Frost <sfrost@snowman.net>; Tom Lane <tgl@sss.pgh.pa.us>; Praneel Devisetty <devisettypraneel@gmail.com>; pgsql-admin
On Wed, Sep 5, 2018 at 3:09 PM, Tim Cross <theophilusx@gmail.com> wrote:
>
> Stephen Frost <sfrost@snowman.net> writes:
>
> > Greetings,
> >
> > * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> >> Praneel Devisetty <devisettypraneel@gmail.com> writes:
> >> > We have a requirement , where we require a user to get locked after
> three
> >> > wrong login attempts.
> >>
> >> The usual recommendation is to configure Postgres to use PAM
> >> authentication; then you can set up any weird requirements like
> >> this one in the PAM configuration.
> >
> > Unfortunately, it's a pain to set up PAM and there's a lot of things in
> > the PAM stack which can't be used because PostgreSQL doesn't run as
> > root. We should really have a better solution to this pretty commonly
> > asked for capability; I'm hoping to find time soon to hack on that.
> >
> > Thanks!
> >
> > Stephen
>
> These days, I think the better solution is to have this functionality in
> a central system. Putting aside that it is an 'outdated' auditor
> requirement ...
To elaborate, you should explain to the auditor that this introduces a huge
denial-of-service vulnerability into your system. Anyone can start
hammering on everyone else's accounts, and with a fairly trivial script,
lock the entire company out of all accounts. This is a terrible idea.
Craig
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: User to get locked after three wrong login attempts.
2018-09-04 07:48 User to get locked after three wrong login attempts. Praneel Devisetty <devisettypraneel@gmail.com>
2018-09-04 14:06 ` Re: User to get locked after three wrong login attempts. Tom Lane <tgl@sss.pgh.pa.us>
2018-09-05 17:48 ` Re: User to get locked after three wrong login attempts. Stephen Frost <sfrost@snowman.net>
2018-09-05 22:09 ` Re: User to get locked after three wrong login attempts. Tim Cross <theophilusx@gmail.com>
2018-09-05 22:14 ` Re: User to get locked after three wrong login attempts. Craig James <cjames@emolecules.com>
@ 2018-09-05 22:18 ` Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Ron @ 2018-09-05 22:18 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 09/05/2018 05:14 PM, Craig James wrote:
[snip]
> To elaborate, you should explain to the auditor that this introduces a
> huge denial-of-service vulnerability into your system. Anyone can start
> hammering on everyone else's accounts, and with a fairly trivial script,
> lock the entire company out of all accounts. This is a terrible idea.
And be tracked down (relatively) quickly.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: User to get locked after three wrong login attempts.
2018-09-04 07:48 User to get locked after three wrong login attempts. Praneel Devisetty <devisettypraneel@gmail.com>
2018-09-04 14:06 ` Re: User to get locked after three wrong login attempts. Tom Lane <tgl@sss.pgh.pa.us>
2018-09-05 17:48 ` Re: User to get locked after three wrong login attempts. Stephen Frost <sfrost@snowman.net>
2018-09-05 22:09 ` Re: User to get locked after three wrong login attempts. Tim Cross <theophilusx@gmail.com>
2018-09-05 22:14 ` Re: User to get locked after three wrong login attempts. Craig James <cjames@emolecules.com>
@ 2018-09-05 22:28 ` Tim Cross <theophilusx@gmail.com>
2018-09-05 22:40 ` Re: User to get locked after three wrong login attempts. Ron <ronljohnsonjr@gmail.com>
2018-09-06 06:42 ` Re: User to get locked after three wrong login attempts. Achilleas Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 2 replies; 9+ messages in thread
From: Tim Cross @ 2018-09-05 22:28 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: Stephen Frost <sfrost@snowman.net>; Tom Lane <tgl@sss.pgh.pa.us>; Praneel Devisetty <devisettypraneel@gmail.com>; pgsql-admin
Craig James <cjames@emolecules.com> writes:
> On Wed, Sep 5, 2018 at 3:09 PM, Tim Cross <theophilusx@gmail.com> wrote:
>
>>
>> Stephen Frost <sfrost@snowman.net> writes:
>>
>> > Greetings,
>> >
>> > * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> >> Praneel Devisetty <devisettypraneel@gmail.com> writes:
>> >> > We have a requirement , where we require a user to get locked after
>> three
>> >> > wrong login attempts.
>> >>
>> >> The usual recommendation is to configure Postgres to use PAM
>> >> authentication; then you can set up any weird requirements like
>> >> this one in the PAM configuration.
>> >
>> > Unfortunately, it's a pain to set up PAM and there's a lot of things in
>> > the PAM stack which can't be used because PostgreSQL doesn't run as
>> > root. We should really have a better solution to this pretty commonly
>> > asked for capability; I'm hoping to find time soon to hack on that.
>> >
>> > Thanks!
>> >
>> > Stephen
>>
>> These days, I think the better solution is to have this functionality in
>> a central system. Putting aside that it is an 'outdated' auditor
>> requirement ...
>
>
> To elaborate, you should explain to the auditor that this introduces a huge
> denial-of-service vulnerability into your system. Anyone can start
> hammering on everyone else's accounts, and with a fairly trivial script,
> lock the entire company out of all accounts. This is a terrible idea.
>
Unfortunately, that is a reflection of the poor standard of most
auditors. They are rarely technical people and just follow a rule
book. Most of their rules are outdated and many are wrong. For example,
many still require 'complex' passwords consisting of mixed case,
punctuation/special characters etc. This is despite the fact the person
who originally proposed such a scheme has actually come out and
apologised and said he had it wrong (plus this 'standard' was removed
from NIST standards over 2 years ago) and ignores the changes in
technologies which has changed the threat (i.e. rainbow tables etc now
mean length is far more important than complexity).
The 'trick' with auditors is to only answer what they ask and answer in
such a way that what you say is true, but perhaps open to favourable
interpretation. e.g.
Auditor: do your accounts get locked after X failed login attempts
Answer: We use Active directory for our Windows domain, which has the
failed login policy enabled.
Auditor: Ah yes, I know about that - good, I will mark you as
compliant.
rather than
Answer: Well sort of. We have AD for our windows accounts which has the
failed login policy enabled, but some of our systems, like Postgres,
don't use that service.
Auditor: So do you get locked if you try to login to postgres and fail X
times
Answer: No
Auditor: Oh dear, I will have to mark you as non-compliant.
--
Tim Cross
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: User to get locked after three wrong login attempts.
2018-09-04 07:48 User to get locked after three wrong login attempts. Praneel Devisetty <devisettypraneel@gmail.com>
2018-09-04 14:06 ` Re: User to get locked after three wrong login attempts. Tom Lane <tgl@sss.pgh.pa.us>
2018-09-05 17:48 ` Re: User to get locked after three wrong login attempts. Stephen Frost <sfrost@snowman.net>
2018-09-05 22:09 ` Re: User to get locked after three wrong login attempts. Tim Cross <theophilusx@gmail.com>
2018-09-05 22:14 ` Re: User to get locked after three wrong login attempts. Craig James <cjames@emolecules.com>
2018-09-05 22:28 ` Re: User to get locked after three wrong login attempts. Tim Cross <theophilusx@gmail.com>
@ 2018-09-05 22:40 ` Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Ron @ 2018-09-05 22:40 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 09/05/2018 05:28 PM, Tim Cross wrote:
[snip]
> Unfortunately, that is a reflection of the poor standard of most
> auditors. They are rarely technical people and just follow a rule
> book. Most of their rules are outdated and many are wrong. For example,
> many still require 'complex' passwords consisting of mixed case,
> punctuation/special characters etc. This is despite the fact the person
> who originally proposed such a scheme has actually come out and
> apologised and said he had it wrong (plus this 'standard' was removed
> from NIST standards over 2 years ago) and ignores the changes in
> technologies which has changed the threat (i.e. rainbow tables etc now
> mean length is far more important than complexity).
>
> The 'trick' with auditors is to only answer what they ask and answer in
> such a way that what you say is true, but perhaps open to favourable
> interpretation. e.g.
>
> Auditor: do your accounts get locked after X failed login attempts
> Answer: We use Active directory for our Windows domain, which has the
> failed login policy enabled.
> Auditor: Ah yes, I know about that - good, I will mark you as
> compliant.
>
> rather than
>
> Answer: Well sort of. We have AD for our windows accounts which has the
> failed login policy enabled, but some of our systems, like Postgres,
> don't use that service.
> Auditor: So do you get locked if you try to login to postgres and fail X
> times
> Answer: No
> Auditor: Oh dear, I will have to mark you as non-compliant.
Sadly, our auditors are a bit cleverer. "Send us a screenshot showing that
Server X gets locked out after three failed tries." Naturally, Server X runs
Postgres.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: User to get locked after three wrong login attempts.
2018-09-04 07:48 User to get locked after three wrong login attempts. Praneel Devisetty <devisettypraneel@gmail.com>
2018-09-04 14:06 ` Re: User to get locked after three wrong login attempts. Tom Lane <tgl@sss.pgh.pa.us>
2018-09-05 17:48 ` Re: User to get locked after three wrong login attempts. Stephen Frost <sfrost@snowman.net>
2018-09-05 22:09 ` Re: User to get locked after three wrong login attempts. Tim Cross <theophilusx@gmail.com>
2018-09-05 22:14 ` Re: User to get locked after three wrong login attempts. Craig James <cjames@emolecules.com>
2018-09-05 22:28 ` Re: User to get locked after three wrong login attempts. Tim Cross <theophilusx@gmail.com>
@ 2018-09-06 06:42 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 0 replies; 9+ messages in thread
From: Achilleas Mantzios @ 2018-09-06 06:42 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 06/09/2018 01:28, Tim Cross wrote:
> Craig James <cjames@emolecules.com> writes:
>
>> On Wed, Sep 5, 2018 at 3:09 PM, Tim Cross <theophilusx@gmail.com> wrote:
>>
>>> Stephen Frost <sfrost@snowman.net> writes:
>>>
>>>> Greetings,
>>>>
>>>> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>>>>> Praneel Devisetty <devisettypraneel@gmail.com> writes:
>>>>>> We have a requirement , where we require a user to get locked after
>>> three
>>>>>> wrong login attempts.
>>>>> The usual recommendation is to configure Postgres to use PAM
>>>>> authentication; then you can set up any weird requirements like
>>>>> this one in the PAM configuration.
>>>> Unfortunately, it's a pain to set up PAM and there's a lot of things in
>>>> the PAM stack which can't be used because PostgreSQL doesn't run as
>>>> root. We should really have a better solution to this pretty commonly
>>>> asked for capability; I'm hoping to find time soon to hack on that.
>>>>
>>>> Thanks!
>>>>
>>>> Stephen
>>> These days, I think the better solution is to have this functionality in
>>> a central system. Putting aside that it is an 'outdated' auditor
>>> requirement ...
>>
>> To elaborate, you should explain to the auditor that this introduces a huge
>> denial-of-service vulnerability into your system. Anyone can start
>> hammering on everyone else's accounts, and with a fairly trivial script,
>> lock the entire company out of all accounts. This is a terrible idea.
From what I have gathered they try to protect the system from the admins rather than the users.
> Unfortunately, that is a reflection of the poor standard of most
> auditors. They are rarely technical people and just follow a rule
> book. Most of their rules are outdated and many are wrong. For example,
> many still require 'complex' passwords consisting of mixed case,
> punctuation/special characters etc. This is despite the fact the person
> who originally proposed such a scheme has actually come out and
> apologised and said he had it wrong (plus this 'standard' was removed
> from NIST standards over 2 years ago) and ignores the changes in
> technologies which has changed the threat (i.e. rainbow tables etc now
> mean length is far more important than complexity).
>
> The 'trick' with auditors is to only answer what they ask and answer in
> such a way that what you say is true, but perhaps open to favourable
> interpretation. e.g.
>
> Auditor: do your accounts get locked after X failed login attempts
> Answer: We use Active directory for our Windows domain, which has the
> failed login policy enabled.
> Auditor: Ah yes, I know about that - good, I will mark you as
> compliant.
>
> rather than
>
> Answer: Well sort of. We have AD for our windows accounts which has the
> failed login policy enabled, but some of our systems, like Postgres,
> don't use that service.
> Auditor: So do you get locked if you try to login to postgres and fail X
> times
> Answer: No
> Auditor: Oh dear, I will have to mark you as non-compliant.
^^^ exactly.
We have freeipa in place (via ldap in pg_hba.conf) and it does what they (auditors) ask + more.
Things get even more complicated when requirements come from different authorities e.g.
SOX + GDPR, the first asks for logs to be kept at least X years, the second gives the option for a person to delete his records or the records that contain info about him/her older than Y years. As a
result, the company spends considerable resources going around in circles.
>
>
>
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2018-09-06 06:42 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-09-04 07:48 User to get locked after three wrong login attempts. Praneel Devisetty <devisettypraneel@gmail.com>
2018-09-04 14:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-09-05 17:48 ` Stephen Frost <sfrost@snowman.net>
2018-09-05 22:09 ` Tim Cross <theophilusx@gmail.com>
2018-09-05 22:14 ` Craig James <cjames@emolecules.com>
2018-09-05 22:18 ` Ron <ronljohnsonjr@gmail.com>
2018-09-05 22:28 ` Tim Cross <theophilusx@gmail.com>
2018-09-05 22:40 ` Ron <ronljohnsonjr@gmail.com>
2018-09-06 06:42 ` Achilleas Mantzios <achill@matrix.gatewaynet.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