Message-ID: <55118509.8020009@pgmasters.net>
Date: Tue, 24 Mar 2015 11:38:49 -0400
From: David Steele <david@pgmasters.net>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.10;
 rv:31.0) Gecko/20100101 Thunderbird/31.5.0
MIME-Version: 1.0
To: Sawada Masahiko <sawada.mshk@gmail.com>,
 Alvaro Herrera <alvherre@2ndquadrant.com>
CC: Abhijit Menon-Sen <ams@2ndquadrant.com>,
 Stephen Frost <sfrost@snowman.net>, Fujii Masao <masao.fujii@gmail.com>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: Auditing extension for PostgreSQL (Take 2)
References: <54E005CC.1060605@pgmasters.net>
 <20150217153402.GQ6717@tamriel.snowman.net> <54EB4E56.7050102@pgmasters.net>
 <54ECA551.8060306@pgmasters.net> <20150323053146.GA1919@toroid.org>
 <55104204.80008@pgmasters.net>
 <CAD21AoBBQXPBiOncyvZdtOfSzr121GyDoARc4jXo2v-JM_45EQ@mail.gmail.com>
 <20150323181724.GG3636@alvh.no-ip.org>
 <CAD21AoA1-UVvjt0q8wvXBRc83RRwDhBgtRTR5vXByPdKb=e6-g@mail.gmail.com>
In-Reply-To: 
 <CAD21AoA1-UVvjt0q8wvXBRc83RRwDhBgtRTR5vXByPdKb=e6-g@mail.gmail.com>
OpenPGP: id=2624538721DAF9D5FDF4952A9FCDF424A2739CF3
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="1hmAVUhh9Rfo3P3H8imuO6k8cCCvNWnGi"
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--1hmAVUhh9Rfo3P3H8imuO6k8cCCvNWnGi
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Hi Sawada,

Thank you for taking the time to look at the patch.

On 3/24/15 10:28 AM, Sawada Masahiko wrote:
> I've applied these patchese successfully.
>=20
> I looked into this module, and had a few comments as follows.
> 1. pg_audit audits only one role currently.
> In currently code, we can not multiple user as auditing user. Why?
> (Sorry if this topic already has been discussed.)

There is only one master audit role in a bid for simplicity.  However,
there are two ways you can practically have multiple audit roles (both
are mentioned in the docs):

1) The audit role honors inheritance so you can grant all your audit
roles to the "master" role set in pg_audit.role and all the roles will
be audited.

2) Since pg_audit.role is a GUC, you can set a different audit role per
database by using ALTER DATABASE ... SET.  You can set the GUC per logon
role as well though that would probably make things very complicated.
The GUC is SUSET so normal users cannot tamper with it.

> 2. OBJECT auditing does not work before adding acl info to pg_class.rel=
_acl.
> In following situation, pg_audit can not audit OBJECT log.
> $ cat postgresql.conf | grep audit
> shared_preload_libraries =3D 'pg_audit'
> pg_audit.role =3D 'hoge_user'
> pg_audit.log =3D 'read, write'
> $ psql -d postgres -U hoge_user
> =3D# create table hoge(col int);
> =3D# select * from hoge;
> LOG:  AUDIT: SESSION,3,1,READ,SELECT,,,select * from hoge;
>=20
> OBJECT audit log is not logged here since pg_class.rel_acl is empty
> yet. (Only logged SESSION log)
> So after creating another unconcerned role and grant any privilege to t=
hat user,
> OBJECT audit is logged successfully.

Yes, object auditing does not work until some grants have been made to
the audit role.

> =3D# create role bar_user;
> =3D# grant select on hoge to bar_user;
> =3D# select * from hoge;
> LOG:  AUDIT: SESSION,4,1,READ,SELECT,,,select * from hoge;
> LOG:  AUDIT: OBJECT,4,1,READ,SELECT,TABLE,public.hoge,select * from hog=
e;
>=20
> The both OBJCET and SESSION log are logged.

Looks right to me.  If you don't want the session logging then disable
pg_audit.log.

Session and object logging are completely independent from each other:
one or the other, or both, or neither can be enabled at any time.

> 3. pg_audit logged OBJECT log even EXPLAIN command.
> EXPLAIN command does not touch the table actually, but pg_audit writes
> audit OBJECT log.
> I'm not sure we need to log it. Is it intentional?

This is intentional.  They are treated as queries since in production
they should be relatively rare (that is, not part of a normal function
or process) and it's good information to have because EXPLAIN can be
used to determine the number of rows in a table, and could also be used
to figure out when data is added or removed from a table.  In essence,
it is a query even if it does not return row data.

If that sounds paranoid, well, auditing is all about paranoia!

--=20
- David Steele
david@pgmasters.net


--1hmAVUhh9Rfo3P3H8imuO6k8cCCvNWnGi
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2

iQIcBAEBCAAGBQJVEYUJAAoJEIA2uIJQ5SFALb8P/0v5dqb6qBJQDbgyv18grIfz
WwmbZgUPZzvYCWLU5XTGYM5Arub9MDMMjpTnAUchI0jNPGwMJ4QZK/ZH8Cu9VIvP
6bMILgu9Mxrry0dIUVAtX5WRcZErtvUm1wC0jOE4P7F5ylpPRLJCyXyzx8jUmKZf
nqqnenZv6iI3vzl6729ij9I44982F0IHFhQgzuKYjj/ALim3LwWBQLDcv170QbCY
Mb9RPohDrdM9MEhMktn+to3qexUKnuOP/8LjRcfQhx4swszil8B6Si+OjA231lxL
b2/2PebdJBdQYA72+49L/lX7xAv3dQylirZg115izr3m9NQHL8E8O/gCE5+tX6sl
sgRXFCmDvYQd1Jz2o/khDG2amqd7cd9c+3RsBEvho6SdXM7c0z/GfhSos10iHc/n
muT24E57bBeJrzon8+H20enMl88kXW3AcRr/8maGdfYQzNAkmFXA48mHAHhWeOLm
06M6ROiBP6Hm+DgFsyPq0MNMMq0KHGnNPqjFtNYZO2BRL0P/BJ+RrDmAA1k8pBd8
0I9y7JDEcLAIbqbGJkCW7Lld2rHndAOAJwJBoOb/V/WWZ2rVW/NHrnho0USqWQqi
Ujg/M0GOYEztHCiVXcDh04XGKvgg6VkKgOaZd6IrBKd3kg29eiFp1J/8trsbkjN4
Eo5QqFMGV6InIkheNeke
=v3ck
-----END PGP SIGNATURE-----

--1hmAVUhh9Rfo3P3H8imuO6k8cCCvNWnGi--