SQL (PostgreSQL, MySQL, SQLite) Module 17 – Security: injection, privileges and data protection
Users, roles and least privilege
Create PostgreSQL roles that hold only what an app needs, split owner, migration and app accounts, and check who can do what in each engine.
What you will learn
- Create roles and grant only the privileges an application needs
- Separate owner, migration and application roles
- Check who can do what with privilege functions and catalog queries
- Limit a program's access to an SQLite file, which has no users
- Rotate a login's password without downtime and cap what one role may use
Before you start
What you’ll learn · Before you start
What you will learn
- Create roles and grant only the privileges an application needs
- Separate owner, migration and application roles
- Check who can do what with privilege functions and catalog queries
- Limit a program's access to an SQLite file, which has no users
- Rotate a login's password without downtime and cap what one role may use
Before you start
On this page
In the PostgreSQL 18.6 run on this page, the application’s role may read, add and change orders, yet its DELETE,
TRUNCATE and DROP TABLE are refused: 3 statements that a bug or an injected query can no longer run. A database
checks every statement against the privileges of the role that runs it, and
least privilege: The rule that each program or person connects with only the privileges its own work needs, so that a bug, a leaked password or an injected statement can do only limited damage. means that each program connects as a role that can do what that
program needs and nothing more.
In PostgreSQL, users and groups are both role: In PostgreSQL, the one kind of account for users and groups alike: a role with LOGIN can connect, any role can own objects and hold privileges, and a role can be a member of other roles to use theirs.: a role with the LOGIN attribute can connect,
any role can own objects and hold privileges, and a role can be a member of other roles to use their privileges.
GRANT gives a privilege on an object, such as SELECT on a table or USAGE on a schema, and REVOKE takes it
back. MySQL names its users as accounts of the form 'user'@'host' and has had roles since version 8.0. SQLite knows
no users or roles at all: file permissions and the way a program opens the file are its controls.
Every statement passes three privilege checks
Before a statement runs, PostgreSQL needs a yes to three questions about the role: may it connect to the database
(asked once, when the connection opens), may it look inside the schema, and does it hold the privilege the statement
needs on each table or column? A privilege can come from a grant to the role itself, from a grant to PUBLIC, which
means every role, or from a role it inherits from, and each missing answer has an error of its own. Step through the
statements of the run below:
The drawing shows the whole scene; its 6 steps are listed below.
Every step as text
- shop_report connects. shop_report connects to the database shop. A new database grants CONNECT to PUBLIC, which means every role, so this first check passes.
- Stopped at the schema. It runs SELECT count(*) FROM shop.orders. It holds SELECT on the table but nothing on the schema shop, so the statement stops there: permission denied for schema shop.
- USAGE granted. After GRANT USAGE ON SCHEMA shop, the same role passes the schema check and its SELECT on the table, and its query counts 1 order in Kochi and 2 in Pune.
- shop_app updates. shop_app holds SELECT, INSERT and UPDATE on orders. Its UPDATE of order 101 passes every check and sets the total to 50900 paise.
- DELETE and TRUNCATE refused. Its DELETE and its TRUNCATE need privileges shop_app does not hold, so both stop at the table: permission denied for table orders.
- DROP needs the owner. No GRANT hands out DROP TABLE: only the owner, the roles that inherit from it and superusers may alter or drop a table. shop_app gets: must be owner of table orders.
The run uses a throwaway PostgreSQL 18.6 server, and psql’s \connect opens a new connection as each role:
pg/privilege_errors.py
"""PostgreSQL 18.6: the checks a statement passes before it runs, and the error each missing privilege gives.
The owner role shop_owner owns the schema shop and its table orders. The dashboard (shop_report) and the
application (shop_app) log in with roles of their own, and psql's \\connect opens a new connection as each one.
"""
from pg_server import psql
psql(r"""
CREATE DATABASE shop;
\connect shop
CREATE ROLE shop_owner NOLOGIN;
CREATE ROLE shop_app LOGIN;
CREATE ROLE shop_report LOGIN;
CREATE SCHEMA shop AUTHORIZATION shop_owner;
SET ROLE shop_owner;
CREATE TABLE shop.orders (order_id int PRIMARY KEY, city text NOT NULL, total_paise int NOT NULL);
INSERT INTO shop.orders VALUES (101, 'Pune', 49900), (102, 'Kochi', 12000), (103, 'Pune', 35000);
GRANT SELECT ON shop.orders TO shop_report;
GRANT SELECT, INSERT, UPDATE ON shop.orders TO shop_app;
RESET ROLE;
\echo '-- 1. shop_report connects. It holds SELECT on orders, and nothing on the schema shop'
\connect shop shop_report
SELECT current_user;
SELECT count(*) FROM shop.orders;
\echo '-- 2. After GRANT USAGE ON SCHEMA shop, the same role reads the table'
\connect shop postgres
GRANT USAGE ON SCHEMA shop TO shop_report, shop_app;
\connect shop shop_report
SELECT city, count(*) AS orders FROM shop.orders GROUP BY city ORDER BY city;
\echo '-- 3. shop_app may SELECT, INSERT and UPDATE orders, and nothing more'
\connect shop shop_app
UPDATE shop.orders SET total_paise = 50900 WHERE order_id = 101 RETURNING order_id, total_paise;
DELETE FROM shop.orders WHERE order_id = 102;
TRUNCATE shop.orders;
DROP TABLE shop.orders;
\echo '-- 4. A brand-new login role creates a table without naming a schema'
\connect shop postgres
CREATE ROLE newcomer LOGIN;
\connect shop newcomer
CREATE TABLE notes (id int);
""")Output
-- 1. shop_report connects. It holds SELECT on orders, and nothing on the schema shop
current_user
--------------
shop_report
(1 row)
ERROR: permission denied for schema shop
LINE 1: SELECT count(*) FROM shop.orders;
^
-- 2. After GRANT USAGE ON SCHEMA shop, the same role reads the table
city | orders
-------+--------
Kochi | 1
Pune | 2
(2 rows)
-- 3. shop_app may SELECT, INSERT and UPDATE orders, and nothing more
order_id | total_paise
----------+-------------
101 | 50900
(1 row)
ERROR: permission denied for table orders
ERROR: permission denied for table orders
ERROR: must be owner of table orders
-- 4. A brand-new login role creates a table without naming a schema
ERROR: permission denied for schema public
LINE 1: CREATE TABLE notes (id int);
^
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 privilege_errors.py
pg/pg_server.py
"""Runs SQL on a private, throwaway PostgreSQL 18.6 server and prints what psql printed.
Each run makes a new database cluster in its own temporary folder (initdb), starts a server that listens only on a
Unix socket in that folder (no TCP port), sends the SQL to psql, then stops the server and deletes the folder. Every
run therefore starts from an empty database and prints the same text.
The settings keep the output the same on every run: no autovacuum (statistics change only when the SQL runs ANALYZE
or VACUUM), statistics taken from every row instead of a random sample (default_statistics_target = 10000 samples up
to 3,000,000 rows), no parallel workers, no JIT compilation, and WAL buffers large enough never to fill up during a
run. The cluster uses the C locale, UTF-8 and UTC.
PostgreSQL 18.6 comes from the folder where mise installs it (mise install postgres@18.6). That folder is in the home
folder, which the isolated runs of learn:verify may not read, so the examples that use this file are recorded with its
network isolation off (LEARN_VERIFY_NETWORK=allow); nothing here opens a network connection. The output is psql's own,
except that the "psql:<stdin>:<line>:" in front of each message is left out, and the socket's temporary folder, which
psql names when a connection is refused, is printed as "<temporary folder>". Each call of psql() is a new psql process
on the same server, so a script can go on after a connection that the server refused.
"""
import atexit
import os
import pwd
import re
import shutil
import subprocess
import sys
BIN = os.path.join(pwd.getpwuid(os.getuid()).pw_dir, ".local", "share", "mise", "installs", "postgres", "18.6", "bin")
WHERE = re.compile(r"^psql:<stdin>:\d+: ", re.MULTILINE)
# A Unix socket path may not be longer than 103 characters on macOS, so the socket goes straight into the temporary
# folder (".s.PGSQL.5432" after it) and the cluster into a folder beside it.
_tmp = os.path.normpath(os.environ.get("TMPDIR", os.getcwd()))
_base = os.path.join(_tmp, "pg")
_data = os.path.join(_base, "d")
_sock = _tmp
_env = dict(os.environ, PGHOST=_sock, PGUSER="postgres", PGDATABASE="postgres", LC_ALL="C", LANG="C", TZ="UTC")
_running = False
def _quiet(*argv: str) -> None:
subprocess.run(argv, env=_env, check=True, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
def _start() -> None:
global _running
shutil.rmtree(_base, ignore_errors=True)
os.makedirs(_base)
_quiet(os.path.join(BIN, "initdb"), "-D", _data, "-U", "postgres", "-A", "trust", "-E", "UTF8", "--locale=C", "--no-sync", "--no-instructions")
options = (
f"-k {_sock} -c listen_addresses='' -c fsync=off -c timezone=UTC -c autovacuum=off"
" -c default_statistics_target=10000 -c max_parallel_workers_per_gather=0 -c jit=off -c wal_buffers=64MB"
)
_quiet(os.path.join(BIN, "pg_ctl"), "start", "-w", "-s", "-D", _data, "-l", os.path.join(_base, "server.log"), "-o", options)
_running = True
atexit.register(_stop)
def _stop() -> None:
global _running
if _running:
subprocess.run([os.path.join(BIN, "pg_ctl"), "stop", "-s", "-m", "fast", "-D", _data], env=_env, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
_running = False
shutil.rmtree(_base, ignore_errors=True)
def psql(sql: str) -> None:
"""Sends a block of SQL to psql and prints what it printed, errors included, in order."""
if not _running:
_start()
run = subprocess.run(
[os.path.join(BIN, "psql"), "-X", "-q", "-v", "ON_ERROR_STOP=0", "-P", "pager=off"],
input=sql.strip() + "\n", env=_env, text=True, stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
)
sys.stdout.write(WHERE.sub("", run.stdout).replace(_sock, "<temporary folder>"))
sys.stdout.flush()shop_report held SELECT on the table and still got “permission denied for schema shop”, because a table privilege
is useless without USAGE on its schema; once it had USAGE, its next query on the table counted the orders of
each city.
Ownership is a different thing from privileges. The role that creates a table owns it and holds every privilege on
it, and only the owner, the roles that inherit from it and superusers may alter or drop it; no GRANT hands out those
rights. That is why the application must never own its tables, or belong to the role that does. Since PostgreSQL 15,
new databases also stop ordinary roles from creating tables in the public schema: in the run, a brand-new login role
that tried got “permission denied for schema public”.
Checkpoint
Check: The checks before a statement runs
Owner, migration and application roles split the risk
One owner role and one login role per program is the usual split. The owner holds the schema and every table but nobody logs in as it, the deploy job logs in as a migration role that belongs to the owner, and the application and the reports each get a role with only the statements they run.
| Role | Logs in from | May do |
|---|---|---|
shop_owner |
Nowhere | Owns the schema and its tables |
shop_migrator |
The deploy job | Anything the owner may |
shop_app |
The application | SELECT, INSERT, UPDATE on its tables |
shop_report |
The dashboard | SELECT on chosen tables |
Nothing else is granted: shop_app may not delete, truncate, alter or drop, and shop_report may not change a row.
The split pays off on the first bad day. In the run above, shop_app got “permission denied for table orders” for a
DELETE and a TRUNCATE and “must be owner of table orders” for DROP TABLE, so an injected DROP TABLE would have
failed even before the injection itself was fixed. People get login roles of their own as well, never a shared one,
so that every line of a log names a person.
Key idea
Give every program a login role that holds exactly the statements it runs, and leave ownership to a role nobody logs in as.
Checkpoint
Check: Owner, migration and application roles
In SQLite, the program that opens the file sets the limits
SQLite keeps no list of users to grant anything to, so least privilege lives in the program that opens the file. Below, a migration step builds the database. A reporting connection then opens the same file read-only and installs an authorizer: A callback a program installs on an SQLite connection. SQLite calls it while it prepares each statement, for every table, column and action the statement would touch, and the answer allows it, refuses the statement or makes a column read as NULL.: a function that SQLite calls while it prepares each statement, once for every table and column the statement would touch.
"""Least privilege in SQLite, which has no users: a reporting connection that can read only what it needs."""
import pathlib
import sqlite3
pathlib.Path("mart.db").unlink(missing_ok=True)
# The migration step creates and fills the database file. It is the only code here that writes.
setup = sqlite3.connect("mart.db")
setup.executescript("""
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL, phone TEXT NOT NULL);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, status TEXT NOT NULL);
CREATE TABLE payments (order_id INTEGER PRIMARY KEY, card_last4 TEXT NOT NULL);
INSERT INTO customers VALUES (1, 'Asha Kulkarni', 'Pune', '+91 98200 11111'), (2, 'Vikram Nair', 'Kochi', '+91 98470 22222');
INSERT INTO orders VALUES (101, 1, 'delivered'), (102, 2, 'delivered'), (103, 1, 'placed');
INSERT INTO payments VALUES (101, '4242'), (102, '1881');
""")
setup.close()
# The reporting program opens the same file read-only, then installs an authorizer: a function SQLite calls
# while it prepares each statement, for every table and column the statement would touch.
report = sqlite3.connect("file:mart.db?mode=ro", uri=True)
READABLE = {"customers": {"customer_id", "name", "city"}, "orders": {"order_id", "customer_id", "status"}}
def authorizer(action, arg1, arg2, db_name, trigger_or_view):
if action in (sqlite3.SQLITE_SELECT, sqlite3.SQLITE_FUNCTION):
return sqlite3.SQLITE_OK
if action == sqlite3.SQLITE_READ: # arg1 is the table, arg2 the column ("" for count(*))
if arg1 not in READABLE:
return sqlite3.SQLITE_DENY # the whole statement fails
if arg2 == "" or arg2 in READABLE[arg1]:
return sqlite3.SQLITE_OK
return sqlite3.SQLITE_IGNORE # the column reads as NULL
return sqlite3.SQLITE_DENY # everything else: writes, PRAGMA, ATTACH ...
report.set_authorizer(authorizer)
def attempt(label, sql):
try:
print(f"{label}: {report.execute(sql).fetchall()}")
except sqlite3.DatabaseError as e:
print(f"{label}: refused ({e})")
attempt("orders per city", "SELECT c.city, count(*) FROM orders o JOIN customers c USING (customer_id) GROUP BY c.city ORDER BY c.city")
attempt("names and phones", "SELECT name, phone FROM customers ORDER BY customer_id")
attempt("card digits", "SELECT order_id, card_last4 FROM payments")
attempt("delete orders", "DELETE FROM orders")
attempt("attach another file", "ATTACH DATABASE 'other.db' AS other")
# Without the authorizer, the read-only open still refuses every write.
report.set_authorizer(None)
attempt("delete orders, no authorizer", "DELETE FROM orders")Output
orders per city: [('Kochi', 1), ('Pune', 2)]
names and phones: [('Asha Kulkarni', None), ('Vikram Nair', None)]
card digits: refused (access to payments.order_id is prohibited)
delete orders: refused (not authorized)
attach another file: refused (not authorized)
delete orders, no authorizer: refused (attempt to write a readonly database)
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 report_access.py
Runs on this device, in your browser. The first run downloads Python (about 13.5 MB), which is kept for the next runs.
Your run, in this browser
The report counts orders per city as usual. Names come back, but every phone number reads as None, because the
authorizer answered SQLITE_IGNORE for that column instead of refusing the whole query. The card digits, the
DELETE and the ATTACH are refused outright, and the last line shows the read-only open refusing a write on its
own. Press Edit, add "phone" to the readable columns of customers and run it again: the numbers come back. On a
server, the operating system decides who may open the file at all, so make it readable by the application’s own user
alone, for example with chmod 600 mart.db.
Checkpoint
Check: Least privilege for an SQLite file
Setting up the four roles in PostgreSQL, step by step Pro: opens with a Pro, Premium or Ultimate pass
This part builds the owner, migration, application and reporting roles for one schema on PostgreSQL 18, statement by statement. It then reproduces the five permission errors that new setups meet first, each with its fix, and closes the privileges that every role holds by default.
In this part:
- The statements in the order a new database needs them, worked through
- Five permission errors reproduced, with their fixes
- PUBLIC: the default privileges of every role, reproduced
3 runnable examples (Python) · about 7 minutes
Opening this part with your pass…
This part opens with any MySmartCoPilot pass.
- Pro pass · 1 week₹79$2.99
- 30 days₹179$5.99
- 1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so this part can’t be opened at the moment. The free part above stands on its own. If you have a pass, enter your licence key.
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
Checking who can do what, with queries you can run Pro: opens with a Pro, Premium or Ultimate pass
Grants pile up over years of releases, and the only reliable answer to “what can this role do today?” comes from the database itself. This part writes the audit queries for PostgreSQL and MySQL and works inherited privileges out with a query you can run, with an exercise to try it on.
In this part:
- Asking PostgreSQL: privilege functions and the catalog
- Inherited privileges, worked out with a query
- Your turn: who can change orders?
- List every login role that can change orders
- Results of the sample tests
- MySQL 8.4: accounts, roles and the inactive-role trap
3 runnable examples (Python, SQL, mysql_server.py, roles.py) · 1 exercise with sample tests · about 7 minutes
Opening this part with your pass…
This part opens with any MySmartCoPilot pass.
- Pro pass · 1 week₹79$2.99
- 30 days₹179$5.99
- 1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so this part can’t be opened at the moment. The free part above stands on its own. If you have a pass, enter your licence key.
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
Passwords, TLS and limits for database roles Pro: opens with a Pro, Premium or Ultimate pass
Careful grants still fail if a password leaks or the database port can be reached from the internet. This part rotates an application password without downtime on both servers, makes clients check the server they connect to, and caps what a single role can use, with the numbers worked out.
In this part:
- Rotating the application’s password without downtime, worked
- Encrypted connections that check the server
- Connection budgets and timeouts per role, with numbers
- Removing a role cleanly
3 runnable examples (Python) · about 5 minutes
Opening this part with your pass…
This part opens with any MySmartCoPilot pass.
- Pro pass · 1 week₹79$2.99
- 30 days₹179$5.99
- 1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so this part can’t be opened at the moment. The free part above stands on its own. If you have a pass, enter your licence key.
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
Practise: grants that fit the job
These questions cover the first three sections. The session starts where you are weakest and explains every answer as soon as you give it:
Practice that adapts to you
Practise this lesson
9 questions about 6 ideas of this lesson. Each answer is explained at once, and what you get right is kept in this browser.
Interview questions
-
Fresher to mid · Warm-up
In PostgreSQL, what is the difference between a user and a role, and what does LOGIN change?
PostgreSQL has only roles. A role can own objects, hold privileges and be a member of other roles; "user" is the everyday name for a role with the
LOGINattribute, which lets it open a connection, andCREATE USERisCREATE ROLEwithLOGINalready set. A role withoutLOGINworks as a group: privileges are granted to it once, and the login roles that belong to it use them, through inheritance or afterSET ROLE. Attributes such asLOGIN,SUPERUSER,CREATEDBandCREATEROLEare never inherited through membership. A tidy setup keeps grants and ownership on group roles and gives login roles nothing directly, so changing who may do what means changing memberships, not editing grants table by table. -
Mid to senior
A reporting dashboard connected to PostgreSQL with the application's own database account, and one of its scheduled queries emptied a production table with TRUNCATE. Design the roles so this cannot happen again, and say how you roll the change out without downtime.
-
Mid to senior
Right after a release, the API fails with "permission denied for table refunds". Months ago the app role was given SELECT, INSERT and UPDATE on all tables in the schema, and this release's migration created refunds. Explain the cause, fix it now and fix it for good.
-
Mid to staff
The password of your application's PostgreSQL login was pasted into a public chat channel. Rotate it across 12 running app instances without failed requests, and explain how you limit the damage while you do it.
Key takeaways
- Every statement is checked against its role:
CONNECTon the database,USAGEon the schema, then the privilege on the table or its columns. - Owners may always alter and drop what they own, so programs connect as roles that own nothing.
- Give each program its own login role with only the statements it runs; in SQLite, open the file read-only and add an authorizer.
Terms in this lesson
- authorizer
- A callback a program installs on an SQLite connection. SQLite calls it while it prepares each statement, for every table, column and action the statement would touch, and the answer allows it, refuses the statement or makes a column read as NULL.
- least privilege
- The rule that each program or person connects with only the privileges its own work needs, so that a bug, a leaked password or an injected statement can do only limited damage.
- role
- In PostgreSQL, the one kind of account for users and groups alike: a role with LOGIN can connect, any role can own objects and hold privileges, and a role can be a member of other roles to use theirs.
References
- PostgreSQL documentation: Database Roles (The PostgreSQL Global Development Group)
- PostgreSQL documentation: Role Membership (The PostgreSQL Global Development Group)
- PostgreSQL documentation: Privileges (The PostgreSQL Global Development Group)
- PostgreSQL documentation: Schemas (schemas and privileges, usage patterns) (The PostgreSQL Global Development Group)
- PostgreSQL documentation: GRANT (The PostgreSQL Global Development Group)
- PostgreSQL documentation: ALTER DEFAULT PRIVILEGES (The PostgreSQL Global Development Group)
- PostgreSQL documentation: CREATE FUNCTION (writing SECURITY DEFINER functions safely) (The PostgreSQL Global Development Group)
- PostgreSQL documentation: ALTER ROLE (settings and CONNECTION LIMIT) (The PostgreSQL Global Development Group)
- PostgreSQL documentation: System Information Functions (access privilege inquiry) (The PostgreSQL Global Development Group)
- PostgreSQL documentation: The Information Schema, role_table_grants (The PostgreSQL Global Development Group)
- PostgreSQL documentation: psql (\connect and \dp) (The PostgreSQL Global Development Group)
- PostgreSQL documentation: Predefined Roles (The PostgreSQL Global Development Group)
- PostgreSQL documentation: Dropping Roles (The PostgreSQL Global Development Group)
- PostgreSQL documentation: SSL Support in libpq (sslmode) (The PostgreSQL Global Development Group)
- PostgreSQL documentation: The pg_hba.conf File (The PostgreSQL Global Development Group)
- PostgreSQL documentation: Connections and Authentication (max_connections) (The PostgreSQL Global Development Group)
- PostgreSQL 18 Release Notes (MD5 password deprecation) (The PostgreSQL Global Development Group)
- MySQL 8.4 Reference Manual: Access Control and Account Management (Oracle Corporation)
- MySQL 8.4 Reference Manual: Using Roles (Oracle Corporation)
- MySQL 8.4 Reference Manual: Password Management (dual password support) (Oracle Corporation)
- MySQL 8.4 Reference Manual: Configuring MySQL to Use Encrypted Connections (Oracle Corporation)
- SQLite: Compile-Time Authorization Callbacks (sqlite3_set_authorizer) (SQLite)
- SQLite: Uniform Resource Identifiers (mode=ro) (SQLite)
- sqlite3: DB-API 2.0 interface for SQLite databases (set_authorizer) (Python Software Foundation)
- OWASP Cheat Sheet Series: Database Security Cheat Sheet (OWASP Foundation)
- OWASP Cheat Sheet Series: Secrets Management Cheat Sheet (OWASP Foundation)
Related tools
Report a problem with this lesson
Kept only in this browser. Your Learn progress