Re: Audit based on role

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