Message-ID: <55122A1E.7030401@pgmasters.net>
Date: Tue, 24 Mar 2015 23:23:10 -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>
CC: Alvaro Herrera <alvherre@2ndquadrant.com>,
 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>
 <55118509.8020009@pgmasters.net>
 <CAD21AoD+MFYjjTz08gZtyDNQ77kt71ckyKvo7W+QuPq4icMjQA@mail.gmail.com>
In-Reply-To: 
 <CAD21AoD+MFYjjTz08gZtyDNQ77kt71ckyKvo7W+QuPq4icMjQA@mail.gmail.com>
OpenPGP: id=2624538721DAF9D5FDF4952A9FCDF424A2739CF3
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="9VLP99QculUjDMo5eAscxE4b5DdOxKB6s"
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

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

> On Wed, Mar 25, 2015 at 12:38 AM, David Steele <david@pgmasters.net> wr=
ote:
>>> 2. OBJECT auditing does not work before adding acl info to pg_class.r=
el_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;
>>>
>>> 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=
 that 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 h=
oge;
>>>
>>> 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.
>=20
> It means that OBJECT log is not logged just after creating table, even
> if that table is touched by its owner.
> To write OBJECT log, we need to grant privilege to role at least. right=
?

Exactly.  Privileges must be granted to the audit role in order for
object auditing to work.

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


--9VLP99QculUjDMo5eAscxE4b5DdOxKB6s
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

iQIcBAEBCAAGBQJVEioeAAoJEIA2uIJQ5SFAOLUQAJ2H+WseJVbecFOkEqJsGTd1
HyYdXgO9CWOPz+ulTQ3KEpaEIQlTfvHK7jFjI/j2cFCC3R/udUDJ11WJ1WXL2S+o
FbZ7pwITv9ZK4nEX086fPd9k+ICX/TivM3MgIz1vDGAABrgkTyNimZklQrFk4/p7
139N11zzgj3XBt7sjtD5NElHmg7DsU5V18rtSEsGNhFHfSyG16jfSaYvLk3JP1m+
U4hn6Gs0ZSI5+JXEVC/5t2Vqiw5gLV1PLsXSJ8pO4IeCnkoVHms2eaWj9BxrGEwN
Jr7ijWcVCsV/J3YVyiFCvxKE7bVCV4xlmthR71FoQaPR3bZqGhpqKzqVRJVHoMQB
IYN6FDpVWXEQMTSls0I6X1fQeNxY+Xy5FFaeyAFZHX3F+dnHVFHlzTyBknkYoau8
L3jiUqUy8FLYk2qrNiOWAKjs+XG9+WGpteV31aOsSx3Z9D2xIfFz/KFQ9GhvQTqM
C56hN4cJChAjzdI/Lde2OYA8b71NGmE52QkwBliuqagcqL/pIF5Y1lvWpcSDONDS
yl6yxcX3mQ7Qke4fRYcX9p7a4U4ZWd34+iSeXj8D1XE64E/obV3LH8n7/QXEOJEq
UtXB9h6ZxPHDbVVzTBPMaWPDrXktfgz5iF2Ld3rIH8mLKir4er1Ep2K7GSXTqJrK
i47o887reWcg+96XNSvd
=f7E4
-----END PGP SIGNATURE-----

--9VLP99QculUjDMo5eAscxE4b5DdOxKB6s--