Apply Least Privilege to DB Users

Picture this: your application's database credentials are compromised—maybe a leaked .env file, a SQL injection slip, or an insider threat. If that database user has full SUPERUSER or GRANT ALL privileges, the attacker doesn't just read one table; they can DROP the entire schema, exfiltrate every customer record, or pivot to other systems. This is the nightmare scenario that the principle of least privilege directly prevents: giving each database user only the exact permissions they need to do their job—nothing more, nothing less. By the end of this lesson, you'll know how to audit and apply least privilege to your database users, turning a potential catastrophe into a contained incident.

The Problem This Lesson Solves

Most developers start with a single, all-powerful database user—often the root or postgres superuser—simply because it's the default and it's easy. But this convenience creates a single point of failure. If that one set of credentials is compromised, the blast radius is your entire database. Even in less dramatic scenarios, overprivileged users can accidentally corrupt data, run destructive queries, or violate compliance standards like GDPR or PCI-DSS.

The cost isn't just security; it's also operational. When a developer or service has too many privileges, it becomes harder to audit who did what, and mistakes become more likely. Do you really want your reporting tool to be able to DELETE FROM orders? Absolutely not. The principle of least privilege addresses this by making every access explicit, minimal, and revocable.

Pro tip: The principle of least privilege isn't just about security—it's also about defense in depth. Even if your app has a SQL injection vulnerability, a least-privilege database user limits the damage to what that user can do, acting as a safety net.

Core Concept / Mental Model

Think of least privilege like a key card system in an office building. A junior accountant gets a card that opens only the accounting office and the break room—not the server room, not the CEO's office. If their card is stolen, the thief's access is limited to those areas, and the rest of the building is safe. Similarly, a database user should have a credential that unlocks only the specific tables, columns, or actions (SELECT, INSERT, UPDATE, DELETE) that the corresponding application role requires.

To apply this, you need to understand two key concepts:

• Users: Database accounts that authenticate (e.g., app_read, app_write, backup_user).
• Privileges: Specific actions allowed on specific objects (e.g., SELECT on public.customers).

In SQL databases, you typically grant privileges using GRANT and revoke them using REVOKE. The goal is to define distinct roles—like a read-only reporting role or a write-only application role—and assign them to users, rather than giving everyone a full ALL or SUPERUSER role.

Here's a simple mental model: Every database user should have the minimum privileges required to perform their function. More privileges than necessary equals more risk. You can think of it as a "need-to-know" basis for data.

How It Works Step by Step

Applying least privilege isn't a single action; it's a process. Here's the step-by-step approach:

1. Inventory your database users. List all users and their current privileges. Run queries like \du in PostgreSQL or SHOW GRANTS in MySQL.
2. Identify the role of each user. What application or service does this user represent? Typical roles: - Read-only reporting: Needs SELECT on specific tables or views. - Application write: Needs INSERT, UPDATE, DELETE on certain tables, but not DROP or ALTER. - Schema migrations: Needs CREATE, ALTER, DROP on schema objects, but only during deploy times. - Backup: Needs read access to all data, but no write.
3. Create a new user or modify existing ones with a role that matches the job. Avoid using the superuser for application connections.
4. Revoke unnecessary privileges. Start with the principle that everything is denied by default, then grant only what's needed.
5. Review and audit regularly. Privilege creep happens over time as requirements change. Schedule periodic audits.

Pro tip: Use database roles (groups) to manage privileges instead of granting directly to users. That way, when a user changes roles (e.g., from intern to admin), you just change their assignment to a different role, not a bunch of individual grants.

Hands-On Walkthrough

Let's put this into practice with a concrete example using PostgreSQL. We'll create a read-only user for a reporting application and a write user for an app that manages user profiles.

Step 1: Connect as a superuser

psql -U postgres -d myapp

Step 2: Create roles (groups) and users

-- Create a read-only role
CREATE ROLE report_read;
GRANT CONNECT ON DATABASE myapp TO report_read;
GRANT USAGE ON SCHEMA public TO report_read;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_read;

-- Create a write role (for app that only touches user_profiles)
CREATE ROLE app_write;
GRANT CONNECT ON DATABASE myapp TO app_write;
GRANT USAGE ON SCHEMA public TO app_write;
GRANT SELECT, INSERT, UPDATE, DELETE ON user_profiles TO app_write;

Step 3: Create users and assign roles

-- Create users
CREATE USER reporting_user WITH PASSWORD 'strong_password';
CREATE USER app_user WITH PASSWORD 'different_strong_password';

-- Assign roles
GRANT report_read TO reporting_user;
GRANT app_write TO app_user;

Step 4: Test the privileges

SET ROLE reporting_user;
SELECT * FROM orders;  -- This works (if you have orders table)
INSERT INTO orders (id) VALUES (1);  -- This fails: permission denied

RESET ROLE;
SET ROLE app_user;
INSERT INTO user_profiles (name) VALUES ('Alice');  -- This works
SELECT * FROM orders;  -- This fails: permission denied for table orders

Expected output: The read-only user can SELECT but cannot INSERT (error: permission denied for table orders). The write user can modify user_profiles but cannot even read other tables.

Step 5: Create views for restricted data

Sometimes you want to expose limited data to a read-only role. Instead of granting SELECT on the whole table, create a view and grant access to that view only.

CREATE VIEW public_orders AS SELECT id, total FROM orders;
GRANT SELECT ON public_orders TO report_read;

Now reporting_user can query public_orders but not orders directly.

Compare Options / When to Choose What

Different databases have different ways to implement least privilege. Here's a quick comparison:

When to choose what:

• If you have a simple app with one database, using a read-only user for reporting and a write user for the app is sufficient.
• If you have multiple apps sharing a database, use database schemas to isolate them, and grant privileges per schema.
• If you need fine-grained control over rows, use row-level security (PostgreSQL) or dynamic data masking (SQL Server).

Pro tip: Always prefer deny by default. In PostgreSQL, you can set REVOKE ALL ON SCHEMA public FROM PUBLIC; to ensure new users don't automatically get access to everything.

Troubleshooting & Edge Cases

• "Permission denied for schema public" — This happens when the user lacks USAGE on the schema. Fix: GRANT USAGE ON SCHEMA public TO role;.
• "Permission denied for table" — The user has schema access but not table access. Grant the specific table privilege.
• "Must be owner of table" — Some operations (like TRUNCATE) require ownership. Avoid granting TRUNCATE to regular users—use a separate maintenance role.
• Privilege creep after schema changes — If you add a new table, your role won't automatically have privileges on it. Use ALTER DEFAULT PRIVILEGES to auto-grant to future objects.

-- Set default privileges for tables created by 'admin' to grant SELECT to 'report_read'
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO report_read;

• What about GRANT ALL? Avoid it unless absolutely necessary. GRANT ALL includes TRUNCATE, REFERENCES, TRIGGER, which are rarely needed.
• Using a superuser for application connection — This is the #1 mistake. Never do it.

What You Learned & What's Next

You've learned how to apply the principle of least privilege to database users: the core concept of granting only the minimum needed permissions, the step-by-step process of creating roles and users, and how to troubleshoot common issues. You can now:

• Explain why least privilege is critical for security.
• Create and manage database roles/users with minimal privileges.
• Use views and schema-level grants to further restrict access.

Next step: In the next lesson, we'll explore Defense in depth: beyond least privilege, where you'll learn how to layer additional security controls like network isolation, encryption, and monitoring to protect your database further.

Pro tip: Don't wait for a breach—conduct a privilege audit today. Use this SQL snippet to list all users and their privileges:

SELECT grantee, privilege_type, table_name
FROM information_schema.role_table_grants;