Loging activity for one user on specific database in Postgres

Question

I need to log all activity for some specific user on database. I have set up the logging with ALTER ROLE username SET log_statement TO 'all'; and the logging works fine, all queries from user are logged. The problem is that for this user queries to Postgres internal schemas (pg_catalog) from clients like psql and pgAdmin are also logged. I have a bunch of lines with SELECT pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname).... in the log that are of no use to me. Even worse this queries are more then one line in the log so it's not easy to filter them out.

Is it possible to somehow restrict the logging only to one specific database or schema and not to include queries to other schemas like pg_catalog?

pablosjv · Accepted Answer · 2018-05-16 08:42:12Z

1

I don't know if the standard logging utility in postgres has that option (my guess is no). But maybe it's worth a look to the pgaudit external library for postgres.

The module pgadmin is designed to generate audit logs, but it uses the standard postgres logging tool. You can tweak several parameters to customize the logs, and it has a specific parameter that I think is perfect for your use case. From the documentation:

pgaudit.log_catalog

Specifies that session logging should be enabled in the case where all relations in a statement are in pg_catalog. Disabling this setting will reduce noise in the log from tools like psql and PgAdmin that query the catalog heavily.

The default is on.

I hope it helps!

answered May 16, 2018 at 8:42

pablosjv

6739 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tometzky · Accepted Answer · 2017-03-12 16:48:54Z

0

Change your logging format from text to csv (log_destination=csvlog) — you can then import the data to the database and then filter out the queries you are not interested in:
Using CSV-Format Log Output

answered Mar 12, 2017 at 16:48

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

Collectives™ on Stack Overflow

Loging activity for one user on specific database in Postgres

2 Answers 2

pgaudit.log_catalog

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

pgaudit.log_catalog

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related