Using Function with Security Access Control

Introduction

Continuing from the previous article about Function, we will look into how to use it with Security Access Control for efficient privilege management.
Suppose you have a table storing sensitive and critical data like accounts, and you want to enforce access control to restrict regular accounts from accessing or updating account information.
However, you still want to allow users to perform specific operations such as creating an account or updating a password.
In this scenario, you can create a Function with SECURITY DEFINER, allowing users to simply call this Function and pass the corresponding input parameters.

INVOKER and DEFINER

This is an attribute of a function (or procedure) in PostgreSQL.

SECURITY INVOKER is used by default, meaning the function executes with the privileges of the user calling it. It does not exceed the caller's privileges, which reduces security risks.
SECURITY DEFINER specifies that the function will be executed with the privileges of the user who owns it instead of the user who calls it.
- This is a crucial mechanism for delegating specific operations without granting direct privileges on the underlying table.
- It can perform operations that the caller does not have direct permission to do, so it must be written carefully to prevent misuse.

Important Notes When Using SECURITY DEFINER

Only grant EXECUTE permissions to the exact roles that need them.
The function should only execute a specific business logic, such as account registration, password changes or login authentication.
Thoroughly validate all input parameters inside the function.
Configure a fixed search_path to protect against search_path attacks.

Prerequisites

You can review the previous article on account creation and authorization to capture the necessary background context if anything remains unclear.

Detail

First, create the account table and functions as follows:

CREATE TABLE account (
    id SERIAL PRIMARY KEY,
    username TEXT,
    password_hash TEXT
);

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE OR REPLACE FUNCTION create_account(
    p_username TEXT,
    p_password TEXT
)
RETURNS INT
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
    v_account_id INT;
BEGIN
    IF EXISTS (
        SELECT 1
        FROM Account
        WHERE username = p_username
    ) THEN
        RAISE EXCEPTION 'Username already exists.';
    END IF;

    INSERT INTO Account (
        username,
        password_hash
    )
    VALUES (
        p_username,
        crypt(p_password, gen_salt('bf'))
    )
    RETURNING id INTO v_account_id;

    RETURN v_account_id;
END;
$$ LANGUAGE plpgsql

CREATE OR REPLACE FUNCTION change_password(
    p_account_id INT,
    p_old_password TEXT,
    p_new_password TEXT
)
RETURNS BOOLEAN
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM Account
        WHERE id = p_account_id
          AND password_hash = crypt(p_old_password, password_hash)
    ) THEN
        RETURN FALSE;
    END IF;

    UPDATE Account
    SET password_hash = crypt(p_new_password, gen_salt('bf'))
    WHERE id = p_account_id;

    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

The pgcrypto module is an extension that must be activated to use the crypt function.
The create_account function accepts a username and a password, then hashes the password for secure storage.
The change_password function takes an account_id, old_password and new_password, verifies whether the old_password matches the existing password before performing the updates.
Notice that the SECURITY DEFINER portion is what allows the function to execute with the creator's permissions.
The SET search_path = public configuration prevents search_path attacks by ensuring the function only resolves tables and functions within the public schema.

Next, create the role and configure permissions as follows:

CREATE ROLE backend_user LOGIN PASSWORD 'backend_password';

GRANT SELECT ON TABLE account TO backend_user;

GRANT EXECUTE ON FUNCTION create_account(TEXT, TEXT) TO backend_user;
GRANT EXECUTE ON FUNCTION change_password(INT, TEXT, TEXT) TO backend_user;

In the snippet above, I create the backend_user role, which by default has no permissions to perform any actions.
I only grant the SELECT privilege to view data in the account table, meaning it cannot perform INSERT, UPDATE or DELETE operations directly.
To enable account creation and password updates, I grant additional permissions to execute the two corresponding functions: create_account and change_password.

After switching to the new role, you can verify the access behavior as follows:

SET ROLE backend_user;

SELECT * from account
INSERT INTO account(username, password_hash) VALUES ('username', crypt('password', gen_salt('bf')))
UPDATE account SET password_hash = crypt('password', gen_salt('bf')) WHERE id = 1;

SELECT create_account('alice','P@ssw0rd123');
SELECT change_password(1, 'P@ssw0rd123', 'new_password');

Running SELECT returns data normally because the required permission has been granted.
Running INSERT and UPDATE operations directly will fail because those actions are unauthorized.
However, we can successfully utilize the create_account and change_password functions to interact with the account table safely.