Audit based on role

On 8/7/2017 4:33 PM, anand086 wrote:

We are running Postgres 9.6.3 version and have requirement, where we want to
audit any DML action performed by a user whose has module_dml role granted.

What would be the best way to do that? I was thinking to write something
likehttps://wiki.postgresql.org/wiki/Audit_trigger, but I am not sure on
how to integrate "user whose has module_dml role granted" into the function.

Instead of trigger is there any better way to achieve this?

DML as in select/insert/update/delete ? or did you mean DDL as in
CREATE/ALTER TABLE, etc ?

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Only Insert/Update/Delete sqls are to be audited.

--
View this message in context: http://www.postgresql-archive.org/Audit-based-on-role-tp5976507p5976509.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

*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@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.

Hi,
I personally don't like the solution from
https://wiki.postgresql.org/wiki/Audit_trigger and here are the reasons why:
* it produces a large table where all the changes, from all tables and
schemas go - audit.logged_actions. So when you would like to read it to
check anything it will be super slow
* On audit table are 3 indexes - which slow down the insert process
* All the data are kept in one column - so when comes to any analysis you
need custom functions to do it

Besides: Why there is fillfactor=100? That's the default value for table,
isn't it?

I use a bit different approach:
* every table has got separate table in a audit schema
* audited data are partinioned (usually per month)
* it's much easier to remove old data - just by dropping partition
* data has got exactly the same structure as in original schema

Every od those solution has got pros and cons but I prefer the second one a
lot more.
You can find one of it here: https://github.com/AwdotiaRomanowna/pgaudit

--
View this message in context: http://www.postgresql-archive.org/Audit-based-on-role-tp5976507p5976641.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello,

On Mon, Aug 07, 2017 at 04:33:21PM -0700, anand086 wrote:

Hi,

I am postgres newbie.

We are running Postgres 9.6.3 version and have requirement, where we want to
audit any DML action performed by a user whose has module_dml role granted.

What would be the best way to do that? I was thinking to write something
like https://wiki.postgresql.org/wiki/Audit_trigger, but I am not sure on
how to integrate "user whose has module_dml role granted" into the function.

Instead of trigger is there any better way to achieve this?

Did you try pgaudit extension?
https://github.com/pgaudit/pgaudit

--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 08/07/2017 06:59 PM, Melvin Davidson wrote:

*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;

The problem with that query is is you have more than one level of
nesting. E.g.:

Role name | Attributes | Member of
------------+---------------------------------+--------------
bob | | {joe}
joe | | {module_dml}
module_dml | Cannot login | {}

pgopen2017=# 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 = 'joe'
AND g.rolname = 'module_dml'
ORDER BY 1, 2;
group | user | admin | g_super | u_super
------------+------+-------+---------+---------
module_dml | joe | f | f | f
(1 row)

pgopen2017=# 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 = 'bob'
AND g.rolname = 'module_dml'
ORDER BY 1, 2;
group | user | admin | g_super | u_super
-------+------+-------+---------+---------
(0 rows)

Better would be a recursive WITH clause. An example can be seen in the
README.md file here (see the VIEW roletree):

https://github.com/pgaudit/set_user

Then you can do something like:
SELECT
ro.rolname,
ro.rolcanlogin,
ro.rolparents
FROM roletree ro
WHERE 'module_dml' = ANY (rolparents);
rolname | rolcanlogin | rolparents
---------+-------------+------------------
bob | t | {module_dml,joe}
joe | t | {module_dml}
(2 rows)

On Mon, Aug 7, 2017 at 8:05 PM, Joe Conway wrote:
ALTER USER whomever SET log_statement = mod;

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.

And in turn, this can be done like so:

SELECT * FROM pg_db_role_setting
WHERE setrole IN (SELECT ro.roloid FROM roletree ro
WHERE 'module_dml' = ANY (rolparents));
setdatabase | setrole | setconfig
-------------+---------+-----------
(0 rows)

DO $$
DECLARE
username text;
BEGIN
FOR username IN
SELECT ro.rolname FROM roletree ro
WHERE 'module_dml' = ANY (rolparents) LOOP
EXECUTE 'ALTER USER ' || username || ' SET log_statement = mod';
END LOOP;
END
$$;

SELECT * FROM pg_db_role_setting
WHERE setrole IN (SELECT ro.roloid FROM roletree ro
WHERE 'module_dml' = ANY (rolparents));
setdatabase | setrole | setconfig
-------------+---------+---------------------
0 | 150929 | {log_statement=mod}
0 | 150930 | {log_statement=mod}
(2 rows)

HTH,

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Thank you all for your input.

We plan to use ALTER USER username SET log_statement = mod when the user
account is created.

Regards,
Anand

--
View this message in context: http://www.postgresql-archive.org/Audit-based-on-role-tp5976507p5977104.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general