Message-ID: <5512B71C.7030808@pgmasters.net>
Date: Wed, 25 Mar 2015 09:24:44 -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>
 <55122A1E.7030401@pgmasters.net>
 <CAD21AoBFs2sA31Y+zgioQYfiXc0vGEOM3GcgouNc_AH00QCCow@mail.gmail.com>
In-Reply-To: 
 <CAD21AoBFs2sA31Y+zgioQYfiXc0vGEOM3GcgouNc_AH00QCCow@mail.gmail.com>
OpenPGP: id=2624538721DAF9D5FDF4952A9FCDF424A2739CF3
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="BF6Onkl1vdqacilAGX4AHEEov57J8LuWp"
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

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

On 3/25/15 7:46 AM, Sawada Masahiko wrote:
> On Wed, Mar 25, 2015 at 12:23 PM, David Steele <david@pgmasters.net> wr=
ote:
>>> On Wed, Mar 25, 2015 at 12:38 AM, David Steele <david@pgmasters.net> =
wrote:
>>>>> 2. OBJECT auditing does not work before adding acl info to pg_class=
=2Erel_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=
 hoge;
>>>>>
>>>>> The both OBJCET and SESSION log are logged.
>>>>
>>>> Looks right to me.  If you don't want the session logging then disab=
le
>>>> pg_audit.log.
>>>>
>>>> Session and object logging are completely independent from each othe=
r:
>>>> one or the other, or both, or neither can be enabled at any time.
>>>
>>> It means that OBJECT log is not logged just after creating table, eve=
n
>>> if that table is touched by its owner.
>>> To write OBJECT log, we need to grant privilege to role at least. rig=
ht?
>>
>> Exactly.  Privileges must be granted to the audit role in order for
>> object auditing to work.
>>
>=20
> The table owner always can touch its table.
> So does it lead that table owner can get its table information while
> hiding OBJECT logging?

Yes, the table owner would be able to access the table without object
logging if grants to that table were not made to the audit role.  That
would also be true for any other user that had grants on the table.

The purpose of object auditing is to allow more fine-grained control and
is intended to be used in situations where you only want to audit some
things, rather than all things.  Logging everything is better done with
the session logging.

However, object logging does yield more information since it lists every
table that was touched by the statement, so there may be cases where
you'd like to object log everything.  In that case I'd recommend writing
a bit of plpgsql code to create the grants.

> Also I looked into latest patch again.
> Here are two review comment.
>=20
> 1.
>> typedef struct
>> {
>>    int64 statementId;
>>   int64 substatementId;
> Both statementId and substatementId could be negative number.
> I think these should be uint64 instead.

True.  I did this because printf formatting for uint64 seems to be vary
across platforms.  int64 formatting is more standard and still gives
more than enough IDs.

I could change it back to uint64 if you have a portable way to modify
the sprintf at line 507.

> 2.
> I got ERROR when executing function uses cursor.
>=20
> 1) create empty table (hoge table)
> 2) create test function as follows.
>=20
> create function test() returns int as $$
> declare
>     cur1 cursor for select * from hoge;
>     tmp int;
> begin
>     open cur1;
>     fetch cur1 into tmp;
>    return tmp;
> end$$
> language plpgsql ;
>=20
> 3) execute test function (got ERROR)
> =3D# select test();
> LOG:  AUDIT: SESSION,6,1,READ,SELECT,,,selecT test();
> LOG:  AUDIT: SESSION,6,2,FUNCTION,EXECUTE,FUNCTION,public.test,selecT t=
est();
> LOG:  AUDIT: SESSION,6,3,READ,SELECT,,,select * from hoge
> CONTEXT:  PL/pgSQL function test() line 6 at OPEN
> ERROR:  pg_audit stack is already empty
> STATEMENT:  selecT test();
>=20
> It seems like that the item in stack is already freed by deleting
> pg_audit memory context (in MemoryContextDelete()),
> before calling stack_pop in dropping of top-level Portal.

Good catch, I'll add this to my test cases and work on a fix.  I think I
see a good way to approach it.

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


--BF6Onkl1vdqacilAGX4AHEEov57J8LuWp
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

iQIcBAEBCAAGBQJVErccAAoJEIA2uIJQ5SFAhkgQAJJQfeTVSF9dZtqcijnvrU/G
17Y9A67HJNLkba4K0DFwNFBxN293bPRSK2SoJdd4LN0hYavG9wLa9DzyaL6vrtHy
J5gHP3Bt7TDrRpBHSCTBnQNcyIzoZt3NV/eBPbOQ8BXKbQhJ29NjFlNmKaotZsjH
BLLmTV556EpCUDrQZuaHHzH3/xOmf9SoCw9jK72/X/95eTOG2yIS6rTbmwrAgGwQ
w6lz+txIKs0F5R41B1HCIwq6mDmyFWicj8E8sBx9bQCww5cuJlAVIFh7GFCZ8/fd
VunBWwUd/yTqFoRc3sQCsipyve+YkX6sTJWoWqBrtJzBGGVg1JDjjqUrUgxIR/xT
Ypm0A75t/3Qn5Mk6Pxkgqe/jyBCCoHnsFYFAktZYQ+3Tp6pT78AVgpXc9YXBVgTW
72WY+FLTAB3B1X7ys+XWa2vIGlaUvF0oX2wGhY3IRJmYRk6pnmvl2hX2tr+2EzcG
NkGWlV9FQqNdfc7+sSdojMapkKWG5dWCoRW4Iwr9ncnd3xwNpqS8AHiCTb8PD3qh
25qjbTXVy4XH1mnEXMZS0M0yq3SJ9TPmt0f5/aNmXWPxANGZ7cokWpuuqX6oM8bo
iLuYgL/aISjtIfQs4Wo4bodYZeP0GtKKbmw0BGg8GtnBjPCiXmbHe6uYIlaYtEDr
WFnPQqrycktlSv9EMt+E
=xM3q
-----END PGP SIGNATURE-----

--BF6Onkl1vdqacilAGX4AHEEov57J8LuWp--