PostgreSQL 38004 Error: Causes and Solutions Complete Guide

PostgreSQL Error 38004: Reading SQL Data Not Permitted

PostgreSQL error code 38004 (reading_sql_data_not_permitted) occurs when a function or procedure attempts to read SQL data in a context where such access is explicitly forbidden. This error belongs to the external_routine_exception class (38xxx) and is enforced by PostgreSQL's internal security and access control mechanisms. It most commonly surfaces when function attributes, ownership privileges, or procedural language permissions are misconfigured.

---

Top 3 Causes and Fixes

1. Function Declared with Incorrect Data Access Attribute

When a function is defined with NO SQL or an incompatible data access level but internally executes a SELECT statement, PostgreSQL blocks the operation and throws 38004. This mismatch between declaration and implementation is a common pitfall when migrating from other database systems like MySQL or Oracle.

Fix:

-- Problematic function (NO SQL declared but SELECT used internally)
CREATE OR REPLACE FUNCTION get_total_orders()
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- This will conflict if function context disallows SQL reads
    RETURN (SELECT COUNT(*) FROM orders);
END;
$$;

-- Corrected function with proper declaration
CREATE OR REPLACE FUNCTION get_total_orders()
RETURNS INTEGER
LANGUAGE plpgsql
STABLE  -- Indicates the function reads but does not modify data
AS $$
DECLARE
    v_total INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_total FROM orders;
    RETURN v_total;
END;
$$;

-- Verify function properties
SELECT proname, provolatile, prosecdef, lanname
FROM pg_proc p
JOIN pg_language l ON p.prolang = l.oid
WHERE proname = 'get_total_orders';

---

2. SECURITY DEFINER Function Without Proper Privileges

A SECURITY DEFINER function runs with the privileges of its owner, not the caller. If the function owner lacks SELECT privileges on the referenced tables, or if Row-Level Security (RLS) policies block the owner's access, error 38004 is raised. This is especially tricky in environments where ownership and role assignments are managed separately.

Fix:

-- Create SECURITY DEFINER function safely
CREATE OR REPLACE FUNCTION get_user_profile(p_user_id INT)
RETURNS TABLE(username TEXT, email TEXT)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public  -- Always set search_path for security
AS $$
BEGIN
    RETURN QUERY
    SELECT u.username, u.email
    FROM users u
    WHERE u.id = p_user_id;
END;
$$;

-- Grant SELECT to the function owner
GRANT SELECT ON TABLE users TO func_owner_role;

-- Check if RLS is blocking access
SELECT tablename, rowsecurity FROM pg_tables WHERE tablename = 'users';

-- Add RLS policy for function owner if needed
CREATE POLICY func_owner_read_policy
ON users
FOR SELECT
TO func_owner_role
USING (true);

-- Audit all SECURITY DEFINER functions
SELECT n.nspname, p.proname, pg_get_userbyid(p.proowner) AS owner
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE p.prosecdef = true
  AND n.nspname NOT IN ('pg_catalog', 'information_schema');

---

3. Untrusted Procedural Language Restrictions

Functions written in untrusted languages like plpythonu or plperlu require superuser privileges to create and execute. In managed cloud environments (AWS RDS, Google Cloud SQL, Azure Database for PostgreSQL), superuser access is restricted, causing SQL read operations inside these functions to be blocked.

Fix:

-- Instead of untrusted plpythonu, use trusted plpgsql
-- Avoid this pattern in restricted environments:
-- CREATE FUNCTION fetch_name() RETURNS TEXT LANGUAGE plpythonu AS $$
-- import plpy
-- rv = plpy.execute("SELECT name FROM products LIMIT 1")
-- return rv[0]['name']
-- $$;

-- Use plpgsql instead (trusted and widely supported)
CREATE OR REPLACE FUNCTION fetch_name()
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    v_name TEXT;
BEGIN
    SELECT name INTO v_name FROM products LIMIT 1;
    RETURN v_name;
END;
$$;

-- Check installed languages and their trust status
SELECT lanname, lanpltrusted, lanacl
FROM pg_language
WHERE lanname IN ('plpgsql', 'plpython3u', 'plperlu', 'plperl');

-- Grant language usage to application roles
GRANT USAGE ON LANGUAGE plpgsql TO app_role;

---

Quick Prevention Tips

Standardize function templates: Always define functions with explicit SECURITY INVOKER (default), a pinned search_path, and an appropriate volatility marker (STABLE, IMMUTABLE, or VOLATILE). This reduces misconfiguration risk significantly.

-- Recommended function creation template
CREATE OR REPLACE FUNCTION myschema.my_function(p_id INT)
RETURNS TEXT
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = myschema
STABLE
AS $$
DECLARE
    v_result TEXT;
BEGIN
    SELECT col INTO v_result FROM my_table WHERE id = p_id;
    RETURN v_result;
END;
$$;

Schedule regular privilege audits: Run automated checks on function ownership and privileges to catch permission drift before it causes production issues.

-- Weekly audit query for function security review
SELECT
    n.nspname AS schema,
    p.proname AS function,
    pg_get_userbyid(p.proowner) AS owner,
    p.prosecdef AS security_definer,
    l.lanname AS language,
    p.proacl AS acl
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
JOIN pg_language l ON p.prolang = l.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY p.prosecdef DESC, n.nspname;

---

Related Error Codes

Code	Name	Description
38000	external_routine_exception	Parent class for all 38xxx errors
38001	containing_sql_not_permitted	SQL not allowed in this context
38002	modifying_sql_data_not_permitted	Write operations blocked (counterpart to 38004)
42501	insufficient_privilege	General privilege error, often co-occurs with 38004

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.