Re: New feature proposal (trigger)

From: Christoph Moench-Tegeder <cmt(at)burggraben(dot)net>
To: Sergiu Velescu <Sergiu(dot)Velescu(at)endava(dot)com>
Cc: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: New feature proposal (trigger)
Date: 2020-01-24 09:29:37
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

## Sergiu Velescu (Sergiu(dot)Velescu(at)endava(dot)com):

> OnLogin/Logout.
> I want to log/audit each attempt to login (successful and/or not).


> Who/how long was logged in DB (who logged in out of business hours
> (maybe deny access)).

Use PAM authentication.

> Set session variable based on username (or maybe IP address) -
> for example DATE format.

"Based on user name": ALTER ROLE

> OnStartup (or AfterStarted)
> I want to start a procedure which check for a specific event in a loop
> and send an email.

That sounds like "problematic architecture" right from the start:
- sending emails in a database transaction is not a good idea
- active-waiting for events ("in a loop") is inefficient, try writing
to a queue table and have a daemon read from that.

> Log every DDL in a DB log table (who/when altered/created/dropped/
> truncated a specific object) and send an email.

Event Triggers

> Duplicate WAL (to have WAL in 2 different places – for example I take
> backup on separate disk and I want to have a copy of WAL on that disk)

We have streaming replication/pg_receivewal or file based archiving,
both also wrapped in practical products like barman, pgbackrest, ...


Spare Space

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Konstantin Knizhnik 2020-01-24 09:43:10 Re: [Proposal] Global temporary tables
Previous Message Hamid Akhtar 2020-01-24 09:28:50 Re: BUG #16171: Potential malformed JSON in explain output