Recieving a HibernateJdbcException error with a custom script I cannot resolve

[Jeremy Jedlicka]

I am writing a script that will eventually become a job designed to remove all permissions of users who no longer have a Confluence license. The script works great when I feed it usernames directly. However when I pull all inactive users via a SQL search I get the error: JDBC exception on Hibernate data access: SQLException for SQL [n/a]; SQL state [25006]; error code [0]; could not execute statement. ERROR: cannot execute DELETE in a read-only transaction.

The SQL search performs fine, and the error happens on the 

spacePermissionManager.removePermission action. What it seems to me is happening is that the SQL search I perform to get the inactive users is still "open" and so a second SQL put cannot write to the permissions table.  If this is the case, how do I close the SQL search after I get the data I want?

Here is the code I have written (sorry for the lack of comments, but it's not too complicated)

import com.atlassian.confluence.spaces.SpaceManager
import com.onresolve.scriptrunner.db.DatabaseUtil
import com.atlassian.user.User
import com.atlassian.confluence.spaces.Space
import com.atlassian.sal.api.component.ComponentLocator
import com.atlassian.confluence.security.SpacePermission
import com.atlassian.confluence.security.SpacePermissionManager
import com.atlassian.confluence.internal.security.SpacePermissionContext

def spacePermissionManager = ComponentLocator.getComponent(SpacePermissionManager)
def spaceManager = ComponentLocator.getComponent(SpaceManager)
SpacePermissionContext spaceContext = SpacePermissionContext.createDefault()

//def spaces = ComponentLocator.getComponent(SpaceManager).getAllSpaces()
def spaces = ComponentLocator.getComponent(SpaceManager).getSpace("GCMFX")
def inactiveUsers = []
def sqlPull = []
def spacePermissions = []
def permissionsToRemove = []

DatabaseUtil.withSql('test local connection') { sql ->
        sqlPull = sql.rows("""
        SELECT user_name
        FROM cwd_user
        WHERE active = 'F';
        """)
    }

def spaceUsers = sqlPull*.user_name

//return spaceUsers

spaces.each { space ->
    for (p in space.getPermissions()) {
        //log.warn(p.getUserSubject()?.name)
        if(spaceUsers.find{ it == p.getUserSubject()?.name } && p.type != "SETSPACEPERMISSIONS"){
            //log.warn(p.getUserSubject()?.name)
            permissionsToRemove += p
        }
    }
    log.warn(permissionsToRemove)

    permissionsToRemove.each{ permission ->
        spacePermissionManager.removePermission(permission, spaceContext)
    }  
}

[Jeremy Jedlicka · Accepted solution]

The solution to this is that the script console cannot execute read and read-write executions to the database, but a Scheduled job can.  Simply adding the above script to a job instead of the console worked.

[Brant Schroeder]

@Jeremy Jedlicka Does the account running the script have the proper permissions to perform the delete?

[Jeremy Jedlicka]

Yes.  I am a system admin. The above script works fine when I comment out the database.Util and set spaceUsers to a defined list.  It also works, for some reason, without commenting out the database.Util and setting spaceUsers to a single user.

[Brant Schroeder]

Is this in a test instance?  Is the DB set to readonly by default? When you update a single user is it applying in the DB?

[Jeremy Jedlicka]

Yes, I'm working on this code in our Test instance.  The database is not set to readonly, and when I update a single user the database is updated correctly.

[Jeremy Jedlicka]

I solved this with a little help from ChatGPT. I'll post it as it's own solution