Postgre sql disabling accounts

[adyer (Alex Dyer)]

Hi,

We integrated Postgre sql in identitynow, we are trying to disable accounts in the target postgre db when a user status is changed to terminated. To handle this use case we created a rule and deployed it to the connector. Also, we added the source in the provisioning profile. We are getting the following error message while trying to execute a disable operation. We did similar approach for other mysql dbs and it’s working as expected. Below is the code that we are using, can you please advise:

Error:

["org.postgresql.util.PSQLException: ERROR: syntax error at or near \"$1\"\n Position: 12","org.postgresql.util.PSQLException: ERROR: syntax error at or near \"$1\"\n Position: 12"]

Here’s Rule:

import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.PreparedStatement;
 import java.sql.SQLException;
 import java.sql.Types;
  import java.util.List;
 import sailpoint.api.SailPointContext;
 import sailpoint.connector.JDBCConnector;
 import sailpoint.object.Application;
 import sailpoint.object.ProvisioningPlan;
 import sailpoint.object.ProvisioningPlan.AccountRequest;
 import sailpoint.object.ProvisioningPlan.AttributeRequest;
 import sailpoint.object.ProvisioningPlan.PermissionRequest;
 import sailpoint.object.ProvisioningResult;
 import sailpoint.object.Schema;
 public String getAttributeRequestValue(AccountRequest acctReq, String attribute) {
 if ( acctReq != null ) {
 AttributeRequest attrReq = acctReq.getAttributeRequest(attribute);
 if ( attrReq != null ) {
 return attrReq.getValue();
 }
 }
 return null;
 }
  ProvisioningResult result = new ProvisioningResult();
 PreparedStatement statement;
 if ( plan != null ) {
 List accounts = plan.getAccountRequests();
 if ((accounts != null ) && (accounts.size()>0)) {
 for ( AccountRequest account : accounts ) {
 try {
		 if ( AccountRequest.Operation.Disable.equals( account.getOperation() ) ) {
 PreparedStatement statement = connection.prepareStatement( "ALTER USER ? @'%' nologin;" );
 statement.setString (1,(String) account.getNativeIdentity() );
 statement.executeUpdate();
 result.setStatus( ProvisioningResult.STATUS_COMMITTED );
 } else if (AccountRequest.Operation.Enable.equals(account.getOperation())) {
 PreparedStatement statement = connection.prepareStatement("ALTER USER ? @'%' login;");
 statement.setString (1, (String) account.getNativeIdentity() );
 statement.executeUpdate();
 result.setStatus( ProvisioningResult.STATUS_COMMITTED);
 } else {
 // Unknown operation!
 }
 }
 catch( SQLException e ) {
 result.setStatus( ProvisioningResult.STATUS_FAILED );
 result.addError( e );
 }
 finally {
 if(statement != null) {
 statement.close();
 }
 }
 }
 }
 }
 return result;

[iamnithesh (Nithesh Rao)]

@adyer

"ALTER USER ? @'%' nologin;"

Instead try this

"ALTER USER ? nologin;"

[adyer (Alex Dyer)]

Hi Nithesh,

thanks, I tried the above and still getting the same exact error message.

Here’s the rule in escape format that i’m using.

