PostgresDB 38002: Modifying SQL Data Not Permitted

Modifying SQL data is not allowed in the external routine.

When encountering the error "38002: Modifying SQL Data Not Permitted" in a Postgres DB, the user should immediately:

  1. Check the Current Transaction Read-Only Status:
  2. Run the following SQL command to determine if the transaction is set to read-only, which would prevent data modification:
  3. SHOW transaction_read_only;
  4. If the result is on, modifications are not permitted in the current transaction.
  5. Check the Database/Role/User Read-Only Settings:
  6. Investigate if the database or user role has been set to read-only. Execute:
  7. SELECT datname, datallowconn, datistemplate, datconnlimit, datacl, pg_catalog.array_to_string(datacl, ',') AS permissions
    FROM pg_catalog.pg_database
    WHERE datname = current_database();
  8. And for the user/role settings:
  9. SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolconnlimit, rolvaliduntil, rolbypassrls, pg_catalog.array_to_string(rolconfig, ',') AS settings
    FROM pg_catalog.pg_roles
    WHERE rolname = current_user;
  10. These queries help identify if the database or the current user role has been restricted.
  11. Check for Trigger or Rule-Based Restrictions:
  12. Sometimes, a trigger or rule on the table could prevent data modifications. To inspect for any such triggers or rules:
  13. For triggers:
  14. SELECT event_object_table AS table_name, trigger_name
    FROM information_schema.triggers
    WHERE event_manipulation = 'UPDATE' OR event_manipulation = 'INSERT' OR event_manipulation = 'DELETE';
  15. For rules:
  16. SELECT tablename, rulename
    FROM pg_rules
    WHERE ev_type = '1' OR ev_type = '2' OR ev_type = '3'; -- 1: SELECT, 2: UPDATE, 3: INSERT, 4: DELETE
  17. Check Current Session's Role Attributes:
  18. It's also possible that the current session's role has been set in a way that prevents data modification. Check the session's role attributes:
  19. SELECT rolname, rolconfig
    FROM pg_roles
    JOIN pg_auth_members ON (pg_roles.oid = pg_auth_members.roleid)
    JOIN pg_roles as role2 ON (pg_auth_members.member = role2.oid)
    WHERE role2.rolname = current_user;

If after these actions the issue persists, the user might need to adjust the transaction mode to allow write operations (if that’s in their control and aligns with their intent) or contact a database expert for further assistance.

Master

PostgresDB

in Minutes — Grab the Ultimate Cheatsheet

(Perfect for DevOps & SREs)

Most-used commands
Real-world configs/examples
Handy troubleshooting shortcuts
Your email is safe with us. No spam, ever.

Thankyou for your submission

We have sent the cheatsheet on your email!
Oops! Something went wrong while submitting the form.

PostgresDB

Cheatsheet

(Perfect for DevOps & SREs)

Most-used commands
Your email is safe with us. No spam, ever.

Thankyou for your submission

We have sent the cheatsheet on your email!
Oops! Something went wrong while submitting the form.

MORE ISSUES

Made with ❤️ in Bangalore & San Francisco 🏢

Doctor Droid