Database Security

Database security means every role can do exactly what its job needs and nothing more. The examples create two roles and test what each one can reach. Run them as a superuser on a scratch database.

-- setup.sql
CREATE TABLE employees (id INT PRIMARY KEY, name TEXT NOT NULL,
                        dept TEXT NOT NULL, salary INT NOT NULL);
INSERT INTO employees VALUES (1, 'Ana', 'Eng', 95000), (2, 'Ben', 'Sales', 60000);
CREATE ROLE analyst NOLOGIN;
CREATE ROLE app_user LOGIN PASSWORD 'change-me';
GRANT analyst TO app_user;
SELECT rolname, rolcanlogin, rolsuper FROM pg_roles
WHERE rolname IN ('analyst', 'app_user') ORDER BY rolname;

$ psql -d sqldemo -f setup.sql
CREATE TABLE
INSERT 0 2
CREATE ROLE
CREATE ROLE
GRANT ROLE
 rolname  | rolcanlogin | rolsuper 
----------+-------------+----------
 analyst  | f           | f
 app_user | t           | f
(2 rows)

analyst cannot log in; it is a group that will hold privileges. app_user can log in and inherits whatever analyst is granted. Granting privileges to group roles, not to individual logins, keeps permissions manageable as people come and go.

GRANT and REVOKE

A new role can do nothing with a table until it is granted privileges.

-- grants.sql
GRANT SELECT ON employees TO analyst;
SET ROLE app_user;
SELECT name, dept FROM employees ORDER BY id;
UPDATE employees SET salary = 1 WHERE id = 1;
RESET ROLE;

$ psql -d sqldemo -f grants.sql
GRANT
SET
 name | dept  
------+-------
 Ana  | Eng
 Ben  | Sales
(2 rows)

psql:grants.sql:5: ERROR:  permission denied for table employees
RESET

SET ROLE switches the session to app_user, a handy way to test permissions without a second login. Reading worked through the analyst membership; the UPDATE was refused because only SELECT was granted. RESET ROLE switches back.

Column-Level Privileges

Privileges can be narrower than a whole table. Here salaries are hidden.

-- column_grants.sql
REVOKE SELECT ON employees FROM analyst;
GRANT SELECT (id, name, dept) ON employees TO analyst;
SET ROLE app_user;
SELECT id, name, dept FROM employees ORDER BY id;
SELECT name, salary FROM employees;
SELECT * FROM employees;
RESET ROLE;

$ psql -d sqldemo -f column_grants.sql
REVOKE
GRANT
SET
 id | name | dept  
----+------+-------
  1 | Ana  | Eng
  2 | Ben  | Sales
(2 rows)

psql:column_grants.sql:6: ERROR:  permission denied for table employees
psql:column_grants.sql:7: ERROR:  permission denied for table employees
RESET

The permitted columns read normally. Asking for salary, directly or through *, fails the whole query. The error message names the table, not the column, so column grants can confuse application developers; a view exposing only the safe columns is often clearer.

The Public Schema Is Locked Down

Since PostgreSQL 15, ordinary roles cannot create tables in the public schema.

-- public_schema.sql
SET ROLE app_user;
CREATE TABLE scratch (x INT);
RESET ROLE;

$ psql -d sqldemo -f public_schema.sql
SET
psql:public_schema.sql:3: ERROR:  permission denied for schema public
LINE 1: CREATE TABLE scratch (x INT);
                     ^
RESET

Before version 15, any role could create objects in public, which let one user plant functions or tables that another user’s queries would pick up. Now schema creation rights must be granted on purpose, ideally on a schema owned by the application’s migration role.

Row-Level Security

Row-level security (RLS) filters rows by policy. Each role sees only its own notes.

-- rls.sql
CREATE TABLE notes (id INT PRIMARY KEY, author TEXT NOT NULL, body TEXT NOT NULL);
INSERT INTO notes VALUES (1, 'app_user', 'my note'), (2, 'analyst', 'not yours');
GRANT SELECT, INSERT ON notes TO app_user;
ALTER TABLE notes ENABLE ROW LEVEL SECURITY;
CREATE POLICY own_notes ON notes
    USING (author = current_user)
    WITH CHECK (author = current_user);
SET ROLE app_user;
SELECT id, author, body FROM notes;
INSERT INTO notes VALUES (3, 'analyst', 'forged');
RESET ROLE;
SELECT count(*) AS rows_superuser_sees FROM notes;

$ psql -d sqldemo -f rls.sql
CREATE TABLE
INSERT 0 2
GRANT
ALTER TABLE
CREATE POLICY
SET
 id |  author  |  body   
