Access control, row-level security, SQL injection

Access control decides what each user of a database may do. In PostgreSQL every user is a role, and roles receive privileges on objects: SELECT on a table, UPDATE on two of its columns, EXECUTE on a function. GRANT gives a privilege and REVOKE takes it back, and least privilege means giving each role only what its job needs. Row-level security goes one step further and adds a condition to every query on a table, so that each user sees and changes only their own rows, the usual way to keep tenants apart in a shared table. SQL injection is how access control is defeated from outside: a program pastes user input into SQL text, and the input changes what the query does. Sending the input as a parameter, separately from the SQL, keeps it a value. SQLite has no users or GRANT at all; the operating system’s file permissions and the program decide who may do what.

The roles, policies and passwords below are what psql printed on an empty PostgreSQL 18.6 cluster; the two Python programs ran on Python 3.14.8 with its built-in sqlite3 module.

Roles, privileges and least privilege

A role with the LOGIN attribute can connect; a role without it is a group that holds privileges for its members. The creator of an object is its owner, who can do anything with it, including grant privileges on it, and the right to alter or drop it cannot be granted at all. PostgreSQL gives the pseudo-role PUBLIC (everyone) a few privileges by default, such as EXECUTE on new functions and CONNECT on databases, but nothing on tables, columns, sequences or newly created schemas.

# Roles and privileges in PostgreSQL 18: least privilege, column privileges and a view.
from pg_session import psql

psql("""
CREATE ROLE admissions NOLOGIN;              -- a group role: holds privileges
CREATE ROLE asha LOGIN IN ROLE admissions;   -- a person: gets them by membership
CREATE ROLE reporting LOGIN;

CREATE TABLE students (roll integer PRIMARY KEY, name text NOT NULL, dept text NOT NULL, phone text);
INSERT INTO students VALUES (101, 'Asha', 'CSE', '90000 00101'), (102, 'Ravi', 'ECE', '90000 00102');
CREATE VIEW dept_sizes AS SELECT dept, count(*) AS students FROM students GROUP BY dept;

GRANT SELECT, INSERT, UPDATE ON students TO admissions;
GRANT SELECT (roll, name, dept) ON students TO reporting;   -- every column but phone
GRANT SELECT ON dept_sizes TO reporting;

SET ROLE reporting;
SELECT roll, name FROM students ORDER BY roll;
SELECT * FROM students;                     -- * includes phone
SELECT * FROM dept_sizes ORDER BY dept;     -- the view reads students for it
DELETE FROM students WHERE roll = 102;
RESET ROLE;

SET ROLE asha;
UPDATE students SET phone = '90000 00199' WHERE roll = 101;
DELETE FROM students WHERE roll = 102;      -- admissions has no DELETE
RESET ROLE;

REVOKE UPDATE ON students FROM admissions;
SET ROLE asha;
UPDATE students SET phone = '90000 00111' WHERE roll = 101;
RESET ROLE;

SELECT grantee, privilege_type FROM information_schema.role_table_grants
WHERE table_name = 'students' AND grantee <> 'postgres' ORDER BY grantee, privilege_type;
SELECT grantee, column_name, privilege_type FROM information_schema.column_privileges
WHERE table_name = 'students' AND grantee = 'reporting' ORDER BY column_name;
""")

CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE TABLE
INSERT 0 2
CREATE VIEW
GRANT
GRANT
GRANT
SET
 roll | name 
------+------
  101 | Asha
  102 | Ravi
(2 rows)

ERROR:  permission denied for table students
 dept | students 
------+----------
 CSE  |        1
 ECE  |        1
(2 rows)

ERROR:  permission denied for table students
RESET
SET
UPDATE 1
ERROR:  permission denied for table students
RESET
REVOKE
SET
ERROR:  permission denied for table students
RESET
  grantee   | privilege_type 
------------+----------------
 admissions | INSERT
 admissions | SELECT
(2 rows)

  grantee  | column_name | privilege_type 
-----------+-------------+----------------
 reporting | dept        | SELECT
 reporting | name        | SELECT
 reporting | roll        | SELECT
(3 rows)

• asha has no privileges of her own; she acts through her membership of admissions, so revoking from the group took UPDATE away from her at once. Granting to group roles and adding people to them keeps privileges manageable.
• reporting may read three columns of students. Naming them works; SELECT * fails, because the asterisk asks for phone too.
• reporting read dept_sizes although it cannot read phone or even run SELECT * on students. A view reads its tables with the privileges of the view’s owner, so a grant on a view hands out exactly the rows and columns the view shows, as the views lesson describes.
• Every refusal reads “permission denied”, and the last two queries show where PostgreSQL keeps the grants: information_schema.role_table_grants for table privileges and information_schema.column_privileges for columns.

