Database Security

Reviewed & published by Brayan K

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

• Apply the principle of least privilege
• Create roles and users, and GRANT roles to users
• Grant and REVOKE object privileges on tables
• Restrict access to individual columns
• Filter rows per user/tenant with Row-Level Security
• Know where encryption at rest and in transit fits

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.

-- Roles group privileges; users are roles that can log in.
-- Goal: build a "read-only" badge and a "read-write" badge.

-- 1) Create two roles (no login on their own — they are just permission sets)
CREATE ROLE app_readonly;
CREATE ROLE app_readwrite;

-- 2) Create a real login account for your application
CREATE USER api_service WITH PASSWORD 'a_long_random_secret';

-- 3) Hand the read-write badge to that account
GRANT app_readwrite TO api_service;   -- api_service now inherits app_readwrite

-- 4) Roles can inherit other roles (least privilege as a hierarchy)
GRANT app_readonly TO app_readwrite;  -- read-write also gets everything read-only has

-- The app logs in as api_service, NOT as a superuser.
-- If that one account leaks, the blast radius is only what app_readwrite can do.

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.

-- Object privileges decide WHAT a role may do to WHICH table.
-- The role must also be allowed into the schema first.

GRANT USAGE ON SCHEMA public TO app_readonly;          -- "you may enter this schema"

-- Read-only role may only look, never change:
GRANT SELECT ON employees TO app_readonly;             -- read the employees table

-- Read-write role may look AND change rows:
GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO app_readwrite;

-- REVOKE takes a privilege back at any time:
REVOKE DELETE ON employees FROM app_readwrite;         -- now it cannot delete rows

-- Without an explicit GRANT, a role has NO access — deny by default.

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.

-- Column-level privileges hide sensitive columns, not whole tables.
-- Goal: let app_readonly read names and departments, but NEVER salary.

-- Start clean: remove any table-wide SELECT it might have
REVOKE SELECT ON employees FROM app_readonly;

-- Grant SELECT on SPECIFIC columns only:
GRANT SELECT (id, name, department, hire_date) ON employees TO app_readonly;

-- Now this works for app_readonly:
SELECT id, name, department FROM employees;   -- ✅ allowed columns

-- ...but this is rejected:
SELECT id, name, salary FROM employees;
-- ❌ ERROR: permission denied for column salary

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.

-- 🎯 YOUR TURN — fill in the two blanks, then press "Try it Yourself"
-- Goal: let app_readonly read only the name and the department columns
--       of employees (so salary and email stay hidden).

GRANT SELECT (___, ___) ON employees TO app_readonly;
-- 👉 put the two allowed column names inside the parentheses: name, department

-- ✅ Expected: app_readonly can run
--    SELECT name, department FROM employees;   -- works
--    but SELECT salary FROM employees;          -- permission denied for column salary

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.

-- Row-Level Security (RLS) filters ROWS per user, automatically.
-- Goal: in a shared multi-tenant table, each tenant sees only its own rows.

-- 1) Turn RLS on for the table (with no policy yet, the table returns 0 rows)
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

-- 2) Write a policy: a row is visible only when its tenant_id matches
--    the tenant id stored in the session.
CREATE POLICY tenant_isolation ON invoices
    FOR ALL                                                     -- SELECT/INSERT/UPDATE/DELETE
    USING (tenant_id = current_setting('app.tenant_id')::int)  -- which rows you can READ
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::int); -- which rows you may WRITE

-- 3) The app sets the tenant for this connection, then queries normally:
SET app.tenant_id = '7';
SELECT * FROM invoices;   -- the database silently adds: WHERE tenant_id = 7

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.

-- 🎯 YOUR TURN — fill in the blank to finish the policy.
-- Goal: each customer may see only their OWN orders. The current customer's
--       id is in the session value 'app.current_user_id'.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY own_orders ON orders
    FOR SELECT
    USING (___);
-- 👉 the row is visible only when customer_id equals the session user id:
--    customer_id = current_setting('app.current_user_id')::int

-- ✅ Expected: after SET app.current_user_id = '42';
--    SELECT * FROM orders;  returns ONLY rows where customer_id = 42

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.

-- Encryption protects data the access rules can't.
-- "At rest" = on disk; "in transit" = over the network; plus per-column secrets.

-- 1) NEVER store passwords in plain text — store a salted hash:
INSERT INTO users (username, password_hash)
VALUES ('alice', crypt('user_password', gen_salt('bf', 12)));
-- bf = bcrypt; 12 = cost factor (higher is slower to crack).
-- Verify later by re-hashing the input with the stored salt:
SELECT id FROM users
WHERE username = 'alice'
  AND password_hash = crypt('user_password', password_hash);

-- 2) Encryption AT REST: the database files on disk are encrypted by the
--    server/cloud (e.g. AWS RDS, Azure SQL, GCP Cloud SQL). It's a config
--    setting, not SQL you write per query — a stolen disk reveals nothing.

-- 3) Encryption IN TRANSIT: require TLS so traffic can't be sniffed:
--    ALTER USER api_service SET sslmode = 'require';

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.

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.

Q: Is hashing the same as encryption?

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.

-- 🎯 MINI-CHALLENGE
-- A reporting role needs to read customers, but must NOT see card numbers,
-- and must only ever see rows for its own region.
--
-- Using ONLY what this lesson covered:
--   1. Create a role called report_reader
--   2. Let it into schema public, then GRANT SELECT on just
--      (id, name, region) of the customers table — card_number stays hidden
--   3. Enable RLS on customers and add a policy so report_reader sees only
--      rows where region = current_setting('app.region')
--
-- ✅ Expected: report_reader can read id/name/region, is denied card_number,
--    and after SET app.region = 'EU'; sees only EU customers.

-- your SQL here

🎉 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: Arrays, HSTORE, Geography, UUID
• Next: SQL Injection Defenses & Secure Query Practices — Prevent SQL injection with parameterised queries and input validation
• Quick reference: SQL cheat sheet

Related lessons

• SELECT