Trigger when user logs in

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

Trigger when user logs in

Ron-2
Hi,

PCI auditors have mandated that our databases (all running v9.6) send an
email when certain users log in.  Thus, I've been searching for how to do
this, but without much luck.

https://www.postgresql.org/message-id/flat/20170720204733.40f2b7eb.nagata%40sraoss.co.jp

This long thread from almost two years ago seems to point in the right
direction, but then died with "Okay, this has proved to require broader
changes than thought first. I am marking the patch as returned with feedback."

So, is there something that I've missed which lets me write an "on login"
trigger, or do I have to install pgaudit and tail_n_mail?

Thanks

--
Angular momentum makes the world go 'round.


Reply | Threaded
Open this post in threaded view
|

Re: Trigger when user logs in

Tom Lane-2
Ron <[hidden email]> writes:
> PCI auditors have mandated that our databases (all running v9.6) send an
> email when certain users log in.  Thus, I've been searching for how to do
> this, but without much luck.

PAM is the usual suggestion when you need off-the-beaten-path login
behavior.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Trigger when user logs in

Ron-2
On 4/11/19 9:12 PM, Tom Lane wrote:
> Ron <[hidden email]> writes:
>> PCI auditors have mandated that our databases (all running v9.6) send an
>> email when certain users log in.  Thus, I've been searching for how to do
>> this, but without much luck.
> PAM is the usual suggestion

Can you be more specific?  (All users except postgres log in directly to the
database from remote systems.)

>   when you need off-the-beaten-path login behavior.

I bet requests like this will start to make it onto the beaten path.

--
Angular momentum makes the world go 'round.


Reply | Threaded
Open this post in threaded view
|

Re: Trigger when user logs in

Tom Lane-2
Ron <[hidden email]> writes:
> On 4/11/19 9:12 PM, Tom Lane wrote:
>> PAM is the usual suggestion

> Can you be more specific?

I'm suggesting that you use PAM auth

https://www.postgresql.org/docs/current/auth-pam.html

and then configure the email behavior on the PAM side.

The PAM doc link we provided there leads to a page that looks kind of
moribund, but Mr. Google finds much more useful resources, eg

https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/6/html/managing_smart_cards/pluggable_authentication_modules

https://www.freebsd.org/doc/en/articles/pam/pam-freebsd-modules.html

https://web.archive.org/web/20180303034326/http://www.tuxradar.com/content/how-pam-works

I would have guessed that there was a PAM module that does what you
want out-of-the-box, but it looks like maybe you have to resort to
invoking an MUA via the generic pam_exec module, as in one of the
responses here:

https://askubuntu.com/questions/179889/how-do-i-set-up-an-email-alert-when-a-ssh-login-is-successful

>> when you need off-the-beaten-path login behavior.

> I bet requests like this will start to make it onto the beaten path.

Meh.  I'm not that excited about inventing our own versions of wheels
that already exist, especially when there's nothing very Postgres-specific
about the requirements.  Notice that the example I pointed you at is for
sshd not Postgres.  IMO the fact that you can use the same tool to solve
both cases is a good thing.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Trigger when user logs in

Ron-2
On 4/11/19 9:52 PM, Tom Lane wrote:
> Ron <[hidden email]> writes:
>> On 4/11/19 9:12 PM, Tom Lane wrote:
>>> PAM is the usual suggestion
>> Can you be more specific?
> I'm suggesting that you use PAM auth
>
> https://www.postgresql.org/docs/current/auth-pam.html
>
> and then configure the email behavior on the PAM side.

Understood.  Thanks.

--
Angular momentum makes the world go 'round.


Reply | Threaded
Open this post in threaded view
|

Re: Trigger when user logs in

Jeremy Schneider-2
In reply to this post by Tom Lane-2

> On Apr 11, 2019, at 19:52, Tom Lane <[hidden email]> wrote:
>
> Ron <[hidden email]> writes:
>> I bet requests like this will start to make it onto the beaten path.
>
> Meh.  I'm not that excited about inventing our own versions of wheels
> that already exist, especially when there's nothing very Postgres-specific
> about the requirements.  Notice that the example I pointed you at is for
> sshd not Postgres.  IMO the fact that you can use the same tool to solve
> both cases is a good thing.

This might work for sending an email, but not very useful if I want to do something in the database.

