Re: pgaudit - an auditing extension for PostgreSQL

From: David Steele <david(at)pgmasters(dot)net>
To: Abhijit Menon-Sen <ams(at)2ndQuadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: pgaudit - an auditing extension for PostgreSQL
Date: 2015-02-09 01:02:41
Message-ID: 54D80731.2010800@pgmasters.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)pgmasters(dot)net

Attachment Content-Type Size
pgaudit.c text/plain 30.7 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2015-02-09 01:21:10 Re: RangeType internal use
Previous Message Amit Langote 2015-02-09 00:38:49 Re: RangeType internal use