Search Postgresql Archives

Re: Event Triggers and GRANT/REVOKE

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 <adrian.klaver@xxxxxxxxxxx> 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@xxxxxxxxxxx

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux