PostgresDB 38002: Modifying SQL Data Not Permitted
Modifying SQL data is not allowed in the external routine.
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Stuck? Let AI directly find root cause
AI that integrates with your stack & debugs automatically | Runs locally and privately
Download Now
What is
PostgresDB 38002: Modifying SQL Data Not Permitted
?
When encountering the error "38002: Modifying SQL Data Not Permitted" in a Postgres DB, the user should immediately:
- Check the Current Transaction Read-Only Status:
- Run the following SQL command to determine if the transaction is set to read-only, which would prevent data modification:
SHOW transaction_read_only;- If the result is
on, modifications are not permitted in the current transaction. - Check the Database/Role/User Read-Only Settings:
- Investigate if the database or user role has been set to read-only. Execute:
SELECT datname, datallowconn, datistemplate, datconnlimit, datacl, pg_catalog.array_to_string(datacl, ',') AS permissions
FROM pg_catalog.pg_database
WHERE datname = current_database();- And for the user/role settings:
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;- These queries help identify if the database or the current user role has been restricted.
- Check for Trigger or Rule-Based Restrictions:
- Sometimes, a trigger or rule on the table could prevent data modifications. To inspect for any such triggers or rules:
- For triggers:
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';- For rules:
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- Check Current Session's Role Attributes:
- 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:
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.
Attached error:
PostgresDB 38002: Modifying SQL Data Not Permitted
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
PostgresDB
80+ monitoring tool integrations
Long term memory about your stack
Locally run Mac App available
Oops! Something went wrong while submitting the form.
Time to stop copy pasting your errors onto Google!
PostgresDB
80+ monitoring tool integrations
Long term memory about your stack
Locally run Mac App available
Oops! Something went wrong while submitting the form.
Oops! Something went wrong while submitting the form.
Time to stop copy pasting your errors onto Google!
MORE ISSUES
Backed by
SOC 2 Type II
certifed
certifed
ISO 27001
certified
certified
Deep Sea Tech Inc. — Made with ❤️ in Bangalore & San Francisco 🏢