dotConnect PostgreSQL auditing

dotConnect PostgreSQL auditing

Postby sallys.dba » Wed 20 May 2015 03:21

Hi,

We've an application that uses .Net Entity framework (4.5.1) and dotConnect for PostgreSQL (version 7.3.352.6), I need to track SQL transactions in the DB for auditing purpose, however even though I think I've turned on everything possible for logging, I'm not seeing the actual values in the SQL statements, all I'm seeing is just the parameter markers like the following:
AUDIT_RECORD_START,2015-05-14 08:36:34.481 UTC,192.168.1.1(54610),postgres,test,828,[unknown],4/167,00000,LOG: execute PRSTMT144390157037054937: INSERT INTO "Clients"("Name", "Description", "UserName", "Code", "Status", "CreateEventId", "UpdateEventId")

VALUES ($1, $2, $3, $4, $5, $6, $7)

RETURNING "Id"



Does anyone know if this is a configuration issue or this is simply because
of the way that data are being handled by the Entity Framework? Is there any other way to print the values of the parameter markers with the underlying query/statement?


Following is the postgres.conf snippet for the "logging parameters":
log_destination = 'stderr'
# This is used when logging to stderr:
logging_collector = on
# These are only used if logging_collector is on:
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d.log'

#log_file_mode = 0600
#log_truncate_on_rotation = off
log_rotation_age = 1d

#log_rotation_size = 10MB
# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
# This is only relevant when logging to eventlog (win32):
#event_source = 'PostgreSQL'

# - When to Log -

#client_min_messages = notice
#log_min_messages = warning
#log_min_error_statement = error
#log_min_duration_statement = -1


# - What to Log -

debug_print_parse = on
debug_print_rewritten = on
debug_print_plan = on
debug_pretty_print = on
log_checkpoints = off
log_connections = on
log_disconnections = on
log_duration = off
log_error_verbosity = terse
#log_hostname = off
log_line_prefix = 'AUDIT_RECORD_START,%m,%r,%u,%d,%p,%a,%v,%e,'

#log_lock_waits = off
log_statement = 'all'
#log_temp_files = -1
log_timezone = 'UTC'



Thanks,
Sally
sallys.dba
 
Posts: 1
Joined: Tue 19 May 2015 19:09

Re: dotConnect PostgreSQL auditing

Postby Shalex » Wed 20 May 2015 10:51

sallys.dba wrote:Does anyone know if this is a configuration issue or this is simply because of the way that data are being handled by the Entity Framework?

If your EF LINQ query includes parameters, parameters in the generated SQL statement will also include parameters. The question why PostgreSQL log doesn't represent the parameters values in its log should be addressed to the PostgreSQL Support Team or asked at the corresponding forum. Refer to http://www.postgresql.org/message-id/0357196CB603794BB76F4F6B373F27330506EACE98@SERVER.ddnglobal.local.

sallys.dba wrote:Is there any other way to print the values of the parameter markers with the underlying query/statement?

You can implement a log at the .NET side
* with the dbMonitor tool: http://www.devart.com/dotconnect/postgresql/docs/?dbmonitor.html
* via MonitorEventHandler:http://www.devart.com/dotconnect/postgresql/docs/?Devart.Data~Devart.Common.DbMonitor~TraceEvent_EV.html
Shalex
Devart Team
 
Posts: 7773
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL