4

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?

Rui F Ribeiro
  • 367
  • 1
  • 8
  • 18
  • http://stackoverflow.com/questions/722221/how-to-log-postgresql-queries – Landon Kuhn Jun 15 '16 at 17:17
  • @LandonKuhn Thanks. I am aware of the logging directives, however I only intend to log the commands done by hand/by operators, not the commands done by programs/software. The intention is identical to my other question http://stackoverflow.com/questions/37834849/logging-mysql-interactive-queries – Rui F Ribeiro Jun 15 '16 at 17:18
  • 2
    Do the application and the interactive sessions connect as different users? You can set per-user overrides for many configuration variables with e.g. `ALTER USER ... SET log_statement = 'all'`. – Nick Barnes Jun 15 '16 at 22:10
  • @NickBarnes I would suggest you make that an answer. Whilst it is/was not my intention, it may work for my needs. – Rui F Ribeiro Jun 16 '16 at 07:46

2 Answers2

5

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.

Nick Barnes
  • 19,816
  • 3
  • 51
  • 63
1

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
Alasdair
  • 566
  • 1
  • 4
  • 16