“import java.sql.Connection;\r\n import java.sql.DriverManager;\r\n import java.sql.PreparedStatement;\r\n import java.sql.SQLException;\r\n import java.sql.Types;\r\n import java.util.List;\r\n import sailpoint.api.SailPointContext;\r\n import sailpoint.connector.JDBCConnector;\r\n import sailpoint.object.Application;\r\n import sailpoint.object.ProvisioningPlan;\r\n import sailpoint.object.ProvisioningPlan.AccountRequest;\r\n import sailpoint.object.ProvisioningPlan.AttributeRequest;\r\n import sailpoint.object.ProvisioningPlan.PermissionRequest;\r\n import sailpoint.object.ProvisioningResult;\r\n import sailpoint.object.Schema;\r\n public String getAttributeRequestValue(AccountRequest acctReq, String attribute) {\r\n if ( acctReq != null ) {\r\n AttributeRequest attrReq = acctReq.getAttributeRequest(attribute);\r\n if ( attrReq != null ) {\r\n return attrReq.getValue();\r\n }\r\n }\r\n return null;\r\n }\r\n ProvisioningResult result = new ProvisioningResult();\r\n PreparedStatement statement;\r\n if ( plan != null ) {\r\n List accounts = plan.getAccountRequests();\r\n if ((accounts != null ) && (accounts.size()>0)) {\r\n for ( AccountRequest account : accounts ) {\r\n try {\r\n\t\t if ( AccountRequest.Operation.Disable.equals( account.getOperation() ) ) {\r\n PreparedStatement statement = connection.prepareStatement( "ALTER USER ? nologin;" );\r\n statement.setString (1,(String) account.getNativeIdentity() );\r\n statement.executeUpdate();\r\n result.setStatus( ProvisioningResult.STATUS_COMMITTED );\r\n } else if (AccountRequest.Operation.Enable.equals(account.getOperation())) {\r\n PreparedStatement statement = connection.prepareStatement("ALTER USER ? login;");\r\n statement.setString (1, (String) account.getNativeIdentity() );\r\n statement.executeUpdate();\r\n result.setStatus( ProvisioningResult.STATUS_COMMITTED);\r\n } else {\r\n // Unknown operation!\r\n }\r\n }\r\n catch( SQLException e ) {\r\n result.setStatus( ProvisioningResult.STATUS_FAILED );\r\n result.addError( e );\r\n }\r\n finally {\r\n if(statement != null) {\r\n statement.close();\r\n }\r\n }\r\n }\r\n }\r\n }\r\n return result;”

[adyer (Alex Dyer)]

Hi Nithesh,

I tried the above command and still receiving the same error. Any thoughts?

[iamnithesh (Nithesh Rao)]

@adyer

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"

I would suggest trying these

PreparedStatement statement = connection.prepareStatement( "ALTER USER " + account.getNativeIdentity() + " nologin;" );

OR

PreparedStatement statement = connection.prepareStatement( "ALTER ?::USER nologin;" );
statement.setString (1,(String) account.getNativeIdentity() );

[adyer (Alex Dyer)]

Hi Nithesh,

I tried both approach above and still getting error when attempting to disable the account, any other recommendations.

For first approach, see error:

Second approach, i got this error message:

[gladstoneabrahamr (Gladstone Abraham)]

Hi @adyer,

I think you fixed the issue, from the first approach @iamnithesh suggested,

Can you verify if your disable user code like the follows,

if ( AccountRequest.Operation.Disable.equals( account.getOperation() ) ) {
            PreparedStatement statement = connection.prepareStatement( "ALTER USER " + account.getNativeIdentity() + " nologin;" );
            statement.executeUpdate();
            result.setStatus( ProvisioningResult.STATUS_COMMITTED );
        }

If my guess is right I think the error is based on the setString statement on the next line which should have been removed,

statement.setString (1,(String) account.getNativeIdentity() );

In short the core of the issue is that PostgreSQL’s protocol can only bind placement parameters for “plannable” statements, which are SELECT, INSERT, UPDATE, DELETE and a few others. For these statement types it only supports placement parameters for literal values, not for identifiers or other syntax elements.

For other statement types, literals must be substituted in by the client. Some drivers support client-side emulation of prepare for these statements, so they seem to work transparently, but PgJDBC does not currently support this.

For more details do check this reference here

[iamnithesh (Nithesh Rao)]

@adyer

For first approach, see error:

The error is caused due to statement.setString(1, "......."). You need to comment or remove this line as there are no placeholder(?)s in your PreparedStatement query in first case

[adyer (Alex Dyer)]

Hi @gladstoneabrahamr , that fixed the issue. Thank you for the recommendation.

[adyer (Alex Dyer)]

Hi Nithesh, I tried both approach and the issue was resolved. Thank you.