Re: Event Triggers unable to capture the DDL script executed
Hi Laurenz, Actually, current_query() may not help us in our case, as we won't be able to capture the ddl statement completely in case if it's in multiple lines. Can you please help me with the event trigger in C? & also how can we integrate it with our current postgresql DB? Thanks & Regards, Neethu From: n.kobza...@aeronavigator.ru Sent: Wednesday, February 22, 2023 3:33 PM To: 'Laurenz Albe' ; 'Neethu P' ; 'pgsql-general' Subject: RE: Event Triggers unable to capture the DDL script executed >>-Исходное сообщение- >>От: Laurenz Albe >>Отправлено: 22 февраля 2023 г. 12:52 >>Кому: Neethu P ; pgsql-general >> >>Тема: Re: Event Triggers unable to capture the DDL script executed >>On Wed, 2023-02-22 at 07:57 +, Neethu P wrote: >>> We are using event triggers to capture the DDL changes in a postgres >>> database. >>> However, we are unable to get the column information & the actual DDL >>> script executed, while a table is altered. >>> Also, in the postgres documentation for pg_event_trigger_ddl_commands ()- >>> it is mentioned as below. >>> pg_ddl_command A complete representation of the command, in internal >>> format. Thiscannot be output directly, but it can be passed to other >>> functions to obtain different pieces of information about the command. >>> >>> Is it possible to access pg_ddl_command in postgresql? Or is there any >>> scripts which can help to get theactual Alter DDL statement that was >>> executed by the user? >>That is simple if you write the event trigger in C. I would say that that is >>the only way to get at the actual statement. >>Yours, >>Laurenz Albe In MSSQL there is a brilliant possibility to have a server wide trigger to monitor commands. We are using It to have a history for all DDL operations. Please try this (on new empty database) and give a feedback. CREATE OR REPLACE FUNCTION public.notice_ddl() RETURNS event_trigger LANGUAGE 'plpgsql' COST 100 VOLATILE NOT LEAKPROOF AS $BODY$ DECLARE r RECORD; begin raise info '%', session_user || ' ran '||tg_tag||' '||current_query(); FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP RAISE NOTICE 'we got a % event for object " %"', r.command_tag, r.object_identity; END LOOP; end; $BODY$; CREATE OR REPLACE FUNCTION public.notice_ddl_drop() RETURNS event_trigger LANGUAGE 'plpgsql' COST 100 VOLATILE NOT LEAKPROOF AS $BODY$ DECLARE r RECORD; begin raise info '%', session_user || ' ran '||tg_tag||' '||current_query(); FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP RAISE NOTICE 'dropped: type "%" identity %', r.object_type, r.object_identity; END LOOP; end; $BODY$; CREATE EVENT TRIGGER etg ON DDL_COMMAND_END EXECUTE PROCEDURE public.notice_ddl(); CREATE EVENT TRIGGER etg_drop ON SQL_DROP EXECUTE PROCEDURE public.notice_ddl_drop();
Re: Event Triggers unable to capture the DDL script executed
On Thu, 2023-02-23 at 04:10 +, Neethu P wrote: > Actually, current_query() may not help us in our case, as we won't be able > to capture the ddl statement completely in case if it's in multiple lines. Multiple lines should not be a problem. The problems I see are - you won't catch DDL statements issued in a function with that, since you only see the top-level statement - if you have the DDL statement as string, you need to parse it, which is non-trivial > Can you please help me with the event trigger in C? & also how can we > integrate it with our current postgresql DB? In an e-mail, I cannot do much beyond pointing you to the documentation: https://www.postgresql.org/docs/current/event-trigger-interface.html There is also a simple example: https://www.postgresql.org/docs/current/event-trigger-example.html Getting used to reading and writing PostgreSQL server code takes a while. Yours, Laurenz Albe
Re: Event Triggers unable to capture the DDL script executed
Thanks Laurenz. Is there any documentaion example to use the pg_ddl_command to capture the actual ddl statement (for event triggers in C)? Regards, Neethu From: Laurenz Albe Sent: Thursday, February 23, 2023 4:20 PM To: Neethu P ; n.kobza...@aeronavigator.ru ; 'pgsql-general' Subject: Re: Event Triggers unable to capture the DDL script executed On Thu, 2023-02-23 at 04:10 +, Neethu P wrote: > Actually, current_query() may not help us in our case, as we won't be able > to capture the ddl statement completely in case if it's in multiple lines. Multiple lines should not be a problem. The problems I see are - you won't catch DDL statements issued in a function with that, since you only see the top-level statement - if you have the DDL statement as string, you need to parse it, which is non-trivial > Can you please help me with the event trigger in C? & also how can we > integrate it with our current postgresql DB? In an e-mail, I cannot do much beyond pointing you to the documentation: https://www.postgresql.org/docs/current/event-trigger-interface.html There is also a simple example: https://www.postgresql.org/docs/current/event-trigger-example.html Getting used to reading and writing PostgreSQL server code takes a while. Yours, Laurenz Albe
Re: RLS without leakproof restrictions?
Greetings,
* Tom Dunstan (pg...@tomd.cc) wrote:
> I'm currently researching different strategies for retrofitting some
> multi-tenant functionality into our existing Postgres-backed application.
> One of the options is using RLS policies to do row filtering. This is quite
> attractive as I dread the maintenance and auditing burden of adding
> filtering clauses to the majority of our queries. I'm somewhat concerned
> though about getting unexpected query plans based on the planner avoiding
> non-leakproof functions until row filtering has occurred - warning about
> this seems common in articles on RLS.
This is certainly something to be aware of as it helps in debugging
cases where RLS impacts performance but that doesn't make it necessarily
likely that there'll be an issue.
> Our application is the only "user" of the database, and we do not pass
> database errors through to the user interface, so for our case leakproof
> plans are overkill - we'd just like the implicit filtering clauses added
> based on some session GUCs that we set.
>
> Is there any way to get what we're looking for here? I don't see anything
> documented on CREATE POLICY, ALTER TABLE or any GUCs.
There isn't today. It's possible that this feature could be added in
the future, perhaps.
> Alternatively, are the concerns about changed plans unfounded? For example
> we don't use many expression indexes or exotic types, it's mostly btrees on
> text and ints. We do use tsearch a certain amount, but constructing
> tsvectors and tsqueries manually rather than through stemmers etc.
If you know the operators that are being used and the data types you're
using with them, then it's not too hard to check the leakproof status of
them-
select
oprname,
l.typname as left,
r.typname as right
from
pg_operator
join pg_proc on oprcode = pg_proc.oid
join pg_type l on oprleft = l.oid
join pg_type r on oprright = r.oid
where
proleakproof
and oprname = '='
and l.typname in ('text','int4','int8')
and r.typname in ('text','int4','int8');
oprname | left | right
-+--+---
= | int8 | int8
= | int4 | int8
= | int8 | int4
= | int4 | int4
= | text | text
(5 rows)
For the complete list:
select oprname,l.typname as left,r.typname as right from pg_operator
join pg_proc on oprcode = pg_proc.oid join pg_type l on oprleft = l.oid
join pg_type r on oprright = r.oid where proleakproof;
Thanks,
Stephen
signature.asc
Description: PGP signature
