Database Security

By the end of this lesson you'll be able to lock a database down the way professionals do: hand each account only the privileges it needs, hide sensitive columns, filter rows automatically per tenant or user with Row-Level Security, and know where encryption fits. These are the controls auditors look for and the difference between a breach that leaks one customer and one that leaks them all.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

Our Sample Table: employees

Most examples below secure this little employees table. Notice it holds a mix of harmless columns (name, department) and sensitive ones (email, salary) — that mix is exactly why fine-grained access control exists.

1. The Principle of Least Privilege

Database security has one governing idea: least privilege . Every account gets exactly the permissions it needs to do its job, and not one more. A reporting tool that only reads data should be physically unable to DELETE a row — not merely "told not to".

The opposite — handing your app a superuser account "to keep things simple" — is the single most common and most expensive mistake in this lesson. If that account leaks, the attacker can read, change, or drop everything.

Roles are like the keycards in an office building. A cleaner's card opens the front door and supply cupboard; it does not open the server room or the CEO's safe. You don't give every employee the master key just because it's convenient — and you don't give your web app the database master key either.

2. Roles & Users: GRANT a Role to a User

A role is a named bundle of privileges. A user is just a role that's allowed to log in (it has a password). You attach privileges to a role once, then GRANT that role to whichever login accounts should have it — so you manage permissions in one place instead of per person.

Roles can even inherit other roles, which lets you model least privilege as a hierarchy: "read-write" includes everything "read-only" can do, plus more.

3. Object Privileges: GRANT & REVOKE on Tables

Object privileges say what a role may do to a specific object such as a table — SELECT to read, INSERT / UPDATE / DELETE to change. GRANT hands a privilege out; REVOKE takes it back. The default for everything is no access , so a role can only touch what you've explicitly granted.

4. Column-Level Privileges

Sometimes a role should read some columns but not others — names and departments are fine, but salary and email are not. Instead of granting SELECT on the whole table, list the allowed columns: GRANT SELECT (col1, col2) ON table TO role . Any query that touches an un-granted column is rejected.

Here is what the same SELECT name, department FROM employees returns for two different roles. The read-only role is allowed those columns, so it succeeds; asking it for salary is refused outright.

Your Turn: grant just two columns

Fill in the two blanks so app_readonly can read only name and department from employees . The expected outcome is in the comments so you can check yourself.

5. Row-Level Security (RLS)

Column privileges hide columns ; Row-Level Security hides rows . You enable it with ALTER TABLE ... ENABLE ROW LEVEL SECURITY , then write a CREATE POLICY with a USING condition. The database silently appends that condition to every query — so each user automatically sees only the rows that pass it. This is how one shared invoices table can safely serve thousands of tenants.

A policy has two halves. USING (...) decides which existing rows you can read (and update/delete). WITH CHECK (...) decides which rows you're allowed to write — without it, a user could INSERT a row tagged for someone else's tenant.

Once RLS is enabled, a table with no matching policy returns zero rows — RLS fails closed. That's a feature: forget to write a policy and you leak nothing, rather than everything.

With the policy above, the same SELECT * FROM invoices returns different rows depending on the session's app.tenant_id . The query text never changes — the database filters for you.

Your Turn: finish the policy

One blank — write the USING condition so each customer sees only their own orders. The hint and expected outcome are in the comments.

6. Encryption: At Rest & In Transit

Access rules stop the logged-in from seeing too much; encryption stops the uninvited — someone who steals a backup tape or taps the network. There are three layers worth knowing:

• In transit: require TLS/SSL so data crossing the network is unreadable to anyone sniffing it.
• At rest: the database files on disk are encrypted (often called Transparent Data Encryption), so a stolen disk or backup is useless without the key. This is usually a server/cloud setting, not SQL.
• Per-column / application level: hash passwords (never store them in plain text) and encrypt the most sensitive fields so even a valid login can't read them in the clear.

Common Errors (and the fix)

• Granting too much / using a superuser app account: never let your application log in as a superuser or table owner "to avoid permission errors". Create a dedicated least-privilege role and grant it exactly what the app needs. A leaked superuser account is game over.
• Granting to PUBLIC : GRANT SELECT ON employees TO PUBLIC; gives the privilege to every role, present and future. It's almost never what you want — grant to a specific role instead, and REVOKE ... FROM PUBLIC on sensitive tables.
• "permission denied for schema public": you granted table privileges but forgot the schema. Add GRANT USAGE ON SCHEMA public TO the_role; first.
• RLS seems ignored: the table owner (and superusers) bypass RLS by default . If you're testing as the owner you'll see every row. Test as the restricted role, or add ALTER TABLE t FORCE ROW LEVEL SECURITY; so the owner is subject to policies too.
• Storing plaintext secrets: never keep passwords, API keys, or card numbers as plain text. Hash passwords ( crypt() + gen_salt('bf', 12) ) and encrypt other secrets — a single SELECT by an attacker shouldn't hand them the keys to everything.

📘 Quick Reference

Syntax

Purpose

Frequently Asked Questions

Q: What's the difference between a role and a user?

In PostgreSQL they're the same kind of object — a user is just a role created with LOGIN (a password). Conventionally you make permission-only roles like app_readonly and grant them to login accounts, so you manage access in one place.

Q: Why did my RLS policy not filter anything?

You were almost certainly connected as the table owner or a superuser, who bypass RLS by default . Test as the restricted role, or use ALTER TABLE ... FORCE ROW LEVEL SECURITY to subject the owner to policies too.

Q: Should I use column privileges or a masking view?

Column-level GRANT SELECT (…) is the simplest way to hide a column outright. A view is better when you want to transform the data (e.g. show a salary band instead of the exact figure) rather than hide it entirely.

No. Encryption is two-way — with the key you can get the original back, which is right for data you must read again (like an SSN). Hashing is one-way and is correct for passwords: you only ever compare hashes, so the plaintext is never stored.

Mini-Challenge: a locked-down reporting role

Put it all together — a brief, a blank canvas, and the expected outcome in the comments. Write it, then run it against a Postgres instance to confirm.

🎉 Lesson Complete

• ✅ Least privilege: every account gets only what it needs — never a superuser app login
• ✅ Roles & users: bundle privileges in a role, then GRANT role TO user
• ✅ Object & column privileges: GRANT / REVOKE , including SELECT (col1, col2)
• ✅ Row-Level Security: ENABLE ROW LEVEL SECURITY + a CREATE POLICY ... USING/WITH CHECK filters rows automatically
• ✅ Encryption: hash passwords, and know where at-rest and in-transit encryption fit
• ✅ Next: defend against SQL Injection — the attack that bypasses all of this if your queries are built carelessly

Practice quiz

Continue this course

• Previous: Advanced Data Types
• Next: SQL Injection Defenses

Related lessons

• SELECT