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.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

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.

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.

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.

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.

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.

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.

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.

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.

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

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:

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.

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.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary