When encountering the error "38002: Modifying SQL Data Not Permitted" in a Postgres DB, the user should immediately:
SHOW transaction_read_only;
on, modifications are not permitted in the current transaction.SELECT datname, datallowconn, datistemplate, datconnlimit, datacl, pg_catalog.array_to_string(datacl, ',') AS permissions
FROM pg_catalog.pg_database
WHERE datname = current_database();
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;
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';
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
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.
(Perfect for DevOps & SREs)
(Perfect for DevOps & SREs)
