PostgreSQL Trigger Security

Introduction

Triggers are powerful mechanisms in PostgreSQL that automatically execute functions in response to table or view events. They can centralize business logic, enforce invariants, and build audit trails — but they also introduce serious security considerations.

Because trigger functions run inside the database backend and may execute with elevated privileges, a mistake here is an easy way to create a Trojan horse or a privilege-escalation path. PostgreSQL’s own docs explicitly call out functions and triggers as such a risk and recommend tight control over who can define them. (Postgres Pro)

This guide focuses on security-relevant behavior of triggers in current PostgreSQL (v18), how they run, common pitfalls, and safer patterns for production systems.

---

How Trigger Execution Really Works

Execution context and roles

In modern PostgreSQL, a trigger does not “run as the user who created the trigger”.

The key rule from the PostgreSQL 18 documentation is:

“The trigger will always run as the role that queued the trigger event, unless the trigger function is marked as SECURITY DEFINER, in which case it will run as the function owner.” (PostgreSQL)

Translated:

• For a normal data change trigger (SECURITY INVOKER, the default):

• The trigger function runs with the privileges of the role that executed the SQL statement that caused the trigger event (the queuing role).

• If the trigger function is declared SECURITY DEFINER:

• It runs with the privileges of the function owner, regardless of which role fired the trigger.

This is critical when you design triggers that touch tables ordinary users cannot access directly.

-- Example: simple BEFORE UPDATE trigger

CREATE TABLE sensitive_data (
  id            serial PRIMARY KEY,
  data          text,
  last_modified timestamptz
);

CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS trigger AS $$
BEGIN
  NEW.last_modified := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;  -- SECURITY INVOKER (default)

CREATE TRIGGER set_last_modified
BEFORE UPDATE ON sensitive_data
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

• If role app_user issues UPDATE sensitive_data ..., the trigger function runs as app_user.
• If we later mark the function as SECURITY DEFINER and the owner is db_admin, then the very same UPDATE will execute the function as db_admin, even though app_user fired the trigger.

Trigger functions vs. triggers

• Trigger functions are just functions with RETURNS trigger (or RETURNS event_trigger) and must be created before the trigger itself. (PostgreSQL)
• Triggers reference these functions and specify when to run them (BEFORE/AFTER/INSTEAD OF, row- or statement-level, which table/view, etc.). (PostgreSQL)

Security properties like SECURITY DEFINER are attached to the function, but they strongly affect how the trigger behaves.

---

Security Risks with Triggers

1. Privilege escalation

If you mark a trigger function SECURITY DEFINER, every user who can cause the trigger to fire can indirectly perform whatever operations that function performs, with the full privileges of the function owner.

-- Example: audit log trigger with potential escalation

