Thread: New feature proposal (trigger)
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.
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.
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.
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.
Pavel Stehule <pavel.stehule@gmail.com> writes: > čt 23. 1. 2020 v 17:26 odesílatel Sergiu Velescu <Sergiu.Velescu@endava.com> > napsal: >> 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. > Do you have some examples of these useful triggers? > I don't know any one. See also the fate of commit e788bd924, which proposed to add on-session-start and on-session-end hooks. Getting that sort of thing to work safely is a LOT harder than it sounds. There are all sorts of definitional and implementation problems, at least if you'd like the hook or trigger to do anything interesting (like run a transaction). I rather suspect that exposing such a thing at SQL level would also add a pile of security considerations (i.e. who's allowed to do what to whom). The hook proposal didn't have to address that, but a trigger feature certainly would. Maybe it's all do-able, but the work to benefit ratio doesn't look very appetizing to me. regards, tom lane
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.stehule@gmail.com>
Sent: Thursday, January 23, 2020 18:39
To: Sergiu Velescu <Sergiu.Velescu@endava.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: New feature proposal (trigger)
čt 23. 1. 2020 v 17:26 odesílatel Sergiu Velescu <Sergiu.Velescu@endava.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.
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.stehule@gmail.com>
Sent: Thursday, January 23, 2020 18:39
To: Sergiu Velescu <Sergiu.Velescu@endava.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: New feature proposal (trigger)
čt 23. 1. 2020 v 17:26 odesílatel Sergiu Velescu <Sergiu.Velescu@endava.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.
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.stehule@gmail.com>
Sent: Friday, January 24, 2020 10:03
To: Sergiu Velescu <Sergiu.Velescu@endava.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: New feature proposal (trigger)
pá 24. 1. 2020 v 8:55 odesílatel Sergiu Velescu <Sergiu.Velescu@endava.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.stehule@gmail.com>
Sent: Thursday, January 23, 2020 18:39
To: Sergiu Velescu <Sergiu.Velescu@endava.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: New feature proposal (trigger)
čt 23. 1. 2020 v 17:26 odesílatel Sergiu Velescu <Sergiu.Velescu@endava.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.
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.stehule@gmail.com>
Sent: Friday, January 24, 2020 10:03
To: Sergiu Velescu <Sergiu.Velescu@endava.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: New feature proposal (trigger)
pá 24. 1. 2020 v 8:55 odesílatel Sergiu Velescu <Sergiu.Velescu@endava.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.stehule@gmail.com>
Sent: Thursday, January 23, 2020 18:39
To: Sergiu Velescu <Sergiu.Velescu@endava.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: New feature proposal (trigger)
čt 23. 1. 2020 v 17:26 odesílatel Sergiu Velescu <Sergiu.Velescu@endava.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.
## Sergiu Velescu (Sergiu.Velescu@endava.com): > OnLogin/Logout. > I want to log/audit each attempt to login (successful and/or not). log_connections/log_disconnections > 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. > OnDDL > Log every DDL in a DB log table (who/when altered/created/dropped/ > truncated a specific object) and send an email. Event Triggers https://www.postgresql.org/docs/current/event-triggers.html > 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, ... Regards, Christoph -- Spare Space