Security: Roles, Privileges, and Injection Prevention
~14 min · apps, security
Level 0Schema Seedling
0 XP0/86 lessons0/10 achievements
0/120 XP to next level120 XP to go0% complete
Don't connect as the superuser
The default postgres user is the superuser — bypasses all permission checks, can drop the database. Production applications must connect as a least-privileged role with only the privileges they need: usually SELECT, INSERT, UPDATE, DELETE on the application tables, nothing more.
Roles and grants
Create an application role (app) and grant it the specific privileges. Use GRANT USAGE ON SCHEMA + GRANT SELECT, INSERT ON TABLE. ALTER DEFAULT PRIVILEGES automatically grants the same privileges on tables you create later.
Row-Level Security
RLS lets you push multi-tenant rules into the database itself: "doctors only see their own patients," "users only see their own posts." A policy is attached to a table; every query against that table has the policy applied transparently. Supabase's auth story is built almost entirely on RLS.
SQL injection — solved problem
Parameterised queries (driver-level) make injection structurally impossible. Use them everywhere. Never concatenate user input into SQL. Never trust client-supplied identifier names; if you must dynamically pick a column, validate against an allowlist and use quote_ident.
Code
Application role with least privilege·sql
-- Create the role used by the app
CREATE ROLE app LOGIN PASSWORD '****';
-- Schema usage
GRANT USAGE ON SCHEMA public TO app;
-- Table privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app;
-- Future tables get the same grants automatically
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app;
Row-Level Security policy·sql
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
CREATE POLICY posts_owner_only
ON posts
FOR ALL
USING (author_id = current_setting('app.current_user_id')::int);
-- The application sets the user id at the start of each request
SET LOCAL app.current_user_id = '7';
SELECT * FROM posts; -- only returns rows where author_id = 7
Parameterised queries — the SQL injection cure·python
# The driver guarantees no escaping mistakes
cur.execute("SELECT * FROM posts WHERE author_id = %s AND status = %s", (user_id, status))
# Dynamic identifier (rare): allowlist + quote_ident
ALLOWED_COLUMNS = {'created_at', 'updated_at', 'title'}
if sort_col not in ALLOWED_COLUMNS:
raise ValueError("invalid sort column")
cur.execute(
sql.SQL("SELECT * FROM posts ORDER BY {} DESC").format(sql.Identifier(sort_col))
)
In a sandbox, create an app role with only INSERT/SELECT on a single table. Connect as that role. Try to DROP TABLE — confirm it fails. Then add an RLS policy that filters rows by a session variable; verify it works.
Progress
Progress is local-only — sign in to sync across devices.