Event Triggers and GRANT/REVOKE

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Event Triggers and GRANT/REVOKE

Miles Elam-4
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

Reply | Threaded
Open this post in threaded view
|

Re: Event Triggers and GRANT/REVOKE

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Event Triggers and GRANT/REVOKE

Miles Elam-4
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();

On Wed, Oct 9, 2019 at 2:27 PM Adrian Klaver <[hidden email]> 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Event Triggers and GRANT/REVOKE

Adrian Klaver-4
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 <[hidden email]
> <mailto:[hidden email]>> 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
>     [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]