pg_log_userqueries is a PostgreSQL module that logs each query executed that
follows a specific pattern (superuser, user name, database name, app name,
inet addr, query, query_id). It records each query in the standard log file.
Default is to log superusers queries executed on all databases. It is also
possible to filter the queries to be logged through a regular expression.

To install pg_log_userqueries, you should untar the pg_log_userqueries tarball
anywhere you want.

You'll then need to compile it with pgxs. So the pg_config tool must be in your
path. Depending on your PostgreSQL installation, you may need to install a dev
package. Once pg_config is in your path, do "make", and then "make install".

Here are the steps on a Rocky Linux 9 minimal install with PostgreSQL 15:

```
# install required OS packages
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf install -y https://dl.fedoraproject.org/pub/epel/epel-release-latest-9.noarch.rpm
sudo dnf install -y gcc git make clang ccache openssl-devel krb5-devel redhat-rpm-config
sudo dnf install -y postgresql15-server postgresql15-contrib postgresql15-devel

# compile and install pg_log_userqueries
git clone https://github.com/gleu/pg_log_userqueries.git
cd pg_log_userqueries/
export PATH=/usr/pgsql-15/bin:$PATH
make
sudo make install
```

Once it's installed, you'll need to configure PostgreSQL to make use of it:

* shared_preload_libraries='pg_log_userqueries'

There are also optional parameters. To configure them, with a PostgreSQL release
older than 9.2, you first need to add the class of pg_log_userqueries in
custom_variable_classes:

* custom_variable_classes = 'pg_log_userqueries'

In more recent release, custom_variable_classes is no longer available but you
can still configure pg_log_userqueries.

You'll be able to set the two following parameters:

* pg_log_userqueries.log_level: to choose the log level of pg_log_userqueries
  (WARNING by default)
* pg_log_userqueries.log_label: to give the prefix of the log line for
  pg_log_userqueries logs (defaults to the name of the extension).
* pg_log_userqueries.log_duration: to log statement duration time in ms.

Once this configuration is done, restart PostgreSQL.

You can specify exactly what you want to log.

If none of the following variables is set, all queries issued by superusers will be logged
in all databases, and only those.

If you set any of log_db, log_db_blacklist, log_user, log_user_blacklist,
log_addr, log_addr_blacklist, log_app, log_app_blacklist, log_query or
log_query_blacklist, superuser queries won't be systematically logged, and
you'll have to set log_superusers to on to reactivate it.

* pg_log_userqueries.log_superusers: to turn on/off logging of all superusers (off by default)
* pg_log_userqueries.log_db: to give a pipe (|) separated list of database to log.
* pg_log_userqueries.log_db_blacklist: to give a pipe (|) separated list of database to avoid logging.
* pg_log_userqueries.log_user: to give a pipe separated list of user to log.
* pg_log_userqueries.log_user_blacklist: to give a pipe separated list of user to avoid logging.
* pg_log_userqueries.log_addr: to give a pipe separated list of IP addresses to log.
* pg_log_userqueries.log_addr_blacklist: to give a pipe separated list of IP addresses to avoid logging.
* pg_log_userqueries.log_app: to give a pipe separated list of application name to log.
* pg_log_userqueries.log_app_blacklist: to give a pipe separated list of application name to avoid logging.
* pg_log_userqueries.log_query_id: to give a pipe separated list of query_id to log.
* pg_log_userqueries.log_query_id_blacklist: to give a pipe separated list of query_id to avoid logging.


For a query to be logged, it needs to :
* match the white list of a filter (eg: log_db) AND
* not match the back list of that filter (eg: log_db_blacklist).

For example :

* pg_log_userqueries.log_db="application_.*"
* pg_log_userqueries.log_db_blacklist="application_test"

This will log queries on all databases starting with "application_" except for
the database "application_test".

You can use advanced regular expression in that list. For example:

* pg_log_userqueries.log_user="postgres|admin_.*|.*_adm"

will match if the exact username is 'postgres', or if it begins with 'admin_' or
ends with '_adm'.

* pg_log_userqueries.log_query_id='1147616880456321454|11780828165390275418'

will match if the exact query_id is 1147616880456321454, or if it is 11780828165390275418.


You can also use pg_log_userqueries to log queries matching a particular regular
expression using the 'pg_log_userqueries.log_query' dedicated configuration directive:

* pg_log_userqueries.log_query: to give a regular expression to log all queries matching this regexp.

For example, to only log calls related to prepared transaction statements, you can set the following:

    pg_log_userqueries.log_query="^(PREPARE TRANSACTION|COMMIT PREPARED|ROLLBACK PREPARED)"

This filter will be applied after all other filters. For example if you have set log_user
but the user don't match nothing will be logged even if the query regexp match.

By default, if log_superusers is set, the filter is checked first and the query
is logged if the user has the superuser attribute.  Then log_user, log_db,
log_app and log_addr are checked in this order and the first matching value
will write to the log if log_query is not set or log_query match. If you want
that all of the filters, when defined, must match to write to log, activate the
match_all directive. In this case, it will log statements only when defined
filters for log_user, log_db, log_addr and log_query all match. These filters
will also apply to superuser queries if the corresponding directive is checked.

* pg_log_userqueries.match_all='on'

By default, pg_log_userqueries will write queries to PostgreSQL log destination.
A superuser can change this behavior with the pg_log_userqueries.log_destination
parameter. By default at stderr, put syslog if you prefer to use a syslog
daemon. You can also configure the syslog facility and the program
identification:

	pg_log_userqueries.syslog_facility='local0'
	pg_log_userqueries.syslog_ident='pg_log_userqueries'

As pg_log_userqueries is a shared_preload_library, you'll have to restart PostgreSQL to completely remove it. To temporarily deactivate it, you have a workaround though:

* pg_log_userqueries.file_switchoff: path to a file that must be root-owned. If this file is present, pg_log_userqueries won't log anymore
* pg_log_userqueries.time_switchoff: frequency at which backends will check file_switchoff's presence

If you need to know the current version number of this library, you can do this:

    strings $(pg_config --libdir)/pg_log_userqueries.so | grep "pg_log_userqueries version"