Comments (4)
Audit logging - recording into a second database who inserted/updated/deleted tuples in any table, by what method (i.e. application_name) and when.
We currently use triggers on every table to record this information, but that is quite a heavyweight approach.
In contrast, we could use logical replication + wal2json, feed the json output into a python script using psycopg2 (which now supports logical replication connections) and update an audit database from there - this would have much lower overhead on the primary database.
from wal2json.
@stevenwinfield neither role nor application name are provided by logical replication. Postgres needs to be patched to include such information in the transaction log. What is the use case for having such information?
from wal2json.
Why don't you use pgaudit [1] or a similar tool? pgaudit uses hooks not triggers.
I'm not sure postgres will add user/application name to transaction log for such a narrow use case (read one explanation at [2]). Logical replication, doesn't log SQL commands; it logs data. Commands are assembled by logical decoding plugin (such as wal2json) before streaming data. It means that commands that don't modify data (such as SET and SELECT) are not replicated. I don't know your audit level but it surely won't cover some common cases.
[1] http://pgaudit.org/
[2] https://www.postgresql.org/message-id/CAMsr+YFhe8yBsNCNEAFnY1OvEU6EXjmrGNFxexK1N=pqHLdjwA@mail.gmail.com
from wal2json.
Thanks for the links.
pgaudit looks good but it only logs to files, which we'd need to parse by tailing them in a separate process (since sometimes we need the information from the audit very soon after it has been written), and having that process be able to pick up from where it left off, should it be taken down for any reason, is cumbersome.
If it were possible using logical replication then - since the master keeps track of the progress of the clients for each slot - we'd be much more robust to the client going down.
I could perhaps insert the user name and application name into the logical decoding stream with pg_logical_emit_message - either in a hook or a lightweight trigger - and then teach the plugin about those messages.
I'll close this issue. Thanks again for your help.
from wal2json.
Related Issues (20)
- Change data not captured properly while operating with partition tables HOT 1
- Getting table name in double Quotes HOT 2
- Segmentation fault HOT 1
- ERROR: could not load library "C:/Program Files/PostgreSQL/9.5/lib/wal2json.dll": The specified module could not be found. HOT 1
- START_REPLICATION command does not work with wal2json options HOT 1
- NaN values are received as "null" for NUMERIC type while capturing change data HOT 4
- Invalid JSON with non-transactional message HOT 5
- WAL record received in different formats when the table name contains the single quote HOT 1
- Add support for update_replication_progress introduced in pg15
- Install failed on Alpine 15 HOT 2
- ProgramLimitExceeded plugin wal2json HOT 2
- include-pk can't work with identity full? HOT 2
- Official instructions to build from source for production use HOT 1
- Change data not captured HOT 3
- LSN not picking from "withStartPosition" in format version-2 HOT 2
- Building dll for windows HOT 4
- Build and Install In RDS
- wal2json_16 is not available in CentOS7 HOT 1
- Unable to compile wal2json on Mac M1 HOT 1
- Installcheck in a loop eventually fails
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from wal2json.