The routine tried to read tables even though it was declared NO SQL or to run inside SECURITY DEFINER without the needed privileges.
reading_sql_data_not_permitted (SQLSTATE 38004) appears when a PostgreSQL function or procedure marked NO SQL or executed under a role without SELECT rights attempts to read table data. Redefine the routine as READS SQL DATA or grant the caller SELECT privileges to resolve the problem.
reading_sql_data_not_permitted
PostgreSQL raises SQLSTATE 38004 when a routine tries to read table data even though its declared access level or execution role forbids that action. The engine blocks the call to protect data integrity and privilege boundaries.
Most developers see this error inside PL/pgSQL, SQL or C functions, but it can also surface in procedures called by EVENT TRIGGERs or from extensions.
Fixing it quickly keeps business logic stable and prevents broken application flows.
The main trigger is a function created with the NO SQL attribute or the stricter MODIFIES SQL DATA flags in SQL-standard syntax. When that function executes a SELECT, PostgreSQL throws 38004 immediately.
A second trigger is SECURITY DEFINER routines.
If the definer lacks SELECT on the referenced tables or the current user has weaker rights, any data read fails with reading_sql_data_not_permitted.
Event triggers that fire under the pg_event_trigger role but call helper functions needing table reads can also generate the error.
Change the routine signature to allow data access: replace NO SQL with READS SQL DATA or drop the SQL-standard clause entirely.
PostgreSQL then permits SELECT statements.
Alternatively grant the executing role SELECT on each referenced table. SECURITY DEFINER functions benefit from giving the creator or designated role broader rights.
Refactor business logic into two layers: a truly NO SQL wrapper and a data-reading helper. This pattern maintains the standard while unblocking data access.
PL/pgSQL functions generated by ORMs sometimes default to NO SQL.
Regenerating them with READS SQL DATA clears the error.
Extension upgrade scripts that call internal helper functions need temporary privilege elevation. Execute ALTER FUNCTION ... SECURITY DEFINER to let them read metadata safely.
When Galaxy’s AI copilot refactors code, verify that the created function includes the correct SQL data access level to avoid 38004 in production.
Always declare the lightest permissible SQL access level but avoid NO SQL if the routine will ever SELECT.
Use READS SQL DATA for read-only logic and MODIFIES SQL DATA for write operations.
Automate privilege auditing with tools or scripts that ensure SECURITY DEFINER owners hold the required rights. Galaxy’s query history makes it easy to review and endorse safe function versions.
38002 modifying_sql_data_not_permitted appears when a NO SQL routine tries to INSERT, UPDATE or DELETE.
The fix mirrors 38004: adjust the attribute or privileges.
38003 prohibited_sql_statement_attempted is raised when a routine marked READS SQL DATA tries to modify data. Upgrade the declaration to MODIFIES SQL DATA.
42501 insufficient_privilege triggers when the user lacks basic table rights outside of function context; granting privileges solves it.
.
These attributes let the planner apply safe optimizations and ensure routines keep declared side-effect guarantees. Violations trigger SQLSTATE 38004 to protect data integrity.
No. The SQL standard requires enforcement. You must adjust the routine definition or privileges.
Usually no. The attribute only informs the planner; it does not add overhead. Correct classification improves clarity.
Galaxy’s linting warns when a function marked NO SQL contains SELECT statements. Its AI copilot suggests the proper attribute before you run the code.
.avif)
.avif){kind=logo}
