Protecting your PostgreSQL data assets: one size does not fit all. Meet Security Framework! - Henrietta Dombrovskaya, DevOpsDays Chicago 2025

In an age where data breaches and unauthorized access are constant threats, safeguarding sensitive information is vital for organizations. With its robust security features, PostgreSQL offers a powerful toolset unmatched by any other commercial or open-source database. However, this extraordinary flexibility often feels overwhelming and turns permission management into a complicated task that is difficult to automate. Unsure of how to proceed, application developers resort to isolating data using a separate Postgres instance for each new project, thereby increasing the overall costs.

Transcript

1. Hettie Dombrovskaya Database Architect Chicago 2025 Securing Your PostgreSQL Data: One Size Doesn’t Fit All: Meet Security Framework!
2. 2 Who Am I Hettie Dombrovskaya • Database Architect at DRW • Local Organizer of Chicago PostgreSQL User Group • PG Day Chicago Operations Committee (PGDay is on April 25!) • Prairie Postgres NFP President (Student PG DATA Day is on April 24!)
3. 3 Why this talk? - We live in an age of data breaches - Securing data is a high priority - PostgreSQL has everything - Still…
4. 4 Instance Database Schema Tables Roles Users
5. 5 Challenge #1: PostgreSQL does not force you to create roles and schemas And all examples in documentation create objects in PUBLIC schema! - Applications are developed using postgres user - When they move to production, developer either forget to change the user or run into permissions problems they do not have time do not know how to fix and they stay that way! As a result…
6. 6 Challenge #2: The wonders of inheritance! Starting from PG 7.3, there is no distinction between users and roles (user=role+login) create role role1; create role role2 login password ‘pwd1’; create user user1 password ‘pwd2’; All of the grants below will work: grant role1 to role2; grant role2 TO user1; grant user1 to role2; … and if later you will execute create role role3; grant role3 to role1 ---will be inherited upstream!
7. 7 Challenge #3: The wonders of ownership! create schema orders owner orders_owner; It created a lot of privileges for orders_owner user: grant all on schema orders to orders_owner However, it does not mean that all objects in this schema will be automatically owned by the same owner! Moreover, when you execute alter schema orders owner new_orders_owner; There will be no changes in permissions!
8. 8 Challenges #4, #5, #6… Lots of weird things! grant select on orders.sales_points to read_role; grant insert, update, delete on orders.sales_points to write_role; grant read_role to api_user; grant write_role to api_user; revoke delete on orders.sales_points from api_user; Will this work?
9. 9 - It won’t, and moreover, errors won’t be reported: REVOKE of permissions which are not granted GRANT permissions which are already granted except for roles - You can’t drop user that has any privileges - You can’t drop role cascade …And there is no easy way to see what permissions a given user has!
10. 10 Now imagine you have not five, not ten, but 300 databases …
11. 11 Security Models Overview Principles and implementation
12. 12 • A user is given the minimum levels of access needed to perform their job functions. Principle of least privilege Basic principles The only security model to support multi- tenancy within one PostgreSQL database • Non-superuser users do not have a way to bypass security settings Durability • One package supports four security models with different permissions hierarchy. Flexibility
13. 13 Key features Event trigger • Schemas and roles creation/deletion are performed using security definer functions Security-definer functions • Schema owner TRUE/FALSE • Account owner TRUE/FALSE Security levels matrix • Forces all objects in each schema to be owned by the schema owner role and assigns default privileges • Security modal is set up on the database level Database level security
14. 14 Four Models Single owner Schema owner Account owner Account and schema owner
15. 15 Single owner
16. 16 Schema owner
17. 17 Account owner
18. 18 Account and schema owner
19. 19 What is there for the users? • No need for a new database when you start a new project • Create new schemas • Create new users • Assign and revoke users’ privileges • Change users’ passwords You are in control!
20. 20 Where to find me LinkedIn: https://www.linkedin.com/in/henrietta- dombrovskaya-367b26/ GitHub: https://github.com/hettie-d PG Day Chicago 2025 2025.pgdaychicago.org Prairie Postgres prairiepostgres.org