Least privilege in practice: the application connects as a role that owns nothing and holds only the privileges it uses; migrations run as the owner; reports read views; nobody uses a superuser for everyday work, because a superuser skips every check, row-level security included.

Row-level security for a shared table

Many applications keep all customers in the same tables, with a tenant column. Row-level security makes PostgreSQL add the tenant condition to every query itself, so a forgotten WHERE tenant = … in one query cannot leak another customer’s rows. ALTER TABLE … ENABLE ROW LEVEL SECURITY turns it on, and CREATE POLICY says which rows a role may use: USING filters the rows that queries see, update and delete, and WITH CHECK tests the rows that inserts and updates write.

# Row-level security for a table shared by several tenants (PostgreSQL 18).
from pg_session import psql

psql("""
CREATE ROLE app_owner NOLOGIN;      -- owns the table, and is not a superuser
CREATE ROLE web LOGIN;              -- the role the application connects as

CREATE TABLE invoices (
  tenant text NOT NULL,
  no     integer NOT NULL,
  amount integer NOT NULL,
  PRIMARY KEY (tenant, no)
);
ALTER TABLE invoices OWNER TO app_owner;
INSERT INTO invoices VALUES ('north-clinic', 1, 1200), ('north-clinic', 2, 450), ('south-clinic', 1, 900);
GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO web;

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
SET ROLE web;
SELECT count(*) AS rows_seen FROM invoices;           -- enabled, no policy yet: nothing
RESET ROLE;

CREATE POLICY tenant_rows ON invoices
  USING (tenant = current_setting('app.tenant', true))
  WITH CHECK (tenant = current_setting('app.tenant', true));

SET app.tenant = 'south-clinic';
SET ROLE web;
SELECT * FROM invoices ORDER BY no;
INSERT INTO invoices VALUES ('north-clinic', 3, 999);     -- WITH CHECK refuses it
UPDATE invoices SET amount = 0 WHERE tenant = 'north-clinic';
RESET ROLE;

SET ROLE app_owner;
SELECT count(*) AS owner_sees FROM invoices;              -- owners bypass policies
RESET ROLE;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
SET ROLE app_owner;
SELECT count(*) AS owner_sees_when_forced FROM invoices;
RESET ROLE;
SELECT count(*) AS superuser_sees FROM invoices;          -- superusers always bypass

-- The setting is only as trustworthy as whoever sets it: any session can change it.
SET ROLE web;
SET app.tenant = 'north-clinic';
SELECT count(*) AS web_after_changing_the_setting FROM invoices;
""")

CREATE ROLE
CREATE ROLE
CREATE TABLE
ALTER TABLE
INSERT 0 3
GRANT
ALTER TABLE
SET
 rows_seen 
-----------
         0
(1 row)

RESET
CREATE POLICY
SET
SET
    tenant    | no | amount 
--------------+----+--------
 south-clinic |  1 |    900
(1 row)

ERROR:  new row violates row-level security policy for table "invoices"
UPDATE 0
RESET
SET
 owner_sees 
------------
          3
(1 row)

RESET
ALTER TABLE
SET
 owner_sees_when_forced 
------------------------
                      1
(1 row)

RESET
 superuser_sees 
----------------
              3
(1 row)

SET
SET
 web_after_changing_the_setting 
--------------------------------
                              2
(1 row)

• With row-level security enabled and no policy yet, web saw 0 rows. The default is to deny.
• With the policy and app.tenant set to south-clinic, web saw only that clinic’s invoice. Its insert for north-clinic failed the WITH CHECK condition, and its update of the north clinic’s rows found nothing to update, UPDATE 0, because USING hid them.
• The owner saw all 3 rows: owners bypass policies unless the table is set to FORCE ROW LEVEL SECURITY, after which it saw 1. Superusers and roles with the BYPASSRLS attribute always bypass row-level security.
• The last query is the warning. Any session may change a setting such as app.tenant, and after web changed it, it saw the north clinic’s 2 rows. A policy on a setting is only as safe as the code that sets it.

So set the tenant in one trusted place, for example with SET LOCAL at the start of each transaction in the code that checks who the user is, and never from anything the user sends. For stronger isolation, give each tenant its own login role and write the policy on current_user: a session can switch only to roles its login role belongs to, so it cannot become another tenant. Two limits remain: unique keys and foreign keys are checked against all rows, whatever the policies say, so a refused insert can reveal that a value exists in another tenant’s rows; and whole-table operations such as TRUNCATE are not filtered by policies at all.

SQL injection

