Encrypted Database Connections to HCL Informix on AWS

In my last post we launched an HCL Informix AWS instance and verified the engine was running, now I want to connect to this instance from my data center over the public internet.

I can't just send data back and forth without encrypting it because I don't want my data intercepted by someone in between. I'll enable TLS encryption to keep my data secret and I'll also create a transaction table, insert some data and do some basic performance testing. In later posts, I will shard this transaction table over multiple AWS Informix instances and I'll do a little prep work to make that step easier.

If you don't plan to connect to your Informix instance over the public internet and keep all of your communications between servers in the same VPC then this encryption step is unnecessary, but it is still good to know how to do it.
There is one rub. You're going to need to get your hands on a later version of the CSDK that comes with GSKit 8 (I know version 4.10 of the CSDK works) and that is currently kind of a challenge. As of this writing, you can contact HCL and ask for it or if you have an IBM Passport Advantage login, you can download it there. I'm going to assume you have an appropriate CSDK installed on the client. If you need help installing the CSDK on a client, leave a note in the comments and I'll add them to the post.

Prerequisites

• HCL Informix on AWS engine setup and running
• Informix CSDK 4.10 installed on the client
• I will use the informixdb Python module to connect to Informix from my client, you can use whatever you like

What we are going to do

• Open up a port on our AWS instance for TLS communications
• AWS instance changes
• Use the HCL Informix certificate to create a TLS/SSL key store on the client
• Connect over the public internet and do some testing

Open up a port on AWS instance for TLS communications

In our Security Group we only let connections from port 22 for ssh into our AWS instance and if we're going to connect to Informix we need to open up a port.

In Security Groups on the AWS console, select our Security Group and click Actions and then click Edit inbound rules.

In the dialogue that is opened up click the Add Rule button and add a rule of:

• Type: Custom TCP Rule
• Protocol: TCP
• Port Range: 9089
• Source: Custom and the public IP address or range of your client OR you can use Anywhere if you don't (just for the purposes of testing)

Click Save and your AWS firewall will now allow traffic on port 9089 from your client over the public internet.

AWS instance changes

I'm going to make some changes to my ONCONFIG and sqlhosts that will really be more useful in future posts when I setup sharding, but I'll do them now. 

1. ssh to your AWS instance and sudo -u informix bash

2. ONCONFIG changes

I'm going to change the name of my instance to something more unique so I can start multiple instances later.

Change DBSERVERNAME from ol_aws to informix_1

Change DBSERVERALIASES to informix_1_ssl

3. sqlhosts changes: replace contents with the following. You can find the internal DNS name for an EC2 instance in the details of the instance in the AWS console.

informix_1     onsoctcp <internal DNS Name>   9088 
informix_1_ssl onsocssl *                     9089 

4. Edit /etc/profile.d/informix.sh and change INFORMIXSERVER to informix_1

5. Shutdown the engine via onmode -ky

6. Exit back to the centos user, sudo -u root bash and rename the engine's SSL files

The engine looks for these ssl files in the $INFORMIXDIR/ssl/<dbservername>.extension. We changed the DBSERVERNAME, so we need to rename these.

cd $INFORMIXDIR/ssl/
mv ol_aws.rdb informix_1.rdb
mv ol_aws.crl informix_1.crl
mv ol_aws.sth informix_1.sth
mv ol_aws.kdb informix_1.kdb

7. Exit back to the centos user, sudo -u informix bash and start the engine with oninit -v

8. Create a database user named 'client' that we will use to connect

echo "create user client with password 'your_password' account unlock properties user nobody authorization(dbsa)" | dbaccess sysmaster

Create TLS key store on the client

1. On the AWS instance, copy the TLS certificate to /tmp

cp /home/informix/client_ssl/selfsigned_ssl.cert /tmp

2. Create a client_ssl directory on the client server and get the selfsigned_ssl.cert file

I'm doing this as user informix and I've decided to create /home/informix/client_ssl to mirror HCL's setup. Feel free to put this directory wherever you see fit.

mkdir /home/informix/client_ssl
scp -i ~/.ssh/informixdba.pem centos@<elastic ip>:/tmp/selfsigned_ssl.cert .

3. Create a TLS key store

/usr/local/ibm/gsk8_64/bin/gsk8capicmd_64 -keydb -create -db informix_ssl.kdb -pw <a password> -type cms -stash

4. Add the AWS certificate to the key store

/usr/local/ibm/gsk8_64/bin/gsk8capicmd_64 -cert -add -db informix_ssl.kdb -pw <the password> -label selfsigned_ssl -file selfsigned_ssl.cert -format ascii

5. Create a conssl.cfg file on the client

Create $INFORMIXDIR/etc/conssl.cfg and add

SSL_KEYSTORE_FILE   /home/informix/client_ssl/informix_ssl.kdb   # Keystore file
SSL_KEYSTORE_STH    /home/informix/client_ssl/informix_ssl.sth   # Keystore stash file

and change the permissions of informix_ssl.kdb and informix_ssl.sth via chmod 644 <filename>

6. Add the encrypted connection to the client's sqlhosts file

informix_1_ssl          onsocssl          52.0.114.137          9089

You will now be able to connect to informix_1_ssl with dbaccess as the user 'client' and create a database and a table that we will use for some testing. 

create database stocks in dbs_1 with buffered log;

create table stock_trans (
   id            bigserial,
   account_id    integer,
   stock_id      integer,
   action        char(1),
   shares        integer,
   price         float,
   timestamp     datetime year to second
) in dbs_1 extent size 20000 next size 20000 lock mode row;

create unique index stock_trans_pk on stock_trans(id);
alter table stock_trans add constraint primary key (id)
        constraint stock_trans_pk;

I created a simple Python script to generate and insert 10,000 records into the stock_trans table and I ran 3 tests.

The first test is to run the script locally on the AWS instance to get a baseline of how long it takes the engine to process the inserts without any encryption and without going over the public internet. 

Average per row 0.00011 seconds

The second test is to perform the inserts over the public internet, but without any encryption enabled. This will show me how much overhead is added by the public internet.

Average per row: 0.03660 seconds

That's not a terribly unexpected result, but the internet does add some considerable overhead. The final test is to perform the inserts over the public internet using encryption to see how much overhead it adds.

Average per row : 0.03848 seconds

We can see that encryption is relatively inexpensive, only costing a couple milliseconds per row insert.

That's all for today. Next time I will create a sharded collection of our stock_trans table over multiple AWS instances, add and remove shards from the collection and do some more performance testing.

Don't forget to check out the IIUG World 2018 Informix event in Washington, DC October 28 to November 1, 2018.

Early Registration is now open (save $275) and we are now accepting Speaker Proposals (save 100%) and we even have a new website from this century and a Twitter account you can follow @IIUGWorld2018 for the latest updates.