For example, one very common use of logon triggers in other databases is to look at various connection parameters (like username or source IP) and enable sql logging or debugging for only certain cases (not always doing the same thing for a particular user). Another common use case is to do something like running plpgsql or manipulating data in db tables - but again looking at some combination of things at a database level to make a decision about what to do; for example the application itself might enable or disable certain behaviors by setting values in a configuration table.

I’m still trying to work out the best approach for solving these sorts of use cases in current versions of PostgreSQL... I’m curious how others are solving this?

-Jeremy

Sent from my TI-83
Reply | Threaded
Open this post in threaded view
|

Re: Trigger when user logs in

Jeremy Schneider-2

> On Apr 13, 2019, at 17:28, Schneider, Jeremy <[hidden email]> wrote:
>
>> On Apr 11, 2019, at 19:52, Tom Lane <[hidden email]> wrote:
>>
>> Ron <[hidden email]> writes:
>>> I bet requests like this will start to make it onto the beaten path.
>>
>> Meh.  I'm not that excited about inventing our own versions of wheels
>> that already exist, especially when there's nothing very Postgres-specific
>> about the requirements.  Notice that the example I pointed you at is for
>> sshd not Postgres.  IMO the fact that you can use the same tool to solve
>> both cases is a good thing.
>
> This might work for sending an email, but not very useful if I want to do something in the database.
>
> For example, one very common use of logon triggers in other databases is to look at various connection parameters (like username or source IP) and enable sql logging or debugging for only certain cases (not always doing the same thing for a particular user). Another common use case is to do something like running plpgsql or manipulating data in db tables - but again looking at some combination of things at a database level to make a decision about what to do; for example the application itself might enable or disable certain behaviors by setting values in a configuration table.

Probably worth mentioning that I’m all for solving this in the application - just that I’ve experienced many cases in the past where it wasn’t feasible or even possible to get the sorts of changes I’d need into applications using the databases that I was responsible for.

> I’m still trying to work out the best approach for solving these sorts of use cases in current versions of PostgreSQL... I’m curious how others are solving this?
Reply | Threaded
Open this post in threaded view
|

Re: Trigger when user logs in

Dan Langille
Pushover.net comes to mind. 

The email sending should not delay login. 

If you want notifications,  look at mqtt. 


Lightweight, non-pausing solution. 

HTH

--
  Dan Langille



On Sat, Apr 13, 2019, at 9:16 PM, Schneider, Jeremy wrote:

> On Apr 13, 2019, at 17:28, Schneider, Jeremy <[hidden email]> wrote:

>> On Apr 11, 2019, at 19:52, Tom Lane <[hidden email]> wrote:
>> 
>> Ron <[hidden email]> writes:
>>> I bet requests like this will start to make it onto the beaten path.
>> 
>> Meh.  I'm not that excited about inventing our own versions of wheels
>> that already exist, especially when there's nothing very Postgres-specific
>> about the requirements.  Notice that the example I pointed you at is for
>> sshd not Postgres.  IMO the fact that you can use the same tool to solve
>> both cases is a good thing.

> This might work for sending an email, but not very useful if I want to do something in the database.

> For example, one very common use of logon triggers in other databases is to look at various connection parameters (like username or source IP) and enable sql logging or debugging for only certain cases (not always doing the same thing for a particular user). Another common use case is to do something like running plpgsql or manipulating data in db tables - but again looking at some combination of things at a database level to make a decision about what to do; for example the application itself might enable or disable certain behaviors by setting values in a configuration table.

Probably worth mentioning that I’m all for solving this in the application - just that I’ve experienced many cases in the past where it wasn’t feasible or even possible to get the sorts of changes I’d need into applications using the databases that I was responsible for.

> I’m still trying to work out the best approach for solving these sorts of use cases in current versions of PostgreSQL... I’m curious how others are solving this?


Reply | Threaded
Open this post in threaded view
|

Re: Trigger when user logs in

Ron-2
In reply to this post by Jeremy Schneider-2
On 4/13/19 7:28 PM, Schneider, Jeremy wrote:

