Re: Trigger when user logs in

On 4/13/19 7:28 PM, Schneider, Jeremy wrote:
>> On Apr 11, 2019, at 19:52, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>> Ron <ronljohnsonjr(at)gmail(dot)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

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.