Why should you care?

Swiggy mobile app never connects directly to postgres — API tier enforces auth, rate limits, and maps DTOs to SQL.

See it live — copy this example

Run in pgAdmin or psql.

-- Role for API read-only access
CREATE ROLE api_reader NOLOGIN;
GRANT CONNECT ON DATABASE "PostgresVerse" TO api_reader;
GRANT USAGE ON SCHEMA public TO api_reader;
GRANT SELECT ON orders, products TO api_reader;

SET ROLE api_reader;
SELECT order_id, status FROM orders LIMIT 3;

What happened?

• api_reader can SELECT specific tables only.
• SET ROLE tests least privilege.
• Real API uses pooled connection with this role, not superuser.

Practice next

1. CREATE ROLE api_reader and GRANT as shown.
2. SET ROLE api_reader; run SELECT and INSERT (should fail).
3. RESET ROLE; confirm superuser access returns.
4. CREATE ROLE api_writer GRANT INSERT,UPDATE on orders only.
5. Enable RLS on orders and attach tenant policy for API role.

Remember

API sits above database with scoped roles. Prepared statements reduce parse overhead. RLS enforces tenant even if API bug leaks id.

PostgresVerse order API

Node service pools 20 connections as api_reader; write path uses api_writer with RLS.

Outcome: Pen test SQLi cannot DROP DATABASE.