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

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?

6 ответов

22 просмотра

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/

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.

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

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)?

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.

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.

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

Обсуждают сегодня

Мужики и девушки, привет) в Вelphi xe7 в настройках во вкладке "Editor Options" далее " Color" есть список: "Elements", открыв который мы можем настраивать отображение разных...
Kraszx
14
Добрый вечер. Есть вопрос, а может и предложение. Был у меня диалог в другой группе о делфи и я задался вопросом: "А нельзя ли в делфи цвет //коментария и {комментария} сде...
Kraszx
24
как быть с принтером? такой подход прокатит?
zamtmn
12
Всем привет! Подскажи, пожалуйста, как передать в TComboBox сразу значение и id записи. На Delphi я делал так: ComboBox1.Items.AddObject('Какое-то значение', Pointer(id запис...
Евгений
13
Мдя, прикол, боевая сборка запускается (именно под отладчиком) после F9 примерно полторы минуты (97 секунд если быть точным). Начал копать - проблема детектится сразу - зависа...
Александр (Rouse_) Багель
38
А вот это что за конструкция? Вернее, она тут нафига?
Serjone
10
Привет. Подскажите, как правильно сматчить лист фиксированного размера, чтобы компилятор не говорил мне о неполном паттерне? Допустим что-то такое [x', y'] = sort [x, y]?
Arseny
8
Здравствуйте, вопрос по структурам данных. Были у вас случаи, когда пришлось писать деревья или двунаправленные списки?
/ /
50
Товарищи, кто работа с iphelper? Или может я в самой логике ошибки фигачу, не пойму.... var ifTable : PMIB_IFTABLE; size, corSize: DWORD; Buffer ...
Warfarellen
4
Мужики. привет) в Вelphi xe7 в настройках во вкладке "Editor Options" далее " Color" есть список: "Elements", открыв который мы можем настраивать отображение разных элементов...
Kraszx
2
Карта сайта