1---
2title: Apply Principle of Least Privilege
3impact: MEDIUM
4impactDescription: Reduced attack surface, better audit trail
5tags: privileges, security, roles, permissions
6---
7 
8## Apply Principle of Least Privilege
9 
10Grant only the minimum permissions required. Never use superuser for application queries.
11 
12**Incorrect (overly broad permissions):**
13 
14```sql
15-- Application uses superuser connection
16-- Or grants ALL to application role
17grant all privileges on all tables in schema public to app_user;
18grant all privileges on all sequences in schema public to app_user;
19 
20-- Any SQL injection becomes catastrophic
21-- drop table users; cascades to everything
22```
23 
24**Correct (minimal, specific grants):**
25 
26```sql
27-- Create role with no default privileges
28create role app_readonly nologin;
29 
30-- Grant only SELECT on specific tables
31grant usage on schema public to app_readonly;
32grant select on public.products, public.categories to app_readonly;
33 
34-- Create role for writes with limited scope
35create role app_writer nologin;
36grant usage on schema public to app_writer;
37grant select, insert, update on public.orders to app_writer;
38grant usage on sequence orders_id_seq to app_writer;
39-- No DELETE permission
40 
41-- Login role inherits from these
42create role app_user login password 'xxx';
43grant app_writer to app_user;
44```
45 
46Revoke public defaults:
47 
48```sql
49-- Revoke default public access
50revoke all on schema public from public;
51revoke all on all tables in schema public from public;
52```
53 
54Reference: [Roles and Privileges](https://supabase.com/blog/postgres-roles-and-privileges)
55