0

I'm implementing a database with a few triggers that registers changes to specific tables into a couterpart tables in a named "audit" schema.

This is, as it sounds, to be able to audit changes made to the data.

It works pretty well. But, of course, I need to grant insert privileges to the user used to connect to the database from the application because, otherwise, that triggers will fail on inserting rows in the audit schema.

I think the answer is "no", but anyway I would ask for if I'm wrong: Is there any way to permit a PostgreSQL trigger function to be executed as distinct user (for example function's owner) making it able to perform operations (such as that inserts) that are not permitted to users who performed the operation that triggered them?

Thanks.

bitifet
  • 3,514
  • 15
  • 37
  • 4
    Triggers can only call functions right now in PostgreSQL (and I don't think this "limitation" will be loosened in the near future) -- And trigger functions (like any other functions) [can be marked](https://www.postgresql.org/docs/current/static/sql-createfunction.html) with `SECURITY DEFINER` -- *`SECURITY INVOKER` indicates that the function is to be executed with the privileges of the user that calls it. That is the default. `SECURITY DEFINER` specifies that the function is to be executed with the privileges of the user that created it.* – pozs Jan 25 '17 at 09:32
  • Thanks!!. I didn't know about 'SECURITY' keyword (even it is well explained in 'create function' documentation). I checked 'grant' documentation and googled about it but, stupid of me, I didn't realized that it could be simply a function property. – bitifet Jan 25 '17 at 10:55

0 Answers0