CREATE TABLE admin_audit_log (
  id          bigserial PRIMARY KEY,
  table_name  text,
  operation   text,
  user_name   text,
  changed_at  timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION log_data_changes()
RETURNS trigger AS $$
BEGIN
  INSERT INTO admin_audit_log (table_name, operation, user_name, changed_at)
  VALUES (TG_TABLE_NAME, TG_OP, current_user, now());

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;  -- SECURITY INVOKER (safe *if* caller can INSERT here)

CREATE TRIGGER log_updates
AFTER INSERT OR UPDATE OR DELETE ON some_table
FOR EACH ROW
EXECUTE FUNCTION log_data_changes();

If some_table is writable by regular users but admin_audit_log is only writable by admins, and you later change log_data_changes() to SECURITY DEFINER with owner admin, then any user who can modify some_table can now indirectly insert rows into admin_audit_log as admin. That might be OK if it’s deliberate and carefully constrained, or a serious privilege-escalation vector if done carelessly. (Postgres Pro)

2. Data leakage

Triggers can leak sensitive data to:

• Server logs (RAISE NOTICE, RAISE EXCEPTION)
• Unprotected audit tables
• Notification channels (NOTIFY, messaging bridges)
• External functions (e.g., calling HTTP from extensions)

-- Risky trigger that leaks sensitive data into logs
CREATE OR REPLACE FUNCTION notify_data_change()
RETURNS trigger AS $$
BEGIN
  RAISE NOTICE 'Sensitive data changed: %', NEW.sensitive_field; -- leaks value
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Anyone with access to PostgreSQL logs or error reports might see the sensitive values.

3. Policy bypass and RLS interactions

Triggers can:

• Read and write tables that row-level security (RLS) would normally protect.
• Use SECURITY DEFINER to bypass RLS (because RLS is evaluated using the active role’s privileges).
• Perform side effects that ignore application-level authorization logic.

This is sometimes intentional — for example, a central audit trigger that must see all rows — but it needs to be clearly designed and reviewed.

---

Designing Secure Triggers

Prefer explicit application logic when possible

Before you add a trigger, ask:

• Could this logic live in the application or in a stored procedure the app calls explicitly?
• Does it really need to be automatic and hidden?

Trigger-based behavior is easy to forget and hard to reason about, especially when debugging security issues. Use triggers when they add real value (auditing, cross-table integrity, etc.), not just out of habit.

Principle of least privilege

Some practical rules:

1. Limit who can create functions and triggers. PostgreSQL’s function and trigger security docs explicitly note that untrusted users can “Trojan horse” others with custom code. (Postgres Pro)

2. Keep function owners narrowly privileged.

• If you must have a SECURITY DEFINER function, create a small, dedicated owner role that has only the specific privileges required for that function.
• Do not use your superuser or broadly privileged admin role as the function owner.

3. Prefer SECURITY INVOKER whenever you can.

• Start with the default behavior.
• Only switch to SECURITY DEFINER when there’s a clear, well-documented need.

4. Be cautious with SET ROLE and SET SESSION AUTHORIZATION.

• These can be used inside functions, but they broaden the attack surface and complicate reasoning about who has which privileges when.
• Many “least privilege with SECURITY DEFINER + SET ROLE” patterns are fragile and easy to misconfigure.
• Guidance from security-focused articles on PostgreSQL specifically calls out careless use of SECURITY DEFINER and role switching as a privilege-escalation risk. (mssql.fr)

In practice, a safer pattern is:

• Dedicated function owner role with minimal grants
• No dynamic SET ROLE inside the function
• Very small, well-audited function body

SECURITY DEFINER vs SECURITY INVOKER

For trigger functions you should internalize:

If you decide to use SECURITY DEFINER:

• Set a safe search_path at function creation time to avoid attacks via injected schemas (e.g. SET search_path = pg_catalog, public or a more restricted path). (Postgres Pro)
• Avoid executing dynamic SQL built from untrusted input.
• Keep the function short and focused on a single task.
• Document clearly why it needs elevated privileges.

---

Using Triggers with Row-Level Security (RLS)

Triggers and RLS can complement each other, but you need a clear mental model.

-- Table with RLS enabled
CREATE TABLE customer_data (
  id          serial PRIMARY KEY,
  customer_id integer NOT NULL,
  data        text
);

ALTER TABLE customer_data ENABLE ROW LEVEL SECURITY;

-- RLS policy based on an application-set parameter
CREATE POLICY customer_data_policy ON customer_data
  USING (customer_id = current_setting('app.current_customer_id')::integer);

Now add a trigger that enforces additional business rules beyond the RLS policy:

CREATE TABLE user_customers (
  username    text    NOT NULL,
  customer_id integer NOT NULL,
  PRIMARY KEY (username, customer_id)
);

CREATE OR REPLACE FUNCTION validate_customer_access()
RETURNS trigger AS $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM user_customers
    WHERE username = current_user
      AND customer_id = NEW.customer_id
  ) THEN
    RAISE EXCEPTION 'Not authorized to modify this customer data';
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;  -- SECURITY INVOKER

CREATE TRIGGER check_customer_access
BEFORE INSERT OR UPDATE ON customer_data
FOR EACH ROW
EXECUTE FUNCTION validate_customer_access();

Notes:

• Because the function is SECURITY INVOKER, it uses the same role as the queuing statement, and RLS applies as usual.
• The trigger adds extra checks, but doesn’t bypass the RLS policy.

If you changed this function to SECURITY DEFINER and gave the owner broader privileges (e.g., BYPASSRLS), the trigger could see or modify rows that normal users can’t — which might be what you want in an audit or system-maintenance context, but is dangerous if accidental.

---

A Safer Audit Trigger Pattern

Auditing is a legitimate use case for triggers, but it’s also a high-value target for attackers. Let’s build a pattern that:

• Captures old and new row states
• Attributes changes to the correct user
• Uses SECURITY DEFINER intentionally and minimally

