Untangling the Web of PostgreSQL Permissions
summary
States that create user/group are aliases for create role and roles are cluster-wide not per-database, so use pg_dumpall -g to export them; shows psql metacommands (\ddp, \z, \dx) and suggests a recursive CTE to audit inherited grants; explains Postgres 15 change so post-15 public schema locked down and the tricky default-privileges behavior (defaults affect only the current role); warns that row-level security works but adds a measurable performance hit; recommends separation: use separate users for replication, monitoring, backups.
AI-written summary · about
chapters (10)
- 0:00Introduction and speaker intro
- 4:26PostgreSQL permissions overview
- 10:04Roles vs users vs groups
- 13:15Role attributes and privileges
- 17:34Public schema permissions
- 23:14Permission inheritance explained
- 30:55Predefined roles in PostgreSQL
- 33:24Row-level security overview
- 38:42Best practices for permissions
- 43:19Q&A session
related talks
27mFor Your Eyes Only: Roles, Privileges, and Security in PostgreSQL
42mData Security using Roles, Privileges and Row Level Security
40mPostgreSQL Security for Application Developers
28mPostgreSQL vs. SQL Server: Security Model Differences
32mEnhancing Security in PostgreSQL
07mAll about Postgres security
50mPostgreSQL Security. How Do We Think?
24mSecure Your Web Webapp passwords in PostgreSQL