Postgres 11 partitioning
PostgreSQL supported table partitioning implementation based on inheritance and triggers for over more than a decade now. However, the declarative partition support was added in Postgres 10 release in Oct 2017. Since Postgres 10, Postgres supports built-in declarative partitioning so it was easier to create partitions but you still need to manage trigger to update […]
Audit logging with Postgres partitioning
As I mentioned in my previous post “Audit logging using JSONB in Postgres” , audit tables can be partitioned easily in Postgres 10. Let’s use Postgres partitioning in sample users_audit table… Drop existing trigger on live table and users_audit table drop trigger users_audit_trig ON public.users; drop table if exists audit.users_audit; Create partition table by RANGE partition […]
Audit logging using JSONB in Postgres
Recently, someone reached out to me asking “what’s the best way to achieve database DML auditing in Postgres?” I have suggested a couple of options below to achieve DML auditing in Postgres: Audit using Postgres logs. Postgres allows to keep track of DML statements at database level by enabling auditing directly in the postgres logs. You can use https://github.com/pgaudit/pgaudit extension […]
Connecting Postgres to Active Directory for Authentication
I have connected our Postgres instance to Active Directory for authentication because I didn’t want to manage passwords for hundreds for users accessing mission critical databases. The authentication is being performed by Active Directory but authorization (managing groups, permissions at table/column/row level) is still be handled by Postgres. The biggest advantage of connecting Postgres to AD for real users, […]