-- Audit table
CREATE TABLE audit_trail (
  id          bigserial PRIMARY KEY,
  table_name  text        NOT NULL,
  operation   text        NOT NULL,
  record_id   bigint,
  old_data    jsonb,
  new_data    jsonb,
  changed_by  text        NOT NULL,
  changed_at  timestamptz NOT NULL DEFAULT now()
);

-- Dedicated owner role for the audit function
-- (run as superuser/DBA when creating roles)
CREATE ROLE audit_owner NOLOGIN;
GRANT INSERT ON audit_trail TO audit_owner;

-- Create function owned by audit_owner
SET ROLE audit_owner;

CREATE OR REPLACE FUNCTION audit_changes()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  v_record_id bigint;
  v_old       jsonb := NULL;
  v_new       jsonb := NULL;
BEGIN
  IF TG_OP = 'DELETE' THEN
    v_record_id := OLD.id;
    v_old := to_jsonb(OLD);
  ELSIF TG_OP = 'UPDATE' THEN
    v_record_id := NEW.id;
    v_old := to_jsonb(OLD);
    v_new := to_jsonb(NEW);
  ELSE  -- INSERT
    v_record_id := NEW.id;
    v_new := to_jsonb(NEW);
  END IF;

  INSERT INTO audit_trail (
    table_name, operation, record_id,
    old_data, new_data, changed_by, changed_at
  )
  VALUES (
    TG_TABLE_NAME, TG_OP, v_record_id,
    v_old, v_new, current_user, now()
  );

  -- AFTER ROW trigger: return value is ignored, but NULL is conventional
  RETURN NULL;
END;
$$;

RESET ROLE;

Attach it to a table:

CREATE TABLE users (
  id   serial PRIMARY KEY,
  name text NOT NULL
);

CREATE TRIGGER audit_users_changes
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION audit_changes();

Why this is relatively safe (for a SECURITY DEFINER function):

• audit_owner has only the privileges needed to insert into audit_trail.
• The function itself does a very specific, non-dynamic operation.
• search_path is set to a known value to avoid calling attacker-controlled functions via search-path hijacking. (Postgres Pro)

You still need to:

• Protect who can ALTER FUNCTION audit_changes or DROP it.
• Consider whether storing full row data (including sensitive fields) is acceptable.

---

Securing Sensitive Data in Triggers

When triggers touch sensitive information (PII, card data, secrets), apply the same data-minimization principles you’d use elsewhere.

Example: mask card numbers and drop CVV before persisting anything:

CREATE TABLE payment_info (
  id           serial PRIMARY KEY,
  customer_id  integer NOT NULL,
  credit_card  text    NOT NULL,
  cvv          text    NOT NULL,
  other_data   jsonb
);

CREATE OR REPLACE FUNCTION process_payment_data()
RETURNS trigger AS $$
DECLARE
  masked_card text;
BEGIN
  -- Keep only last 4 digits
  masked_card := 'XXXX-XXXX-XXXX-' || right(NEW.credit_card, 4);

  NEW.credit_card := masked_card;
  NEW.cvv := NULL;  -- never store CVV

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER secure_payment_data
BEFORE INSERT OR UPDATE ON payment_info
FOR EACH ROW
EXECUTE FUNCTION process_payment_data();

This way, even if:

• Someone dumps the payment_info table, or
• The audit trigger logs the row in JSON form

they never see raw card numbers or CVVs.

---

Testing Trigger Security

Don’t just test that triggers “work” — test that they enforce the right security boundaries.

Example test scenario

-- Table to secure
CREATE TABLE test_secured (
  id   serial PRIMARY KEY,
  data text
);

-- Reuse the audit_changes() function from above, or another audit function
CREATE TRIGGER audit_test_secured
AFTER INSERT OR UPDATE OR DELETE ON test_secured
FOR EACH ROW
EXECUTE FUNCTION audit_changes();

-- Application role
CREATE ROLE regular_user LOGIN PASSWORD '...';

GRANT SELECT, INSERT, UPDATE, DELETE ON test_secured TO regular_user;
GRANT USAGE, SELECT ON SEQUENCE test_secured_id_seq TO regular_user;

-- As admin/owner:
INSERT INTO test_secured (data) VALUES ('admin row');

-- As regular_user:
-- \c - regular_user
INSERT INTO test_secured (data) VALUES ('user row');
UPDATE test_secured SET data = 'updated by user' WHERE id = 1;
DELETE FROM test_secured WHERE id = 2;

