Hello everyone, How can I log who/when made a select query

Question

Hello everyone, How can I log who/when made a select query

on a specific table ?

I'm using PGAUDIT with pgaudit.log='write, ddl, role'. Since my disk space is limited, I do not want to open "read" operations on the pgaudit settings.
I can't write a TRIGGER because it is a SELECT operation.
I don't want to write a RULE because it doesn't serve the main purpose of the RULE.

What is your suggestions?

#programming #russian

0

19.03.2024

6 ответов

11 просмотров

Sercan Автор вопроса

Kirk
First, this is not "directly" possible. Second, th...

Thanks for your reply kirk. I understand that every solution I choose has more disadvantages. I think I will restrict access to these tables. It will be a cleaner and cost-effective solution. Because free disk space and query costs are the limiting factor for me.

0

1 месяц назад

Stefanie Janine Stölting

Sercan
Thanks for your reply kirk. I understand that ev...

You can inherit rights, meaning roles can have automatically rights granted on new tables/views. If that's new to you, take a look at my blog post about this https://proopensource.it/blog/postgresql-roles

0

1 месяц назад

Sercan Автор вопроса

Stefanie Janine Stölting
You can inherit rights, meaning roles can have aut...

Thanks for your reply Stefanie. I read your blog post and I found it very useful. I've never tried to block access to a single table in postgresql before. I was using some default defined roles when creating a user: "pg_read_all_data & pg_write_all_data". But when I gave these default defined roles, I could not REVOKE SELECT for a single table. It did not create an access barrier. That's why I used the method in your blog post. This confused me: is it my mistake to use these predefined roles when creating READ USER (pg_read_all_data) and DML USER (pg_read_all_data & pg_write_all_data)?

0

1 месяц назад

Kirk

Sercan
Thanks for your reply Stefanie. I read your blog...

Well, keep in mind, there are 2 flavors of the commands. ONE is for future objects. One is for existing objects. If I do "GRANT SELECT ON ANY TABLE TO spock". That applies to existing tables. Whereas, "ALTER DEFAULT PRIVILEGES..." grants rights on Future Objects. That was the mind bending part for me. If that helps.

0

1 месяц назад

Stefanie Janine Stölting

Sercan
Thanks for your reply Stefanie. I read your blog...

These roles are working fine, but they are limited and only available since PostgreSQL 14. For example they don't support execution of procedures, functions, and triggers. And you can't enhance them.

0

1 месяц назад

Kirk · Accepted Answer

First, this is not "directly" possible. Second, this turns a read-only query into a writing query, which can degrade performance. Third, is "select" on this table REALLY this important to know? At what cost? At Cybertec's website, there is an article about creating VIEWS that LOG whenever they are called. As part of a push to rename all views to a consistent naming style, and to not break existing code which definitely creates view names dynamically. So, that article could be used if you renamed the table, create a view in it's place, similar to the concept created in the article. But it's a stretch. To me, this is not a normal requirement. And it's an expensive requirement. https://www.cybertec-postgresql.com/en/view-access-logging-fix-postgresql/

169 похожих чатов

Hello everyone, How can I log who/when made a select query

6 ответов

Похожие вопросы