CODEWITHSUNDEEP
postgresqldebian
Rui F Ribeiro
Rui F Ribeiro

Reputation: 408

Logging PostgreSQL interactive queries

I have several people doing some database queries directly into some systems.

I would like to log to a secure syslog server all the queries done interactively/by hand, or failing that, all the queries done by using the psql binary client.

I am using Debian Jessie, PostgreSQL 9.4 and 9.1.

How could I achieve that?

Upvotes: 4

Views: 1931

Answers (2)

Alasdair
Alasdair

Reputation: 596

You can also add these directives to the config files normally located in /etc/postgresql/{version}/main/postgresql.conf or /etc/postgresql/{version}/main/conf.d/my-overrides.conf e.g.

log_connections = true
log_disconnections = true
log_statement = all
log_duration = true

You can also add log_line_prefix to supply more detail regarding the connection (from the documentation) in the form:

log_line_prefix = "string"

e.g.

log_line_prefix = '%m [%p-%l] [%c] %u %d %a '

where (for completeness) from the documentation the printf options are:

%a  Application name
%u  User name
%d  Database name
%r  Remote host name or IP address, and remote port
%h  Remote host name or IP address
%p  Process ID
%t  Time stamp without milliseconds
%m  Time stamp with milliseconds
%n  Time stamp with milliseconds (as a Unix epoch)
%i  Command tag: type of session's current command
%e  SQLSTATE error code
%c  Session ID: see below
%l  Number of the log line for each session or process, starting at 1
%s  Process start time stamp
%v  Virtual transaction ID (backendID/localXID)
%x  Transaction ID (0 if none is assigned)
%q  Produces no output, but tells non-session processes to stop at this point in the string; ignored by session processes

Upvotes: 1

Nick Barnes
Nick Barnes

Reputation: 21306

There are a multitude of methods for setting configuration variables, with varying scopes.

As log_statement is a superuser-only setting, you can rule out all of the client-side options, and the server- or database-wide settings are presumably too broad for your case.

I believe this only leaves you with the per-user option, i.e.:

ALTER USER interactive_user SET log_statement = 'all';

Or if it makes things easier to manage:

ALTER SYSTEM SET log_statement = 'all';
ALTER USER application_user SET log_statement = 'none';

Note that these settings are only applied on connection, so they cannot be triggered (or bypassed) via SET ROLE commands.

Of course, this all assumes that your application and your interactive users aren't sharing logins, but I don't think there's anything else with the right level of granularity.

Upvotes: 6

Related Questions