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 ответов

24 просмотра

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.

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

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

а через ESC-код ?
Alexey Kulakov
29
30500 за редактор? )
Владимир
47
Чёт не понял, я ж правильной функцией воспользовался чтобы вывести отладочную информацию? но что-то она не ловится
notme
18
У меня есть функция где происходит это: write_bit(buffer, 1); write_bit(buffer, 0); write_bit(buffer, 1); write_bit(buffer, 1); write_bit(buffer, 1); w...
~
13
Недавно Google Project Zero нашёл багу в SQLite с помощью LLM, о чём достаточно было шумно в определённых интернетах, которые сопровождались рассказами, что скоро всех "ибешни...
Alex Sherbakov
5
в JclConsole объявлено так: function CtrlHandler(CtrlType: DWORD): BOOL; stdcall; - где ваше объявление с stdcall? у вас на картинке нет stdcall
Karagy
8
Как передать управляющий символ в открытую через CreateProcess консоль? Собсна, есть процедура: procedure TRedirectThread.WriteData(Data: OEMString); var Written: Cardinal;...
Serjone
6
Ребят в СИ можно реализовать ООП?
Николай
33
program test; {$mode delphi} procedure proc(v: int32); overload; begin end; procedure proc(v: int64); overload; begin end; var x: uint64; begin proc(x); end. Уж не знаю...
notme
6
у вас два процесса. один посылает другому сигнал. у вас есть код обоих процессов? если всё не так - расскажите как оно на самом деле. а именно кто кому чего, есть-ли консоли,...
Karagy
6
Карта сайта