----+----------+---------
  1 | app_user | my note
(1 row)

psql:rls.sql:11: ERROR:  new row violates row-level security policy for table "notes"
RESET
 rows_superuser_sees 
---------------------
                   2
(1 row)

app_user asked for every note and got only its own: USING quietly filtered the other row without an error. WITH CHECK applies to writes, so inserting a note in someone else’s name was rejected. The superuser still sees both rows: superusers and roles with BYPASSRLS always skip RLS. The table owner skips it too, unless you add ALTER TABLE ... FORCE ROW LEVEL SECURITY, which applies the policies to the owner but never to superusers.

SQL Injection

Building a query by gluing user input into SQL text lets the input become SQL.

-- injection.sql
CREATE FUNCTION find_by_name_unsafe(p_name TEXT) RETURNS SETOF employees
LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY EXECUTE
        'SELECT * FROM employees WHERE name = ''' || p_name || '''';
END;
$$;
SELECT name, salary FROM find_by_name_unsafe('Ana');
SELECT name, salary FROM find_by_name_unsafe($$nobody' OR '1'='1$$);

$ psql -d sqldemo -f injection.sql
CREATE FUNCTION
 name | salary 
------+--------
 Ana  |  95000
(1 row)

 name | salary 
------+--------
 Ana  |  95000
 Ben  |  60000
(2 rows)

The second “name” closed the quote and appended OR '1'='1', so the query became WHERE name = 'nobody' OR '1'='1' and returned every salary. The same bug exists in any language that builds SQL with string concatenation. Pass the value as a parameter instead.

-- injection_fixed.sql
CREATE FUNCTION find_by_name(p_name TEXT) RETURNS SETOF employees
LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY EXECUTE 'SELECT * FROM employees WHERE name = $1' USING p_name;
END;
$$;
SELECT name, salary FROM find_by_name('Ana');
SELECT count(*) AS injected_rows FROM find_by_name($$nobody' OR '1'='1$$);

$ psql -d sqldemo -f injection_fixed.sql
CREATE FUNCTION
 name | salary 
------+--------
 Ana  |  95000
(1 row)

 injected_rows 
---------------
             0
(1 row)

With USING, the input travels separately from the SQL text, so the database compares name with the literal string nobody' OR '1'='1 and finds nothing. Driver placeholders (%s in psycopg, $1 in node-postgres, ? in JDBC) work the same way.

Security Checklist

Connect applications as a login role with only the privileges they need, never as a superuser. Grant privileges to group roles. Keep tables in a schema owned by a migration role, not by the application role. Use RLS for per-tenant or per-user data, parameters for every value, and the pgaudit extension when you need an audit trail of who ran what.

Practice

1. Let analyst insert new employees but still not read salaries, then test it as app_user.

-- practice_insert_grant.sql
GRANT INSERT ON employees TO analyst;
SET ROLE app_user;
INSERT INTO employees VALUES (3, 'Cai', 'Ops', 50000);
SELECT id, name FROM employees ORDER BY id;
RESET ROLE;

$ psql -d sqldemo -f practice_insert_grant.sql
GRANT
SET
INSERT 0 1
 id | name 
----+------
  1 | Ana
  2 | Ben
  3 | Cai
(3 rows)

RESET

2. Show which privileges analyst holds on employees.

-- practice_list_privs.sql
SELECT privilege_type, column_name
FROM information_schema.column_privileges
WHERE grantee = 'analyst' AND table_name = 'employees'
ORDER BY privilege_type, column_name;

$ psql -d sqldemo -f practice_list_privs.sql
 privilege_type | column_name 
----------------+-------------
 INSERT         | dept
 INSERT         | id
 INSERT         | name
 INSERT         | salary
 SELECT         | dept
 SELECT         | id
 SELECT         | name
(7 rows)

3. Call the unsafe function with a name containing an apostrophe, O'Brien, and compare it with the safe one.

-- practice_apostrophe.sql
SELECT count(*) AS safe_rows FROM find_by_name($$O'Brien$$);
SELECT count(*) AS unsafe_rows FROM find_by_name_unsafe($$O'Brien$$);

$ psql -d sqldemo -f practice_apostrophe.sql
 safe_rows 
-----------
         0
(1 row)

psql:practice_apostrophe.sql:3: ERROR:  syntax error at or near "Brien"
LINE 1: SELECT * FROM employees WHERE name = 'O'Brien'
                                                ^
QUERY:  SELECT * FROM employees WHERE name = 'O'Brien'
CONTEXT:  PL/pgSQL function find_by_name_unsafe(text) line 3 at RETURN QUERY