SQL injection happens when user input becomes part of the SQL text. The database cannot tell which characters the programmer wrote and which ones came from the user, so input that contains a quote, a comment marker or a keyword changes the structure of the query.

"""One login check written twice: by pasting the input into the SQL text, and with placeholders."""
import hashlib
import sqlite3


def pw_hash(password):
    # Kept short for the example. Real systems store a salted, deliberately slow hash (scrypt, Argon2, bcrypt).
    return hashlib.sha256(password.encode()).hexdigest()


con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT UNIQUE, pw_hash TEXT)")
con.execute("INSERT INTO users (email, pw_hash) VALUES (?, ?)", ("asha@example.com", pw_hash("Tea-at-4pm")))


def login_unsafe(email, password):
    sql = f"SELECT id FROM users WHERE email = '{email}' AND pw_hash = '{pw_hash(password)}'"
    return sql, con.execute(sql).fetchone() is not None


def login_safe(email, password):
    sql = "SELECT id FROM users WHERE email = ? AND pw_hash = ?"
    return sql, con.execute(sql, (email, pw_hash(password))).fetchone() is not None


attack = "asha@example.com' --"
for label, email, password in [
    ("right password", "asha@example.com", "Tea-at-4pm"),
    ("wrong password", "asha@example.com", "guess"),
    ("crafted email ", attack, "anything"),
]:
    sql, ok = login_unsafe(email, password)
    print(f"unsafe, {label}: logged in = {ok}")
    print(f"    {sql[:70]}...")

for label, email, password in [("right password", "asha@example.com", "Tea-at-4pm"), ("crafted email ", attack, "anything")]:
    print(f"placeholders, {label}: logged in = {login_safe(email, password)[1]}")

try:
    con.execute("SELECT 1; DROP TABLE users")
except sqlite3.ProgrammingError as e:
    print("two statements in one execute():", e)

unsafe, right password: logged in = True
    SELECT id FROM users WHERE email = 'asha@example.com' AND pw_hash = 'd...
unsafe, wrong password: logged in = False
    SELECT id FROM users WHERE email = 'asha@example.com' AND pw_hash = 'f...
unsafe, crafted email : logged in = True
    SELECT id FROM users WHERE email = 'asha@example.com' --' AND pw_hash ...
placeholders, right password: logged in = True
placeholders, crafted email : logged in = False
two statements in one execute(): You can only execute one statement at a time.

The pasted version logs in with the right password and refuses the wrong one, so ordinary testing passes. The crafted email closes the string with its quote and starts a comment with --, which removes the password check, and the attacker is logged in as Asha. The version with placeholders sends the SQL with two ? marks and the values separately; the database parses the query before it sees any value, so the crafted email is just an email that no user has. The last line shows one more protection of Python’s sqlite3: execute() refuses two statements in one call, so an input cannot tack a DROP TABLE on. That limits the damage; it does not prevent injection.

The rules that follow:

• Values go in as parameters: ? in Python’s sqlite3, $1 in PostgreSQL, whatever placeholder your driver uses. This also handles honest input that breaks hand-made SQL, such as the name D’Souza.
• Names cannot be parameters. A placeholder stands for a value, so a table or column chosen by the user, as in a sort order, must come from a list of names your code allows, or be quoted as an identifier.
• Escaping by hand is not a defence. Stripping quotes, banning words like OR or doubling quotes yourself breaks honest input and misses forms nobody thought of.
• Least privilege limits the damage of the injection you did not find: a role that cannot read users cannot leak it.

The OWASP SQL Injection Prevention Cheat Sheet puts parameterised queries first among its defences, recommends allowlists for names, and strongly discourages escaping as a main defence. The same rules apply to SQL that the database builds itself. In PL/pgSQL, format() quotes names with %I and values with %L, and EXECUTE … USING passes values as parameters:

# Building SQL inside the database safely: format() with %I and %L, and EXECUTE ... USING.
from pg_session import psql

psql("""
CREATE TABLE students (roll integer PRIMARY KEY, name text NOT NULL);
INSERT INTO students VALUES (101, 'Asha'), (102, 'D''Souza');

CREATE FUNCTION count_named(tbl text, who text) RETURNS bigint LANGUAGE plpgsql AS $$
DECLARE
  n bigint;
BEGIN
  -- %I quotes a name (an identifier), %L a value (a literal); shown here for reading only.
  RAISE NOTICE '%', format('SELECT count(*) FROM %I WHERE name = %L', tbl, who);
  -- The value itself travels as a parameter, $1, and never becomes part of the text.
  EXECUTE format('SELECT count(*) FROM %I WHERE name = $1', tbl) INTO n USING who;
  RETURN n;
END $$;

SELECT count_named('students', 'D''Souza');
SELECT count_named('students; DROP TABLE students', 'x');
SELECT count_named('students', 'x'' OR ''1''=''1');
SELECT count(*) AS students_left FROM students;
SELECT quote_ident('Students') AS ident, quote_ident('students') AS plain, quote_literal('it''s') AS literal;
""")

