Re: pgaudit - an auditing extension for PostgreSQL

On 2/26/15 1:00 AM, Fujii Masao wrote:
> On Thu, Feb 26, 2015 at 1:40 PM, Alvaro Herrera
>> Clearly if you log only DROP TABLE, and then the malicious user hides
>> one such call inside a function that executes the drop (which is called
>> via a SELECT top-level SQL), you're not going to be happy.
>
> Yep, so what SQL should be logged in this case? Only "targeted" nested DDL?
> Both top and nested ones? Seems the later is better to me.
>
> What about the case where the function A calls the function B executing DDL?
> Every ancestor SQLs of the "targeted" DDL should be logged? Probably yes.

Currently only the targeted nested DDL would be logged. However, it
would log the top-level statement as well as the object that was dropped.

Here's an example from the unit tests:

do $$
begin
create table test_block (id int);
drop table test_block;
end; $$

When pg_audit.log = 'function, ddl' the output will be:

AUDIT: SESSION,FUNCTION,DO,,,do $$ begin create table test_block (id
int); drop table test_block; end; $$
AUDIT: SESSION,DDL,CREATE TABLE,TABLE,public.test_block,do $$ begin
create table test_block (id int); drop table test_block; end; $$
AUDIT: SESSION,DDL,DROP TABLE,TABLE,public.test_block,do $$ begin
create table test_block (id int); drop table test_block; end; $$

You can see that in the create and drop audit entries the
fully-qualified name is given. The statement comes from
debug_query_string so it shows the top-level statement, even though more
detail is given in the other fields when possible.

If pg_audit.log = 'ddl' then the DO entry would not be logged even
though statements under it were logged.

At least, that's the way it works currently.

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