Tuesday, February 11, 2020

Retrieve records from Amazon RDS through Salesforce Apex class

Motivation behind this


I was doing a proof-of-concept on retrieving records from Amazon RDS (Relational Database Service) where I have faced critical challenges in authorization mechanism with AWS Signature version 4 signing process which motivates me to write this post.

Usually, most of the cases I have come across either posting or retrieving files to/from AWS S3. But there can be much more.

Let's start with a use case.

Use Case


Business has a requirement to view the data in Salesforce where data is maintained in PostgreSQL database. Amazon RDS will host REST based webservice which retrieves the records from PostgreSQL database table. From Salesforce Apex class, it will make a callout to REST based end point and fetch the data as response.

The architecture looks like this:

For this use case, we will concentrate on the Apex part assuming we have an end point to connect to AWS.

Solution Approach


We will create a Apex class which makes the callout to the end point.


Flow diagram will look like this.


Flow Diagram


Flow diagram will show step by step approach of signing process to perform callout.



Broadly, the signing process can be divided into following 4 steps:

  • Create Canonical Request

  • Create String to Sign

  • Calculate Signature
  • Create Request Header and perform callout.

Code Sample


The guidance has been taken from Signature Version 4 Signing Process


  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
public class MyAWSService{

 //request values
 String region = 'us-east-1';
 String service = 'execute-api';
 String key = 'Provide AWS Key'; //AWS key
 String secret = 'Provide AWS Secret key'; //AWS Secret key
 String host = 'api.amazonaws.com'; 
 String stage = 'qa'; //dev or qa
 String serviceName = 'retrieveAccount'; //this actually fetches the data from PostgreSQL
 
 String method = 'GET';
 String request_parameters = 'Action=ListUsers&Version=2010-05-08';
 String canonical_querystring = '';

 String qaAPIKey = 'Provide API Key';
 
 //date for headers and the credential string
 String amzdate = Datetime.now().formatGMT('yyyyMMdd\'T\'HHmmss\'Z\'');
 String datestamp = Datetime.now().formatGMT('yyyyMMdd');
  
 public void runScript(){
  
  HttpRequest req = new HttpRequest();
  //set the request method (GET, PUT etc.)
  req.setMethod(method);
  
  //create end point URL
  String endPoint = 'https://' + host + '/' + stage + '/' + serviceName;
  system.debug('Endpoint='+endPoint); //'https://api.amazonaws.com/qa/retrieveAccount'

  //assign endpointURL to request
  req.setEndpoint(endPoint);  
  
  //************* TASK 1: CREATE A CANONICAL REQUEST *************
  //Step 1 is to define the verb (GET, POST, etc.)
  //Step 2: Create canonical URI--the part of the URI from domain to query 
  String canonical_uri = '/' + stage + '/' + serviceName;    // '/qa/retrieveAccount'
  
  /*Step 3: Create the canonical query string. In this example (a GET request),
                # request parameters are in the query string. Query string values must
                # be URL-encoded. The parameters must be sorted by name.
  */
  canonical_querystring = '';
  
  //Step 4: Create the canonical headers and signed headers.
  String canonical_headers = 'host:' + host + '\n' + 'x-amz-date:' + amzdate + '\n';
  System.debug('##canonical_headers:' + canonical_headers);
  
  //Step 5: Create the list of signed headers.
  String signed_headers = 'host;x-amz-date';
  
  //Step 6: Create payload hash (hash of the request body content). 
                //For GET requests, the payload is an empty string ('')
  Blob payload = Blob.valueOf('');
  String payload_hash = EncodingUtil.convertToHex(Crypto.generateDigest('SHA-256', payload));
  
  //Step 7: Combine elements to create create canonical request  
  String canonical_request = method + '\n' 
      + canonical_uri + '\n'  
      + canonical_querystring + '\n' 
      + canonical_headers + '\n' 
      + signed_headers + '\n' 
      + payload_hash;
        
  System.debug('canonical_request=' + canonical_request);

  //************* TASK 2: CREATE THE STRING TO SIGN*************
  String algorithm = 'AWS4-HMAC-SHA256';
  String credential_scope = datestamp + '/' + region + '/' + service + '/' + 'aws4_request';
  String string_to_sign = algorithm + '\n' +  amzdate + '\n' +  credential_scope + '\n' + 
        EncodingUtil.convertToHex(Crypto.generateDigest('sha256', Blob.valueOf(canonical_request)));
  System.debug('String_to_sign: ' + string_to_sign);

  //************* TASK 3: CALCULATE THE SIGNATURE *************
  //generate signing key
  Blob signingKey = createSigningKey(secret);
  
  //generate signature  
  String signature =  createSignature(string_to_sign, signingKey); 
  
  //************* TASK 4: ADD SIGNING INFORMATION TO THE REQUEST *************
  String authorization_header = algorithm + ' ' 
      + 'Credential=' + key + '/' 
      + credential_scope + ', ' 
      +  'SignedHeaders=' + signed_headers + ', ' 
      + 'Signature=' + signature;
       
  req.setHeader('Authorization',authorization_header);
  
  //The request can include any headers,
  req.setHeader('x-api-key', qaAPIKey);
  req.setHeader('x-amz-date', amzdate);
  req.setHeader('Accept', 'application/json');
  
  Http http = new Http();
  HTTPResponse res = http.send(req);
  System.debug('*Resp:' + String.ValueOF(res.getBody()));
  System.debug('RESPONSE STRING: ' + res.toString());
  System.debug('RESPONSE STATUS: ' + res.getStatus());
  System.debug('STATUS_CODE: ' + res.getStatusCode()); 
 
 }

