Re: Create trigger on after logon on schema

On Wed, Oct 6, 2021 at 9:10 AM aditya desai <admad123(at)gmail(dot)com> wrote:

> Hi Depesz,
> Sorry , I didn't mention it clearly. We are using these triggers for audit
> purposes.
>
> Triggers calls a procedure to insert username,machine.IPAddress into
> LogonTime in an audit table.
> We also have a trigger that gets fired BEFORE ALTER OR CREATE OR DROP on
> schema that will audit the user details in the audit table.
>
> Any alternative solution in Postgres is welcome.
>
>
>

In my experience, this is solved in a middle tier that brokers access to
Postgres. So the middle tier would handle login (even if brokering the
credentials back to Pg to achieve the login). And then the middle tier can
execute the logic to do login auditing along the lines you want.

That said, I could imagine logging the kind of information you describe not
on login, but on certain sql executions (DDL). I think you could set a
trigger to record IP and other system stats related to each of those
actions (this isn't possible to my knowledge for select statements). This
would be finer grained than logging login data, but would maybe accomplish
the same thing? Here's the kind of system info you can collect and log:
https://www.postgresql.org/docs/current/functions-info.html

I think it's probably more realistic to use the former method: log/audit
everything in a middle tier that brokers access to Postgres.

Steve