-- Back as admin, inspect audit trail:
SELECT * FROM audit_trail WHERE table_name = 'test_secured' ORDER BY id;

Check that:

• Every change appears once in audit_trail.
• changed_by correctly shows the actual current_user (e.g. regular_user vs. admin).
• The trigger doesn’t allow regular_user to touch tables they shouldn’t.

For debugging tricky permission issues, you can temporarily enable more verbose logging:

SET log_min_messages = DEBUG1;
-- Run test statements
RESET log_min_messages;

Then inspect the server logs for trigger behavior, errors, or unexpected context.

---

Troubleshooting Common Security Issues

1. Permission errors from inside trigger functions

• Symptom: “permission denied for table X” even though the calling user can touch the original table.

• Likely cause: Trigger function (especially SECURITY DEFINER) or its owner doesn’t have the right grants, or RLS is blocking it.

• Fix:

• Check function owner and SECURITY DEFINER flag (\df+ in psql).

• Check table grants and RLS policies for the effective role.

2. Unexpected data modifications

• Symptom: Updates change fields “by themselves”, or rows disappear.

• Likely cause: BEFORE triggers modifying NEW or returning NULL to skip rows; cascading triggers firing additional operations.

• Fix:

• Review all triggers on the table (\dS+ table_name).

• Temporarily disable triggers for debugging in a test environment (never in production without a plan).

3. Performance problems from heavy security checks

• Symptom: Simple writes become slow after security-related triggers are added.

• Likely cause: Expensive lookups or cross-table checks in per-row triggers; unnecessary work in AFTER triggers.

• Fix:

• Move complex checks into statement-level triggers or batch jobs where possible.

• Use WHEN clauses on triggers to reduce how often they fire. (PostgreSQL)

---

Summary

Key points to take away:

1. Triggers run as the role that queued the event, not “the trigger creator” — unless the trigger function is SECURITY DEFINER, in which case it runs as the function owner. (PostgreSQL)
2. Functions and triggers are powerful but dangerous; PostgreSQL docs explicitly treat them as a Trojan-horse risk if untrusted users can define them. (Postgres Pro)
3. Prefer SECURITY INVOKER. Use SECURITY DEFINER only when strictly necessary, with:

• Dedicated, minimally privileged owner roles
• Safe search_path
• Short, audited function bodies

4. Be careful about data leakage in logs, audit tables, or notifications.
5. Combine RLS and triggers deliberately, and understand when the trigger is running with elevated privileges.
6. Test triggers with multiple roles to verify that they enforce, not bypass, your intended security model.

---

Additional Resources and Exercises

Resources

• PostgreSQL 18 Documentation — Trigger behavior (section “Overview of Trigger Behavior”). (PostgreSQL)
• PostgreSQL Documentation — Trigger functions in PL/pgSQL. (PostgreSQL)
• PostgreSQL Documentation — Function and trigger security (older but still conceptually relevant). (Postgres Pro)
• Securing PostgreSQL Against Injection, Misconfiguration, and Privilege Escalation (Redgate / Simple Talk) — broader security guidance and discussion of SECURITY DEFINER risks. (mssql.fr)

Exercises

1. Secure audit with masking Create an audit trigger that logs all changes to a table but automatically masks any columns whose name contains password, secret, or token.

2. RLS + trigger validation Implement RLS on a multi-tenant table and add a trigger that enforces an additional constraint (e.g., only certain roles can update specific columns).

3. SECURITY DEFINER hardening Write a small SECURITY DEFINER trigger function that needs slight elevation (e.g., inserting into a central audit table).

• Create a dedicated owner role with the minimum required privileges.
• Set a safe search_path.
• Document and test the behavior with different users.

4. Abuse scenario Intentionally write an unsafe SECURITY DEFINER trigger function in a test database that allows a low-privileged user to escalate their abilities (for example, writing to a sensitive table). Then:

• Demonstrate the exploit.
• Fix it by redesigning the function and its owner privileges.

5. Performance profiling Add a heavy security check inside a per-row trigger (e.g., a subquery that scans a large table). Measure performance, then refactor the check to a more efficient design (e.g., statement-level trigger + index or denormalized lookup).

This MDX article is structured so you can drop it directly into a docs site, and it reflects the current PostgreSQL behavior around trigger execution and security.