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.
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Master
PostgresDB
debugging in Minutes
— Grab the Ultimate Cheatsheet
(Perfect for DevOps & SREs)
Most-used commands
Real-world configs/examples
Handy troubleshooting shortcuts
Oops! Something went wrong while submitting the form.
PostgresDB
Cheatsheet
(Perfect for DevOps & SREs)
Most-used commands
Oops! Something went wrong while submitting the form.
MORE ISSUES
Backed by
Deep Sea Tech Inc. — Made with ❤️ in Bangalore & San Francisco 🏢