>> On Apr 11, 2019, at 19:52, Tom Lane <[hidden email]> wrote:
>>
>> Ron <[hidden email]> writes:
>>> I bet requests like this will start to make it onto the beaten path.
>> Meh.  I'm not that excited about inventing our own versions of wheels
>> that already exist, especially when there's nothing very Postgres-specific
>> about the requirements.  Notice that the example I pointed you at is for
>> sshd not Postgres.  IMO the fact that you can use the same tool to solve
>> both cases is a good thing.
> This might work for sending an email, but not very useful if I want to do something in the database.
>
> For example, one very common use of logon triggers in other databases is to look at various connection parameters (like username or source IP) and enable sql logging or debugging for only certain cases

In our case, another looming Auditor requirement is to be able to instantly
kick off -- or at least send a warning email -- when certain roles log in
from unapproved IP addresses or programs.  For example, service accounts
should only be able to log in from IP addresses and certain applications. 
Humans logging in via service accounts using pgAdmin should, for example, be
instantly kicked off.

Oracle is able to do this, and the auditors expect other RDBMSs to do it to.

> (not always doing the same thing for a particular user). Another common use case is to do something like running plpgsql or manipulating data in db tables - but again looking at some combination of things at a database level to make a decision about what to do; for example the application itself might enable or disable certain behaviors by setting values in a configuration table.
>
> I’m still trying to work out the best approach for solving these sorts of use cases in current versions of PostgreSQL... I’m curious how others are solving this?
>
> -Jeremy
>
> Sent from my TI-83

--
Angular momentum makes the world go 'round.


Reply | Threaded
Open this post in threaded view
|

Re: Trigger when user logs in

Peter J. Holzer
On 2019-04-13 22:22:16 -0500, Ron wrote:
> In our case, another looming Auditor requirement is to be able to instantly
> kick off -- or at least send a warning email -- when certain roles log in
> from unapproved IP addresses or programs.  For example, service accounts
> should only be able to log in from IP addresses and certain applications. 
> Humans logging in via service accounts using pgAdmin should, for example, be
> instantly kicked off.

If you want to prevent a user from logging in (which is functionally
equivalent but a bit stronger than "instantly kick off"), then this is
definitely something that could and should be implemented via PAM (I'm
not sure what information is passed to PAM, so you might get the IP
address but not the application name (the latter can't be trusted
anyway), for example).

If you just want to send an email, I would just monitor the logs. Just
run a cronjob every minute that checks for suspicious activity. Or if
you collect your logfiles in something like ELKS (you probably want to
do that if you have auditing requirements, you can probably configure
that to send notifications.

If the "instantly kick off" doesn't have to be really instantly, you
could also monitor pg_stat_activity every second or so and terminate any
suspicious session. But note that this rather insecure: A session which
lasts for much less than a second has a good chance of flying under the
radar.

        hp

--
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | [hidden email]         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Trigger when user logs in

Ron-2
On 4/14/19 4:05 AM, Peter J. Holzer wrote:

> On 2019-04-13 22:22:16 -0500, Ron wrote:
>> In our case, another looming Auditor requirement is to be able to instantly
>> kick off -- or at least send a warning email -- when certain roles log in
>> from unapproved IP addresses or programs.  For example, service accounts
>> should only be able to log in from IP addresses and certain applications.
>> Humans logging in via service accounts using pgAdmin should, for example, be
>> instantly kicked off.
> If you want to prevent a user from logging in (which is functionally
> equivalent but a bit stronger than "instantly kick off"), then this is
> definitely something that could and should be implemented via PAM (I'm
> not sure what information is passed to PAM, so you might get the IP
> address

Doesn't this require all Postgres roles to also be OS users?

>   but not the application name (the latter can't be trusted
> anyway), for example).


--
Angular momentum makes the world go 'round.


Reply | Threaded
Open this post in threaded view
|

Re: Trigger when user logs in

Michael Nolan
In reply to this post by Peter J. Holzer



On Sun, Apr 14, 2019 at 4:06 AM Peter J. Holzer <[hidden email]> wrote:

If you want to prevent a user from logging in (which is functionally
equivalent but a bit stronger than "instantly kick off"), then this is
definitely something that could and should be implemented via PAM (I'm
not sure what information is passed to PAM, so you might get the IP
address but not the application name (the latter can't be trusted
anyway), for example).

I think the only information you can reliably count on is the user name.  IP addresses may not be the true IP address of the user if there's some kind of relay or cache in place. 

Having been through the PCI compliance wringer a few times, I wish the OP luck. 
--
Mike Nolan