PostgreSQL roles and permissions: CREATE ROLE, GRANT, REVOKE and least privilege

Set up least privilege access in PostgreSQL: create a read-only role and a writer role for your app, grant exactly what each needs, and see the database refuse everything else. The video explains what a role is, why PostgreSQL only knows the role your app connects with (not your app's users), and why an app should never connect as a superuser if it gets compromised. In a shop database you create app_readonly and app_writer with CREATE ROLE ... WITH LOGIN, list roles with \du, and grant permissions in three layers: CONNECT on the database, USAGE on the public schema, and SELECT or SELECT, INSERT, UPDATE on all tables, plus USAGE on sequences so SERIAL inserts work. Logging in as each role shows inserts, deletes and DROP TABLE being denied. Finally you REVOKE a permission, grant it back, and use ALTER DEFAULT PRIVILEGES so new tables get the same grants. 0:00 Who should be allowed to delete your database? 0:50 What a role is 1:22 The key card analogy 2:16 Why your app should not be a superuser 3:26 Lab setup 4:27 The shop database 5:02 CREATE ROLE with LOGIN 6:09 GRANT CONNECT, USAGE and SELECT 7:54 Grants for the writer role and sequences 9:38 Logging in as the read-only role 11:35 Testing the writer role 13:17 REVOKE 14:25 ALTER DEFAULT PRIVILEGES Practice PostgreSQL roles and permissions hands-on on prepare.sh: https://prepare.sh