RE: New feature proposal (trigger)

Hi,

Yes, please find below few examples.

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)).
Set session variable based on username (or maybe IP address) - for example DATE format.

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

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

Out of this topic nice to have (I could elaborate any of below topic if you are interested in):
Storage quota per user (or schema).
Audit – I know about existence of pgaudit extension but it is far from ideal (I compare to Oracle Fine Grained Audit).
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)
To have something like Oracle SQL Tuning Advisor (for example I have a “big” SQL which take longer than it should (probably the optimizer didn’t find the pest execution plan in the tame allocated to this) – this tool provide the possibility to analyze comprehensive the SQL and offer solutions (maybe different execution plan, maybe offer suggestion to create a specific index…)).
Best regards.

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Sent: Thursday, January 23, 2020 18:39
To: Sergiu Velescu <Sergiu(dot)Velescu(at)endava(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: New feature proposal (trigger)

čt 23. 1. 2020 v 17:26 odesílatel Sergiu Velescu <Sergiu(dot)Velescu(at)endava(dot)com<mailto:Sergiu(dot)Velescu(at)endava(dot)com>> napsal:
Dear PgSQL-Hackers,

I would like to propose a new feature which is missing in PgSQL but quite useful and nice to have (and exists in Oracle and probably in some other RDBMS), I speak about “Database Level” triggers: BeforePgStart, AfterPgStarted, OnLogin, OnSuccessfulLogin, BeforePGshutdown, OnLogOut – I just mentioned some of it but the final events could be different.

These DB Level triggers are quite useful for example if somebogy want to set some PG env. variables depends on user belonging to one or another role or want to track who/wen logged in/out, start a stored procedure AfterPgStarted and so on.

Do you have some examples of these useful triggers?

I don't know any one.

Regards

Pavel

Thanks!

The information in this email is confidential and may be legally privileged. It is intended solely for the addressee. Any opinions expressed are mine and do not necessarily represent the opinions of the Company. Emails are susceptible to interference. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is strictly prohibited and may be unlawful. If you have received this message in error, do not open any attachments but please notify the Endava Service Desk on (+44 (0)870 423 0187), and delete this message from your system. The sender accepts no responsibility for information, errors or omissions in this email, or for its use or misuse, or for any act committed or omitted in connection with this communication. If in doubt, please verify the authenticity of the contents with the sender. Please rely on your own virus checkers as no responsibility is taken by the sender for any damage rising out of any bug or virus infection.

Endava plc is a company registered in England under company number 5722669 whose registered office is at 125 Old Broad Street, London, EC2N 1AR, United Kingdom. Endava plc is the Endava group holding company and does not provide any services to clients. Each of Endava plc and its subsidiaries is a separate legal entity and has no liability for another such entity's acts or omissions.