Partial data dumps using Postgres Row Level Security

Summary

Dumping full production databases onto local development machines creates severe security issues once applications hold real user data. To safely generate local seed data, developers can leverage PostgreSQL Row Level Security to restrict data access. The process involves provisioning a dedicated database user with select privileges on the target schema and tables. Next, administrators enable row-level security policies on those tables to filter records using criteria such as specific primary keys, email domain patterns, date intervals, or boolean flags. Finally, running pg_dump with the restricted user credentials and the --enable-row-security flag produces a sanitized seed.sql file containing only approved records.

Context

Dumping entire production databases to local development machines introduces security and privacy risks once real user data is present, yet developers still require subset seed data for testing.

Approach / What changed

Create a dedicated PostgreSQL user with limited permissions, configure Row Level Security (RLS) policies on target tables to restrict selectable rows, and execute pg_dump with the --enable-row-security flag.

Takeaways

• Exporting partial data via Row Level Security requires running pg_dump with the dedicated restricted user and the --enable-row-security flag.
• RLS policies for seed data export can filter rows using SQL expressions, including specific IDs, email domain matching with substrings, timestamp intervals, or dedicated boolean export flags.
• Alternative development environment strategies include using tools like Snaplet for fully masked copies or copy-on-write database forking on ZFS via Database Lab Engine.

Related reading