Re: pgaudit - an auditing extension for PostgreSQL - Mailing list pgsql-hackers

On 2/2/15 3:49 PM, David Steele wrote:

The role-base approach being considered may strike some as a misuse of
the role system, but to my eye it is syntactically very close to how
Oracle does auditing prior to 12c.  Say you wanted to audit selects on
the table hr.employee:

Oracle: AUDIT SELECT ON hr.employee;
pgaudit: GRANT SELECT ON hr.employee TO audit; (assuming audit is the
role defined by pgaudit.roles)

Object-based auditing in Oracle would be very easy to migrate to the
grants needed for pgaudit.  In addition, if an AUDIT command were
introduced later in core, it would be easy to migrate from pgaudit to
AUDIT assuming the syntax was similar to grant, which seems plausible.

I decided to take a whack at this and see what I could come up with, starting with the code in master at https://github.com/2ndQuadrant/pgaudit.

I modified pgaudit.log to work similarly to Oracle's session-level logging, meaning user statements are logged instead of tables which are accessed. pgaudit.log still has the various classes of commands and only those commands which match one of the classes are logged. Note that the pgaudit.log GUC is SUSET but can be set at the cluster, database, or user level.

An example - log all statements that create an object or read data:
  DB: connect user postgres, database postgres SQL: set pgaudit.log = 'DEFINITION, READ' SQL: create user user1
  DB: connect user user1, database postgres SQL: create table account      (          id int,          name text,          password text,          description text      );
AUDIT: SESSION,DEFINITION,CREATE TABLE,TABLE,public.account,<sql>
 SQL: select *        from account;
AUDIT: SESSION,READ,SELECT,,,<statement>
 SQL: insert into account (id, name, password, description)                   values (1, 'user1', 'HASH1', 'blah, blah');
AUDIT: <nothing logged>

Object auditing is done via the grant system (similar to Oracle object auditing), but now there is now a single audit role (defined by the pgaudit.role GUC which can also be set at the cluster, database, or user level).

An example - using column-level grants since they are more interesting:

 DB: connect user postgres, database postgres SQL: set pgaudit.log = 'NONE' SQL: create role audit SQL: set pgaudit.role = 'audit'
  DB: connect user user1, database postgres
 SQL: grant select (password)         on public.account         to audit;
AUDIT: <nothing logged>
 SQL: select id, name        from account;
AUDIT: <nothing logged>
 SQL: select password        from account;
AUDIT: OBJECT,READ,SELECT,TABLE,public.account,<sql>
 SQL: grant update (name, password)         on public.account         to audit;
AUDIT: <nothing logged>
 SQL: update account         set description = 'yada, yada';
AUDIT: <nothing logged>
 SQL: update account         set password = 'HASH2';
AUDIT: OBJECT,WRITE,UPDATE,TABLE,public.account,<sql>

Session and object auditing can be used together so a statement that does not match on an object may still be session logged depending on the settings.

An example - in this case the pgaudit.log GUC will be set on the user and grants persist from the previous example.  Another table is added to show how that affects logging:
  DB: connect user postgres, database postgres SQL: alter role user1 set pgaudit.log = 'READ,WRITE';
AUDIT: <nothing logged>
  DB: connect user user1, database postgres SQL: create table account_role_map      (          account_id int,          role_id int      );
AUDIT: <nothing logged>
 SQL: grant select         on public.account_role_map         to audit;
AUDIT: <nothing logged>
 SQL: select account.password,              account_role_map.role_id        from account             inner join account_role_map                  on account.id = account_role_map.account_id
AUDIT: SESSION,READ,SELECT,,,<sql>
AUDIT: OBJECT,READ,SELECT,TABLE,public.account,<sql>
AUDIT: OBJECT,READ,SELECT,TABLE,public.account_role_map,<sql>
 SQL: update account         set description = 'yada, yada';
AUDIT: SESSION,WRITE,UPDATE,,,<sql>
 SQL: update account         set description = 'yada, yada'       where password = 'HASH2';
AUDIT: SESSION,WRITE,UPDATE,,,<sql>
AUDIT: OBJECT,WRITE,UPDATE,TABLE,public.account,<sql>

That about covers it.  I'd be happy to create a pull request to contribute the code back to 2ndQuadrant.  There are some things I'm still planning to do, but I think this draft looks promising.  pgaudit.c is attached.

Thoughts and suggestions are welcome.

-- 
- David Steele
david@pgmasters.net