Re: Auditing extension for PostgreSQL (Take 2)

On 4/6/15 8:40 AM, Sawada Masahiko wrote:
> On Fri, Apr 3, 2015 at 10:01 PM, David Steele <david(at)pgmasters(dot)net> wrote:
>> On 4/3/15 3:59 AM, Sawada Masahiko wrote:
>>> On Thu, Apr 2, 2015 at 2:46 AM, David Steele <david(at)pgmasters(dot)net> wrote:
>>>> Let me know if you see any other issues.
>>>>
>>>
>>> I pulled HEAD, and then tried to compile source code after applied
>>> following "deparsing utility command patch" without #0001 and #0002.
>>> (because these two patches are already pushed)
>>> <http://www.postgresql.org/message-id/20150325175954.GL3636@alvh.no-ip.org>
>>>
>>> But I could not use new pg_audit patch due to compile error (that
>>> deparsing utility command patch might have).
>>> I think I'm missing something, but I'm not sure.
>>> Could you tell me which patch should I apply before compiling pg_audit?
>>
>> When Alvaro posted his last patch set he recommended applying them to
>> b3196e65:
>>
>> http://www.postgresql.org/message-id/20150325175954.GL3636@alvh.no-ip.org
>>
>> Applying the 3/25 deparse patches onto b3196e65 (you'll see one trailing
>> space error) then applying pg_audit-v6.patch works fine.
>>
>
> I tested v6 patch, but I got SEGV when I executed test() function I
> mentioned up thread.
> Could you address this problem?

Unfortunately I'm not able to reproduce the issue. Here's what I tried
based on your original test:

postgres=# create table hoge (id int);
CREATE TABLE
postgres=# create function test() returns int as $$
postgres$# declare
postgres$# cur1 cursor for select * from hoge;
postgres$# tmp int;
postgres$# begin
postgres$# open cur1;
postgres$# fetch cur1 into tmp;
postgres$# return tmp;
postgres$# end$$
postgres-# language plpgsql ;
CREATE FUNCTION
postgres=# select test();
test
------

(1 row)
postgres=# insert into hoge values (1);
INSERT 0 1
postgres=# select test();
test
------
1
(1 row)

And the log output was:

prefix LOG: statement: create table hoge (id int);
prefix DEBUG: EventTriggerInvoke 16385
prefix LOG: AUDIT: SESSION,3,1,DDL,CREATE
TABLE,TABLE,public.hoge,CREATE TABLE public.hoge (id pg_catalog.int4
) WITH (oids=OFF)
prefix LOG: statement: 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 ;
prefix DEBUG: EventTriggerInvoke 16385
prefix LOG: AUDIT: SESSION,4,1,DDL,CREATE
FUNCTION,FUNCTION,public.test(),"CREATE FUNCTION public.test() RETURNS
pg_catalog.int4 LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT
SECURITY INVOKER COST 100 AS '
declare
cur1 cursor for select * from hoge;
tmp int;
begin
open cur1;
fetch cur1 into tmp;
return tmp;
end'"
prefix LOG: statement: select test();
prefix LOG: AUDIT: SESSION,5,1,READ,SELECT,,,select test();
prefix LOG: AUDIT:
SESSION,5,2,FUNCTION,EXECUTE,FUNCTION,public.test,select test();
prefix LOG: AUDIT: SESSION,5,3,READ,SELECT,,,select * from hoge
prefix CONTEXT: PL/pgSQL function test() line 6 at OPEN
prefix LOG: statement: insert into hoge values (1);
prefix LOG: AUDIT: SESSION,6,1,WRITE,INSERT,,,insert into hoge values (1);
prefix LOG: statement: select test();
prefix LOG: AUDIT: SESSION,7,1,READ,SELECT,,,select test();
prefix LOG: AUDIT:
SESSION,7,2,FUNCTION,EXECUTE,FUNCTION,public.test,select test();
prefix LOG: AUDIT: SESSION,7,3,READ,SELECT,,,select * from hoge
prefix CONTEXT: PL/pgSQL function test() line 6 at OPEN

Does the above look like what you did? Any other information about your
environment would also be helpful. I'm thinking it might be some kind
of build issue.

--
- David Steele
david(at)pgmasters(dot)net