CREATE TABLE
INSERT 0 2
CREATE FUNCTION
NOTICE:  SELECT count(*) FROM students WHERE name = 'D''Souza'
 count_named 
-------------
           1
(1 row)

NOTICE:  SELECT count(*) FROM "students; DROP TABLE students" WHERE name = 'x'
ERROR:  relation "students; DROP TABLE students" does not exist
LINE 1: SELECT count(*) FROM "students; DROP TABLE students" WHERE n...
                             ^
QUERY:  SELECT count(*) FROM "students; DROP TABLE students" WHERE name = $1
CONTEXT:  PL/pgSQL function count_named(text,text) line 8 at EXECUTE
NOTICE:  SELECT count(*) FROM students WHERE name = 'x'' OR ''1''=''1'
 count_named 
-------------
           0
(1 row)

 students_left 
---------------
             2
(1 row)

   ident    |  plain   | literal 
------------+----------+---------
 "Students" | students | 'it''s'
(1 row)

The crafted table name became one quoted identifier, "students; DROP TABLE students", which does not exist, so the statement failed and nothing was dropped: both students are still there. The crafted value matched no name, because it was only ever a value. This matters even for data that was stored safely: text saved once with a parameter and later pasted into dynamic SQL by a nightly job is the same injection, a step later (often called second-order injection).

SQLite: no users, no GRANT

SQLite is a library that reads and writes a single file, so it has no users, no roles and no GRANT or REVOKE; its documentation calls them meaningless for an embedded engine. Whoever can open the file can do anything the file’s operating-system permissions allow. When a program must run SQL it does not trust, for example a query typed by a user, it can install an authorizer: a callback that SQLite consults while it prepares each statement, and that can allow an action, refuse it, or make a column read as NULL:

"""SQLite has no users or GRANT. A program can still limit what SQL may do on one connection, with an authorizer."""
import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
    CREATE TABLE students (roll INTEGER PRIMARY KEY, name TEXT, phone TEXT);
    INSERT INTO students VALUES (101, 'Asha', '90000 00101'), (102, 'Ravi', '90000 00102');
""")


def read_only_without_phone(action, arg1, arg2, db_name, trigger):
    """Called while each statement is prepared: allow it, refuse it, or read a column as NULL."""
    if action == sqlite3.SQLITE_READ and (arg1, arg2) == ("students", "phone"):
        return sqlite3.SQLITE_IGNORE
    if action in (sqlite3.SQLITE_INSERT, sqlite3.SQLITE_UPDATE, sqlite3.SQLITE_DELETE, sqlite3.SQLITE_DROP_TABLE):
        return sqlite3.SQLITE_DENY
    return sqlite3.SQLITE_OK


con.set_authorizer(read_only_without_phone)
print(con.execute("SELECT roll, name, phone FROM students ORDER BY roll").fetchall())
for sql in ("UPDATE students SET name = 'x'", "DELETE FROM students", "DROP TABLE students"):
    try:
        con.execute(sql)
    except sqlite3.DatabaseError as e:
        print(f"{sql}: {e}")

[(101, 'Asha', None), (102, 'Ravi', None)]
UPDATE students SET name = 'x': not authorized
DELETE FROM students: not authorized
DROP TABLE students: not authorized

The phone numbers came back as None and every change was refused with “not authorized”. The authorizer belongs to one connection, so it protects what that connection runs, not the file.

Passwords: SCRAM, not MD5

PostgreSQL stores each role’s password as a salted SCRAM-SHA-256 verifier by default. The older MD5 format protects the password on the wire but gives no protection once the stored hash is stolen, and PostgreSQL 18 deprecates it:

# How PostgreSQL 18 stores role passwords, and what it says about MD5.
from pg_session import psql

psql("""
SHOW password_encryption;
CREATE ROLE new_style LOGIN PASSWORD 'correct horse battery staple';
SET password_encryption = 'md5';
CREATE ROLE old_style LOGIN PASSWORD 'correct horse battery staple';
SELECT rolname,
       CASE WHEN rolpassword LIKE 'SCRAM-SHA-256$%' THEN 'SCRAM-SHA-256 verifier'
            WHEN rolpassword LIKE 'md5%' THEN 'MD5 hash' END AS stored_as