 //key derivation functions
 private Blob createSigningKey(String secretKey){
        Blob dateKey = signString(Blob.valueOf(datestamp),Blob.valueOf('AWS4'+secretKey));
        Blob dateRegionKey = signString(Blob.valueOf(region),dateKey);
        Blob dateRegionServiceKey = signString(Blob.valueOf(service),dateRegionKey);
        return signString(Blob.valueOf('aws4_request'),dateRegionServiceKey);
    }

 private Blob signString(Blob msg, Blob key){
        return Crypto.generateMac('HMACSHA256', msg, key);
    } 
 
 private String createSignature(String stringToSign, Blob signingKey){        
  return EncodingUtil.convertToHex(Crypto.generateMac('HMACSHA256', blob.valueof(stringToSign), signingKey));
    }
}

Few points to be noted:


  • Service has been used as 'execute-api'

  • We can use same code for other HTTP method like, POST, PATCH etc.

  • For GET method payload should be empty.

  • Query parameter keys must be sorted.

  • Algorithm has been used as AWS4-HMAC-SHA256 for signing process.

  • Minimal request header parameters to be passed as 'x-api-key', 'x-amz-date', 'Accept'


If we don't follow the step properly then most of the cases it throws Signature doesn't match error.


Also, Troubleshooting AWS Signature Version 4 Errors helps to solve the issues.


It has taken few days to implement expected authorization mechanism. 


Hope it helps!



Further Reading


Posted by at No comments:
Labels: , ,

Saturday, February 1, 2020

Approach: Dynamically insert records based on External Id with maintaining relationship

Motivation behind this


One of my mentees was struggling with approach and code sample for this below use case which motivates me on writing this post.


Use Case


Business has a requirement to view the data at Salesforce. Data will be provided from External System. External System's data is source of truth. External System will host webservice endpoint and developer could perform callout and fetch the data in JSON format.

For example, external system is maintaining Account and Inventory information. Those information should be fetched and insert the records.

Developer is also looking for an option to make the data mapping in a configurable way so that any field or Object name can be fetched on the fly and prepare the relationship among the objects and finally insert the data.


Solution Approach


Developer is trying to build up a solution with a help of Apex class and Custom Metadata Types as per following diagram.



For example, Account data is being provided from external system like below, this can be a JSON format. For better understanding plotting this in excel columns.



Since, external system's data is source of truth so we need to create External Id field on Account Object and Custom Inventory object (Inventory__c).

Account object will also have a lookup relationship to the Inventory object (relationship name: Inventory__r).

Let us assume, Account record with External Id = 123 and Inventory record with External Id = 125 have been created earlier.

So, when we insert Child Account B record then it will also maintain the relationship to the Parent Account A and Inventory record 125 through respective external Ids.

To make it configurable way of mapping, JSON attributes and Salesforce field Names should be maintained in Custom metadata types with the following information.


Here, main challenge is creating the instance of the object dynamically, add those fields using FieldAPINames and create records with maintaining the relationship.

For sake of simplicity, webservice callout and fetching records from JSON and fetching field mapping from Custom metadata types have been omitted.

Only challenging part with optimized code has been provided below:

Code Sample




 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
//create an instance of Account object, take this object name from Object field of custom metadata type.
String sObjectName = 'Account';
SObject acct = (SObject)(Type.forName('Schema.'+ sObjectName).newInstance());
//add fields based on FieldAPIName of Custom metadata types and values of JSON
acct.put('Name', 'Test Acct C');
acct.put('External_Id__c' ,'124');

//To relate to parent Account record, take this from Custom metadata type's 'Relationship Object' field 
String ParentObjName = 'Account'; //Relationship Object

//create an instance of related parent account object instance
SObject accRelationship = (SObject)(Type.forName('Schema.'+ ParentObjName).newInstance());
accRelationship.put('External_Id__c','123'); //Relationship Field API Name

//here mention Relationship API Name from Custom metadata type using putSObject method
acct.putSObject('Parent',accRelationship);

//similarly to relate Inventory record
String relatedLookupObjectName = 'Inventory__c';
SObject invRelationship = (SObject)(Type.forName('Schema.'+ relatedLookupObjectName).newInstance());
invRelationship.put('External_Id__c','125');
acct.putSObject('Inventory__r',relatedLookupObjectName);

//it can be added to a list and insert that
insert acct;




You can see that, to maintain parent relationship of the Account, Account object instance has been created and  External_Id__c has been mentioned and finally putSObject method of SObject has been used to specify related relationship.

 Also, creating an instance of an object has done with reflection technique which is faster than SObject describe.

 All the String values can be replaceable with dynamic values.

 Sometimes, code looks simple to derive but it takes time to put in a proper approach which I have tried to portray here. Hope it helps.

Further Reading


Posted by at No comments:
Labels: , ,
Subscribe to: Posts (Atom)