SQL Permissions & Roles — GRANT, REVOKE, and Least Privilege

Why database permissions matter

Application code bugs are inevitable. A logic error, a missing WHERE clause, or an unintended admin route can trigger unintended writes or reads. Database permissions are the last line of defence — they limit what damage a bug or compromised connection can do. The principle is least privilege: each database user gets only the permissions needed for its specific role, nothing more.

GRANT and REVOKE

GRANT gives a privilege; REVOKE removes it.

-- Grant SELECT on one table
GRANT SELECT ON orders TO reporting_role;

-- Grant multiple privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON orders    TO app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON customers TO app_role;
GRANT SELECT                         ON products  TO app_role;

-- Grant on all current tables in a schema (Postgres)
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;

-- Revoke a privilege
REVOKE INSERT ON orders FROM app_role;
REVOKE ALL PRIVILEGES ON customers FROM analyst_user;

Grant to roles, not directly to users. Then assign users to roles — you can update access patterns by changing role memberships rather than rerunning dozens of GRANT statements.

Designing roles for a typical web application

-- Three roles matching the three access patterns
CREATE ROLE readonly_role;    -- dashboards, reporting, read-only API
CREATE ROLE app_role;         -- the web application's runtime connection
CREATE ROLE migration_role;   -- only used during deployments

-- readonly_role: read-only access to public schema
GRANT USAGE ON SCHEMA public TO readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly_role;  -- also covers future tables

-- app_role: read-write on specific tables, no DDL
GRANT USAGE ON SCHEMA public TO app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON customers TO app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders     TO app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON order_items TO app_role;
GRANT SELECT ON products TO app_role;  -- read-only on catalogue

-- migration_role: can create and alter tables (only used during deploys)
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO migration_role;
GRANT CREATE ON SCHEMA public TO migration_role;

-- Users
CREATE ROLE api_service  LOGIN PASSWORD '...' NOINHERIT;
CREATE ROLE report_user  LOGIN PASSWORD '...' NOINHERIT;

GRANT app_role      TO api_service;
GRANT readonly_role TO report_user;

Default privileges — covering future tables

Without ALTER DEFAULT PRIVILEGES, a new table added in a migration is not visible to existing roles — their SELECT grant covered only tables that existed at grant time.

-- Ensure all future tables in the schema are accessible to app_role
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_role;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO app_role;

Schema-level access

In Postgres, a user must have USAGE on the schema before accessing any object within it — even if they have SELECT on the table.

-- Department-level schema separation
CREATE SCHEMA finance;
CREATE SCHEMA hr;

GRANT USAGE ON SCHEMA finance TO finance_role;
GRANT SELECT ON ALL TABLES IN SCHEMA finance TO finance_role;
-- finance_role cannot see the hr schema at all

Row-level security (RLS) — multi-tenant isolation

For multi-tenant applications, RLS ensures tenants cannot see each other's data even if the application has a query bug.

-- Enable RLS on the orders table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Policy: the app sets a config variable; the policy filters rows by it
CREATE POLICY tenant_isolation ON orders
    FOR ALL TO app_role
    USING (tenant_id = current_setting('app.tenant_id')::INT);

-- Application sets the tenant before every query
SET app.tenant_id = '42';
SELECT * FROM orders;  -- automatically returns only tenant 42's orders

Audit: inspect current permissions

-- Postgres: what can a role do on a table?
SELECT grantee, privilege_type
FROM   information_schema.role_table_grants
WHERE  table_name = 'orders'
ORDER  BY grantee, privilege_type;

-- psql shorthand
-- \dp orders           → show ACL for the orders table
-- \du api_service      → show role memberships and attributes

-- MySQL
SHOW GRANTS FOR 'api_service'@'%';

-- SQL Server
SELECT permission_name, state_desc
FROM   sys.database_permissions dp
JOIN   sys.database_principals  pr ON dp.grantee_principal_id = pr.principal_id
WHERE  pr.name = 'api_service';

Never use a superuser for application connections

-- BAD: application connects as postgres / root / sa
-- postgresql://postgres:password@localhost/myapp
-- A SQL injection in any query can now DROP TABLE, read pg_shadow, run shell commands

-- GOOD: a minimal-privilege role with only what the app needs
-- postgresql://api_service:password@localhost/myapp

-- Verify the connection user
SELECT current_user, session_user;
-- Should return: api_service | api_service (not postgres)

Recap

Grant privileges to roles, not users — users inherit them by role membership. Define ALTER DEFAULT PRIVILEGES so future tables are automatically covered. Require USAGE on schemas as an additional access layer. Use RLS for multi-tenant isolation at the database level. Never use a superuser for application connections — a least-privilege service account limits the blast radius of both bugs and SQL injection attacks.

More ways to practice