Re: Audit based on role

*You can tweak the following query to help you determine if your user is a
member of role/group 'module_dml'.*

*Then you can use it in a trigger function that does the logging.SELECT
g.rolname as group, u.rolname as user, r.admin_option as
admin, g.rolsuper as g_super, u.rolsuper as u_super FROM
pg_auth_members r JOIN pg_authid g ON (r.roleid = g.oid) JOIN pg_authid
u ON (r.member = u.oid) WHERE u.rolname = '{your_user}' AND g.rolname =
'module_dm;' ORDER BY 1, 2;*

On Mon, Aug 7, 2017 at 8:05 PM, Joe Conway <mail(at)joeconway(dot)com> wrote:

> On 08/07/2017 04:47 PM, anand086 wrote:
> > Only Insert/Update/Delete sqls are to be audited.
>
> You could get close to what you want, I think, by setting log_statement
> = mod for the users of interest, e.g. by doing:
>
> ALTER USER whomever SET log_statement = mod;
>
> See:
>
> https://www.postgresql.org/docs/9.6/static/runtime-
> config-logging.html#GUC-LOG-STATEMENT
>
> Note: "mod logs all ddl statements, plus data-modifying statements such
> as INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM."
>
> Caveat: You would have to do this per user in that group. However you
> could write a query against the system catalogs though to loop through
> the members of the group and execute this statement against each one.
> Maybe rerun it periodically.
>
> HTH,
>
> Joe
>
> --
> Crunchy Data - http://crunchydata.com
> PostgreSQL Support for Secure Enterprises
> Consulting, Training, & Open Source Development
>
>

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.