Re: [GENERAL] Audit based on role - Mailing list 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