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.
We can see that encryption is relatively inexpensive, only costing a couple milliseconds per row insert.
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.
Happy to read your blog and gained a lot of new information in detail. Thanks for sharing.
ReplyDeleteIELTS Coaching in Mulund
IELTS Training in Mulund West
IELTS Courses in Mulund
IELTS Coaching Centres in Mulund
IELTS Centres in Mulund East
Well written Blog, I really enjoy reading your blog. this info will be helpful for me. Thanks for sharing.
ReplyDeleteccna Training in Chennai
ccna institute in Chennai
Angularjs Training in Chennai
gst classes in chennai
ux design course in chennai
PHP Training in Chennai
Web Designing Course in Chennai
ccna course in chennai
ccna training in chennai
Good job and great informative blog.
ReplyDeleteJapanese Classes in Chennai
Japanese Course in Chennai
Best Spoken English Classes in Chennai
French Language Classes in Chennai
pearson vue exam centers in chennai
German Classes in Chennai
Japanese Classes in OMR
Japanese Classes in Porur
French Classes in anna nagar
Spoken English Class in Anna Nagar
Rice Bags Manufacturers
ReplyDeletePouch Manufacturers
wall putty bag manufacturers
fertilizer bag manufacturers
seed bag manufacturers
gusseted bag manufacturers
bopp laminated bags manufacturer
Lyrics with music
we have provide the best ppc service.
ReplyDeleteppc company in gurgaon
website designing company in Gurgaon
PPC company in Noida
seo company in gurgaon
PPC company in Mumbai
PPC company in Chandigarh
Digital Marketing Company
we have provide the best fridge repair service.
ReplyDeletefridge repair in faridabad
Videocon Fridge Repair in Faridabad
Whirlpool Fridge Repair in Faridabad
Hitachi Fridge Repair In Faridabad
Washing Machine Repair in Noida
godrej washing machine repair in noida
whirlpool Washing Machine Repair in Noida
IFB washing Machine Repair in Noida
LG Washing Machine Repair in Noida
iso certification in noida
ReplyDeleteiso certification in delhi
ce certification in delhi
iso 14001 certification in delhi
iso 22000 certification in delhi
iso consultants in noida
iso registration in delhi
ReplyDeleteiso 22000 certification cost
ISO 9001 Certification in Noida
website designing services
SEO Service Consultant
I am glad that I saw this post. It is informative blog for us and we need this type of blog thanks for share this blog, Keep posting such instructional blogs and I am looking forward for your future posts.
ReplyDeleteCyber Security Projects for Final Year
JavaScript Training in Chennai
Project Centers in Chennai
JavaScript Training in Chennai
ReplyDeleteتعد الاول افضل شركة غسيل خزانات بالمدينة المنورة تعمل على استخدام افضل ادوات تنظيف وتعقيم خزانات المياه
It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
ReplyDeletehttp://chennaitraining.in/sap-rem-training-in-chennai/
http://chennaitraining.in/sap-scm-training-in-chennai/
http://chennaitraining.in/sap-sd-training-in-chennai/
http://chennaitraining.in/sap-sm-training-in-chennai/
http://chennaitraining.in/sap-srm-training-in-chennai/
http://chennaitraining.in/sap-success-factor-training-in-chennai/
http://chennaitraining.in/sap-tr-training-in-chennai/
http://chennaitraining.in/sap-wf-training-in-chennai/
Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
ReplyDeleteCyber Security Training Course in Chennai | Certification | Cyber Security Online Training Course | Ethical Hacking Training Course in Chennai | Certification | Ethical Hacking Online Training Course | CCNA Training Course in Chennai | Certification | CCNA Online Training Course | RPA Robotic Process Automation Training Course in Chennai | Certification | RPA Training Course Chennai | SEO Training in Chennai | Certification | SEO Online Training Course
This comment has been removed by the author.
ReplyDelete