Thread: Trigger when user logs in

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.

Ron <ronljohnsonjr@gmail.com> 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

On 4/11/19 9:12 PM, Tom Lane wrote:
> Ron <ronljohnsonjr@gmail.com> 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.

Ron <ronljohnsonjr@gmail.com> 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

On 4/11/19 9:52 PM, Tom Lane wrote:
> Ron <ronljohnsonjr@gmail.com> 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.

> On Apr 11, 2019, at 19:52, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> Ron <ronljohnsonjr@gmail.com> 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
usernameor source IP) and enable sql logging or debugging for only certain cases (not always doing the same thing for a
particularuser). Another common use case is to do something like running plpgsql or manipulating data in db tables -
butagain looking at some combination of things at a database level to make a decision about what to do; for example the
applicationitself 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

> On Apr 13, 2019, at 17:28, Schneider, Jeremy <schnjere@amazon.com> wrote:
> 
>> On Apr 11, 2019, at 19:52, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 
>> Ron <ronljohnsonjr@gmail.com> 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
(likeusername or source IP) and enable sql logging or debugging for only certain cases (not always doing the same thing
fora 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
examplethe 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
thepast where it wasn’t feasible or even possible to get the sorts of changes I’d need into applications using the
databasesthat 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?
 

> On Apr 13, 2019, at 17:28, Schneider, Jeremy <schnjere@amazon.com> wrote:

> 

>> On Apr 11, 2019, at 19:52, Tom Lane <tgl@sss.pgh.pa.us> wrote:

>> 

>> Ron <ronljohnsonjr@gmail.com> 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?

On 4/13/19 7:28 PM, Schneider, Jeremy wrote:
>> On Apr 11, 2019, at 19:52, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>> Ron <ronljohnsonjr@gmail.com> 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
(likeusername 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
plpgsqlor manipulating data in db tables - but again looking at some combination of things at a database level to make
adecision about what to do; for example the application itself might enable or disable certain behaviors by setting
valuesin 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.

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
| |   | hjp@hjp.at         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

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.