Re: [GENERAL] 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;
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@joeconway.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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
В списке pgsql-general по дате отправления: