Log queries accessed by particular user for all tables in single database postgresql

Question

I am in need of logging all the SQL queries accessed by a particular user for single database. I had tried below

ALTER ROLE test_user SET log_statement TO 'all';

While checking the logs it seems to be logging the queries accessed by the user test_user, But I could find the logs for other queries too, like the below:

statement: SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN ('r', 'S', 'v', 'm', 'f', 'p') AND substring(pg_catalog.quote_ident(c.relname),1,18)='table_name' AND pg_catalog.pg_table_is_visible(c.oid) AND c.relnamespace <> (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')

To be clean and to optimize the storage occupied by the log, I want to log the queries accessed by this user alone (Not for all users) and only for all the tables in a specific database without logging the queries internally used by Postgres, Is there any other ways to achieve this? Thanks!

EDIT:

As @jjanes pointed the additional logs for pg_catalog were logged when I do tab completion on the table names, Also, it doesn't log this info while trying from superuser, It logs it only for test_user. And I could see the username as well on the log along with the database info. Please find the example log below:

2020-12-15 05:48:34 UTC:91.241.19.84(46672):pgsql@postgres:[15559]:DETAIL: Role "pgsql" does not exist.
Connection matched pg_hba.conf line 13: "host all all 0.0.0.0/0 md5"
2020-12-15 05:48:45 UTC:157.49.192.241(52106):test_user@foobar:[12991]:LOG: statement: SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN ('r', 'S', 'v', 'm', 'f', 'p') AND substring(pg_catalog.quote_ident(c.relname),1,11)='table_name_for_tab_completion' AND pg_catalog.pg_table_is_visible(c.oid) AND c.relnamespace <> (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')
UNION
SELECT pg_catalog.quote_ident(n.nspname) || '.' FROM pg_catalog.pg_namespace n WHERE substring(pg_catalog.quote_ident(n.nspname) || '.',1,11)='table_name_for_tab_completion' AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,11) = substring('table_name_for_tab_completion',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) > 1
UNION
SELECT pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind IN ('r', 'S', 'v', 'm', 'f', 'p') AND substring(pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname),1,11)='table_name_for_tab_completion' AND substring(pg_catalog.quote_ident(n.nspname) || '.',1,11) = substring('table_name_for_tab_completion',1,pg_catalog.length(pg_catalog.quote_ident(n.nspname))+1) AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,11) = substring('table_name_for_tab_completion',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) = 1
LIMIT 1000
2020-12-15 05:48:46 UTC:157.49.192.241(52106):test_user@foobar:[12991]:LOG: statement: SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN ('r', 'S', 'v', 'm', 'f', 'p') AND substring(pg_catalog.quote_ident(c.relname),1,17)='table_name_for_tab_completion' AND pg_catalog.pg_table_is_visible(c.oid) AND c.relnamespace <> (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')
UNION
SELECT pg_catalog.quote_ident(n.nspname) || '.' FROM pg_catalog.pg_namespace n WHERE substring(pg_catalog.quote_ident(n.nspname) || '.',1,17)='table_name_for_tab_completion' AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,17) = substring('table_name_for_tab_completion',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) > 1
UNION
SELECT pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind IN ('r', 'S', 'v', 'm', 'f', 'p') AND substring(pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname),1,17)='table_name_for_tab_completion' AND substring(pg_catalog.quote_ident(n.nspname) || '.',1,17) = substring('table_name_for_tab_completion',1,pg_catalog.length(pg_catalog.quote_ident(n.nspname))+1) AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,17) = substring('table_name_for_tab_completion',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) = 1
LIMIT 1000
2020-12-15 05:48:48 UTC:157.49.192.241(52106):test_user@foobar:[12991]:LOG: statement: select count(*) from table_name;

jjanes · Accepted Answer · 2020-12-14 15:20:47Z

2

To apply it to just one user in just one database, you can just add the database to the statement:

ALTER ROLE test_user IN DATABASE foobar SET log_statement TO 'all';

Of course you would also have to undo the less specific you used first:

ALTER ROLE test_user RESET log_statement;

As for the specific query you showed us, you didn't show any evidence that is not for that user in the first place. It looks like the type of query that might be issued by a client program for something like introspection or tab-completion. These are issued as the user on behalf of the user by the user's client, the database server has no way to suppress them.

answered Dec 14, 2020 at 15:20

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

Collectives™ on Stack Overflow

Log queries accessed by particular user for all tables in single database postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related