Advanced Track
Database Security: Roles, Privileges & Row-Level 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.
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
GRANT, CREATE POLICY and pgcrypto are server features (most online sandboxes log you in as the owner, who bypasses RLS). Copy snippets into a free db-fiddle.com Postgres fiddle to experiment. Every example below states the expected outcome so you can check yourself. 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.
Result:
| id | name | department | hire_date | salary | |
|---|---|---|---|---|---|
| 1 | Ada Lovelace | Engineering | 2021-03-01 | ada@corp.com | 120000 |
| 2 | Grace Hopper | Engineering | 2019-07-14 | grace@corp.com | 135000 |
| 3 | Alan Turing | Research | 2022-01-09 | alan@corp.com | 110000 |
| 4 | Katherine Johnson | Finance | 2020-11-23 | kat@corp.com | 98000 |
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.
🔑 Real-world analogy
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 & users
Create roles, a login user, and GRANT a role to that user.
-- 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
...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.
USAGE on the schema before any table grant inside it works. Granting SELECT on a table while forgetting schema USAGE is a classic "why can't they see anything?" bug.GRANT & REVOKE
Give a role table privileges, then take one back.
-- 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:
RE
...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 SELECT
Allow only specific columns; salary stays hidden.
-- 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:
...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.
Result — app_readonly running SELECT name, department FROM employees — allowed:
| name | department |
|---|---|
| Ada Lovelace | Engineering |
| Grace Hopper | Engineering |
| Alan Turing | Research |
| Katherine Johnson | Finance |
Result — app_readonly running SELECT name, salary FROM employees — refused:
| name | salary |
|---|---|
| ERROR: permission denied for column salary | — |
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: GRANT SELECT (…)
Replace the ___ blanks with the two allowed column names.
-- 🎯 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 f
...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: tenant isolation
Enable RLS and add a USING/WITH CHECK policy.
-- 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 -- SELE
...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.
Result — after SET app.tenant_id = '7':
| id | tenant_id | amount |
|---|---|---|
| 101 | 7 | 250 |
| 104 | 7 | 80 |
Result — after SET app.tenant_id = '9' — same query, different rows:
| id | tenant_id | amount |
|---|---|---|
| 102 | 9 | 500 |
| 108 | 9 | 42.5 |
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: CREATE POLICY … USING (…)
Limit rows to the current customer.
-- 🎯 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'
...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 & password hashing
Hash passwords, plus notes on at-rest and in-transit encryption.
-- 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 = cr
...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, andREVOKE ... FROM PUBLICon 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 singleSELECTby an attacker shouldn't hand them the keys to everything.
📘 Quick Reference
| Syntax | Purpose |
|---|---|
| CREATE ROLE name | Make a reusable bundle of privileges |
| CREATE USER name WITH PASSWORD '…' | A role that can log in |
| GRANT role TO user | Give a login account that role's privileges |
| GRANT SELECT, INSERT ON t TO role | Object privileges on a table |
| GRANT SELECT (col1, col2) ON t TO role | Allow only specific columns |
| REVOKE priv ON t FROM role | Take a privilege back |
| ALTER TABLE t ENABLE ROW LEVEL SECURITY | Turn on per-row filtering |
| CREATE POLICY p ON t USING (cond) | Which rows a role may read |
| WITH CHECK (cond) | Which rows a role may write |
| ALTER TABLE t FORCE ROW LEVEL SECURITY | Apply RLS to the owner too |
| crypt(pw, gen_salt('bf', 12)) | Hash a password (bcrypt) |
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 role with column-limited reads plus a region RLS policy.
-- 🎯 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.reg
...🎉 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, includingSELECT (col1, col2) - ✅ Row-Level Security:
ENABLE ROW LEVEL SECURITY+ aCREATE POLICY ... USING/WITH CHECKfilters 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
Sign up for free to track which lessons you've completed and get learning reminders.