Security: Roles, Privileges, and Injection Prevention

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))
)

Security: Roles, Privileges, and Injection Prevention

Don't connect as the superuser

Roles and grants

Row-Level Security

SQL injection — solved problem

Code

External links

Exercise

Progress

Comments 0