profile for Gajendra D Ambi on Stack Exchange, a network of free, community-driven Q&A sites

Monday, September 17, 2018

DBaaS: postgresql or mysql ?!

So recently I had to spin up some databases on ibm cloud using ibm compose for a bank. I noticed that most of the import postgresql settings require an access to the OS on which it is installed which makes it less ideal for a DBaaS (database as a service). We are using IBM's very own iCAM aka IBM Cloud Automation Manager which uses compose feature on IBM cloud. iCAM itself uses terraform for provisioning and auto generates a GUI bases on your vars.json. you should try it, its free for exploring.
ex:-
We need to change the user authentication from ident to md5. You need access to pg_hba.conf which will be usually at /opt/postgresql/9.5/data. So if you are someone who is providing DBaaS (elephantsql, azure, gcp, aws) then you will be limiting users with some key configurations since I cannot give all my users access to pg_hba.conf file.
another ex;
I need to whitelist some ip addresses
How it is done in postgresql
local      database  user  auth-method  [auth-options]
host       database  user  address  auth-method  [auth-options]
hostssl    database  user  address  auth-method  [auth-options]
hostnossl  database  user  address  auth-method  [auth-options]
host       database  user  IP-address  IP-mask  auth-method  [auth-options]
hostssl    database  user  IP-address  IP-mask  auth-method  [auth-options]
hostnossl  database  user  IP-address  IP-mask  auth-method  [auth-options]

How is it done in mysql
GRANT SELECT, SHOW VIEW
ON $db_name.*
TO $userName@`83.141.3.27` IDENTIFIED BY ‘$userPassword’;
I actually like postgresql since it offers a lot of good stuff and my favorite jsontype data input but it being a new and younger player than mysql has decided to have such configuriton options available at the OS layer is a bummer. So, yes if you are someone who needs to edit pg_hba.conf for whatever reason then go for mysql. Mysql has started offering json type data input too.
The only way to get around this is to use rest api of the cloud provider and try to do it there. If you are doing it on IBM bluemix then this will help you out.
https://www.compose.com/articles/the-ibm-cloud-compose-api/#availableapicalls