FROM pg_authid WHERE rolname IN ('new_style', 'old_style') ORDER BY rolname;
""")

 password_encryption 
---------------------
 scram-sha-256
(1 row)

CREATE ROLE
SET
WARNING:  setting an MD5-encrypted password
DETAIL:  MD5 password support is deprecated and will be removed in a future release of PostgreSQL.
HINT:  Refer to the PostgreSQL documentation for details about migrating to another password type.
CREATE ROLE
  rolname  |       stored_as        
-----------+------------------------
 new_style | SCRAM-SHA-256 verifier
 old_style | MD5 hash
(2 rows)

The format is chosen when the password is set, so a server moved off MD5 keeps old MD5 hashes until each user sets a new password; pg_authid shows which roles still have one. Generate long random passwords for application roles and keep them in a secret store, never in the code.

Checks in application code come on top of these layers, not instead of them: they protect only the code paths that call them, so a script, a second service or a console session walks straight past them.

Exercise: make a member search safe

def find_member_query(name):
    """The SQL text and its values that find the members with this name."""
    # Unsafe: the name becomes part of the SQL text.
    sql = f"SELECT id, name, city FROM members WHERE name = '{name}'"
    return sql, ()


def list_members_query(order_by, direction):
    """The SQL text and its values that list every member, sorted as asked."""
    # Unsafe: whatever the user sends becomes part of the SQL text.
    sql = f"SELECT id, name, city FROM members ORDER BY {order_by} {direction}"
    return sql, ()

from members import find_member_query, list_members_query


def raises_value_error(call):
    try:
        call()
    except ValueError:
        return True
    return False


def test_name_is_a_parameter():
    """the name is sent as the value of one placeholder"""
    sql, values = find_member_query("Asha")
    assert sql == "SELECT id, name, city FROM members WHERE name = ?"
    assert values == ("Asha",)


def test_quote_in_a_name():
    """a real name with an apostrophe needs no escaping"""
    sql, values = find_member_query("D'Souza")
    assert "Souza" not in sql
    assert values == ("D'Souza",)


def test_attack_stays_a_value():
    """a crafted name leaves the SQL text exactly as it is for any other name"""
    attack = "x' OR '1'='1"
    sql, values = find_member_query(attack)
    assert sql == find_member_query("Asha")[0]
    assert values == (attack,)


def test_sort_by_an_allowed_column():
    """an allowed column and direction, in any case"""
    assert list_members_query("city", "desc") == ("SELECT id, name, city FROM members ORDER BY city DESC", ())
    assert list_members_query("Name", "ASC") == ("SELECT id, name, city FROM members ORDER BY name ASC", ())


def test_sort_refuses_other_columns():
    """anything that is not an allowed column raises ValueError"""
    assert raises_value_error(lambda: list_members_query("phone", "asc"))
    assert raises_value_error(lambda: list_members_query("name; DROP TABLE members", "asc"))


def test_sort_refuses_other_directions():
    """only asc and desc are directions"""
    assert raises_value_error(lambda: list_members_query("joined", "asc; DROP TABLE members"))
    assert raises_value_error(lambda: list_members_query("joined", ""))

Check yourself

Interview questions

What is SQL injection, and how do parameterised queries prevent it? (warm-up)

SQL injection happens when a program builds SQL text by pasting in user input, so that input containing a quote, a comment marker or a keyword changes the structure of the query: asha@example.com' -- in a login form closes the email string and comments out the password check. A parameterised query sends the SQL with placeholders, such as ? or $1, and the values separately. The database parses and plans the statement before it sees any value, so a value can only ever be compared, stored or returned, never run. Placeholders work for values only; table and column names chosen by users must come from an allowlist or be quoted as identifiers. Least privilege for the application’s role limits what a missed injection could reach.

Key takeaways

• PostgreSQL grants privileges to roles; group roles carry them for their members, and the owner can do anything. Give each role only what it needs, with column privileges and views for narrower access.
• A view reads its tables with its owner’s privileges, so granting the view, and not the table, exposes exactly what the view shows.
• Row-level security adds USING and WITH CHECK conditions to every query on a table and denies everything without a policy. Owners bypass it unless the table is forced, and superusers and BYPASSRLS roles always do.
• A policy built on a session setting is only as safe as the code that sets it; set it in one trusted place.
• SQL injection is input that changes a query’s structure. Send values as parameters, take names from an allowlist or quote them with format('%I'), and never escape by hand.
• SQLite has no users or GRANT: protect the file with operating-system permissions, and limit untrusted SQL with an authorizer.
• PostgreSQL stores passwords as SCRAM-SHA-256 by default; PostgreSQL 18 deprecates MD5.