How to diagnose and fix the 38001 containing_sql_not_permitted error code in Postgres. 

by

The 38001 error code in PostgreSQL, which stands for containing_sql_not_permitted, typically occurs when you’re working with external routines or functions, such as those written in PL/Python or PL/Perl, and you attempt to execute an SQL command that is not allowed within that context. This is a security restriction to prevent unauthorized operations from within these routines.

To diagnose and fix a 38001 error, follow these steps:

  1. Identify the Routine: Determine which external routine is throwing the error. Review the code of this routine to understand what SQL operations it is trying to perform.
  2. Review Permissions: Ensure that the user-defined function or the external routine has the appropriate permissions to perform the SQL operations it is attempting. In some cases, the security context under which the routine executes may need to be adjusted.
  3. Adjust Routine Code: Modify the routine to avoid the disallowed SQL operation. This might involve changing the logic of the routine or using different SQL commands that are permitted.
  4. Use SECURITY DEFINER: If the routine must perform the disallowed operation and it is safe to do so, you can define the function with the SECURITY DEFINER clause, which allows it to run with the privileges of the user who created it. However, this should be done with caution, as it can introduce security risks if misused.

Here are some examples and sample code to illustrate:

Example 1: Disallowed SELECT within PL/Python

CREATE OR REPLACE FUNCTION my_python_function()
RETURNS void AS $$
# container: plc_python_shared
import postgresql
plan = postgresql.prepare("SELECT * FROM my_table")
$$ LANGUAGE plpythonu;

This might raise a 38001 error because it attempts to perform a SELECT operation within a PL/Python function that is not permitted. To fix this, you could remove the SELECT operation or ensure that the external language handler is configured to allow such operations.

Example 2: Using SECURITY DEFINER

If the function needs to perform a disallowed operation and you’re certain it’s safe, you can use SECURITY DEFINER like so:

CREATE OR REPLACE FUNCTION my_security_definer_function()
RETURNS void SECURITY DEFINER AS $$
BEGIN
    -- Your SQL operations here
END;
$$ LANGUAGE plpgsql;

This function will now execute with the privileges of the user who created it, potentially bypassing the 38001 error.

Remember, before applying SECURITY DEFINER, review the security implications carefully. It’s essential to ensure that the function does not expose any security vulnerabilities, such as SQL injection or privilege escalation.

For more details on PostgreSQL error codes, including 38001, you can refer to the PostgreSQL Error Codes documentation.

Leave a Comment