Thread: [GENERAL] Audit based on role

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?

Regards,
Anand





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

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

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.

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

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.

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

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.