RE: New feature proposal (trigger)

Hi,

Could you please elaborate – what do you mean by “…you can do almost all things today by C extensions…” – does these extensions already exists or I have to develop it?
If these extensions exists and developed by somebody else (not in PG core) then nobody will install it where sensitive information exists (at least you will not be able to pass the PCI-DSS audit).
If I have to develop it – then I have 2 option 1) to develop it or 2) to use other RDBMS which already have this implemented.

For enterprise class solutions it is vital to have the possibility to keep track of actions in DB (who/when logged-in/out, which statement run and so on), this is even more important than performance because if I need more performance I probably could increase the hardware procession power (CPU/RAM/IOPS) but if I have no audit I have no choice…

I know PostgreSQL is free solution and I can’t expect it to have everything a commercial RDBMS have but at least we should start to think to implement this!

Have a nice day!

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

pá 24. 1. 2020 v 8:55 odesílatel Sergiu Velescu <Sergiu(dot)Velescu(at)endava(dot)com<mailto:Sergiu(dot)Velescu(at)endava(dot)com>> napsal:
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.

you can do almost all things today by C extensions or just with Postgres log

Personally I don't thing so doing these things just from Postgres, PL procedures is good thing

Pavel

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<mailto:pavel(dot)stehule(at)gmail(dot)com>>
Sent: Thursday, January 23, 2020 18:39
To: Sergiu Velescu <Sergiu(dot)Velescu(at)endava(dot)com<mailto:Sergiu(dot)Velescu(at)endava(dot)com>>
Cc: pgsql-hackers(at)postgresql(dot)org<mailto: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.