Thread: Event Triggers and GRANT/REVOKE
GRANT and REVOKE trigger on a ddl_command_end event trigger but don't provide any information beyond whether it was a table, schema, function, etc. that was affected. No object IDs or the like are included. How would you find out which table had its ACLs modified?
Also, why do grants and revokes have an object_type of 'TABLE' instead of lower case names like 'table' for all other event types?
Thanks,
Miles Elam
On 10/9/19 1:56 PM, Miles Elam wrote: > GRANT and REVOKE trigger on a ddl_command_end event trigger but don't > provide any information beyond whether it was a table, schema, function, > etc. that was affected. No object IDs or the like are included. How > would you find out which table had its ACLs modified? What is the code for trigger and function? > > Also, why do grants and revokes have an object_type of 'TABLE' instead > of lower case names like 'table' for all other event types? > > > Thanks, > > Miles Elam > -- Adrian Klaver adrian.klaver@aklaver.com
Using my example below from another thread, GRANTs and REVOKEs leave all fields NULL except for command_tag (set to 'GRANT' or 'REVOKE'), object_type (set to upper case target like 'TABLE'), and in_extension (set to whatever is appropriate, but typically false).
-----
CREATE TABLE IF NOT EXISTS ddl_info (
classid oid,
objid oid,
objsubid integer,
command_tag text,
object_type text,
schema_name text,
object_identity text,
in_extension bool,
transaction_id bigint NOT NULL DEFAULT txid_current(),
inserted timestamptz NOT NULL DEFAULT clock_timestamp()
);
CREATE OR REPLACE FUNCTION ddl_log()
RETURNS EVENT_TRIGGER
LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO ddl_info (
classid, objid, objsubid, command_tag, object_type,
schema_name, object_identity, in_extension
)
SELECT
classid, objid, objsubid, command_tag, object_type,
schema_name, object_identity, in_extension
FROM pg_event_trigger_ddl_commands();
END;
$$;
CREATE EVENT TRIGGER aa_ddl_info ON ddl_command_end
EXECUTE PROCEDURE ddl_log();
classid oid,
objid oid,
objsubid integer,
command_tag text,
object_type text,
schema_name text,
object_identity text,
in_extension bool,
transaction_id bigint NOT NULL DEFAULT txid_current(),
inserted timestamptz NOT NULL DEFAULT clock_timestamp()
);
CREATE OR REPLACE FUNCTION ddl_log()
RETURNS EVENT_TRIGGER
LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO ddl_info (
classid, objid, objsubid, command_tag, object_type,
schema_name, object_identity, in_extension
)
SELECT
classid, objid, objsubid, command_tag, object_type,
schema_name, object_identity, in_extension
FROM pg_event_trigger_ddl_commands();
END;
$$;
CREATE EVENT TRIGGER aa_ddl_info ON ddl_command_end
EXECUTE PROCEDURE ddl_log();
On Wed, Oct 9, 2019 at 2:27 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/9/19 1:56 PM, Miles Elam wrote:
> GRANT and REVOKE trigger on a ddl_command_end event trigger but don't
> provide any information beyond whether it was a table, schema, function,
> etc. that was affected. No object IDs or the like are included. How
> would you find out which table had its ACLs modified?
What is the code for trigger and function?
>
> Also, why do grants and revokes have an object_type of 'TABLE' instead
> of lower case names like 'table' for all other event types?
>
>
> Thanks,
>
> Miles Elam
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 10/9/19 3:20 PM, Miles Elam wrote: > Using my example below from another thread, GRANTs and REVOKEs leave all > fields NULL except for command_tag (set to 'GRANT' or 'REVOKE'), > object_type (set to upper case target like 'TABLE'), and in_extension > (set to whatever is appropriate, but typically false). From the source: https://doxygen.postgresql.org/event__trigger_8c.html#a4fc09f5b8231780b7f1abdd2a72f95a7 https://doxygen.postgresql.org/event__trigger_8c.html#a2b4e240a2c0aa9cfcc61f906af134913 Line 2185 case SCT_Grant Looks like the information is never collected. The why is going to need an answer from a developer. As to case it looks like GRANT/REVOKE use a different method of deriving the string then other commands, namely using stringify_adefprivs_objtype() > > ----- > > CREATE TABLE IF NOT EXISTS ddl_info ( > classid oid, > objid oid, > objsubid integer, > command_tag text, > object_type text, > schema_name text, > object_identity text, > in_extension bool, > transaction_id bigint NOT NULL DEFAULT txid_current(), > inserted timestamptz NOT NULL DEFAULT clock_timestamp() > ); > > CREATE OR REPLACE FUNCTION ddl_log() > RETURNS EVENT_TRIGGER > LANGUAGE plpgsql AS $$ > BEGIN > INSERT INTO ddl_info ( > classid, objid, objsubid, command_tag, object_type, > schema_name, object_identity, in_extension > ) > SELECT > classid, objid, objsubid, command_tag, object_type, > schema_name, object_identity, in_extension > FROM pg_event_trigger_ddl_commands(); > END; > $$; > > CREATE EVENT TRIGGER aa_ddl_info ON ddl_command_end > EXECUTE PROCEDURE ddl_log(); > > On Wed, Oct 9, 2019 at 2:27 PM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 10/9/19 1:56 PM, Miles Elam wrote: > > GRANT and REVOKE trigger on a ddl_command_end event trigger but > don't > > provide any information beyond whether it was a table, schema, > function, > > etc. that was affected. No object IDs or the like are included. How > > would you find out which table had its ACLs modified? > > What is the code for trigger and function? > > > > > Also, why do grants and revokes have an object_type of 'TABLE' > instead > > of lower case names like 'table' for all other event types? > > > > > > Thanks, > > > > Miles Elam > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com