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@pgmasters.net
