Re: New feature proposal (trigger)

pá 24. 1. 2020 v 10:08 odesílatel Sergiu Velescu <Sergiu(dot)Velescu(at)endava(dot)com>
napsal:

> 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!
>

lot of this does pg_audit https://www.pgaudit.org/

these is a possibility to log - loging/logout, using DDL. - you can process
postgresql log.

regards

Pavel

>
> 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>
> 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>
> *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> 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.
>
>