Provides idempotent remote (RDS) PostgreSQL create role/user from python without CM modules, etc.

idempotent-postgresql-rds-create-role.py

#!/usr/bin/env python3
# Overview:
#   Provides idempotent remote RDS PostgreSQL (application) role/user creation from python for use outside of CM modules.
#   Because PostgreSQL doesn't have something like 'CREATE ROLE IF NOT EXISTS' which would be nice.
#   ref: https://stackoverflow.com/questions/8546759/how-to-check-if-a-postgres-user-exists
# Requirements: 
#   Python3 and psycopg2 module 
# cmcc
import psycopg2

##### update creds for each kube cluster
cluster_name = "foo123"
db_host = cluster_name + ".123456dvdfgsdfgd.us-west-1.rds.amazonaws.com"
db_port = 5432

# postgres admin creds
admin_db_name = "postgres"
admin_db_user = "postgres"
admin_db_pass = "super-secret1"

# deis creds
deis_db_name = "deis"
deis_app_user = "deis"
deis_app_passwd = "super-secret2"
##### Dont change below code


# thanks to contributors here: https://stackoverflow.com/questions/8546759/how-to-check-if-a-postgres-user-exists
check_user_cmd = ("SELECT 1 FROM pg_roles WHERE rolname='%s'" % (deis_app_user))

# our create role/user command and vars
create_user_cmd = ("CREATE ROLE %s WITH LOGIN CREATEDB PASSWORD '%s'" % (deis_app_user, deis_app_passwd))

# thanks to contributors here: https://stackoverflow.com/questions/37488175/simplify-database-psycopg2-usage-by-creating-a-module
class RdsCreds():
    def __init__(self):
        self.conn = psycopg2.connect("dbname=%s user=%s host=%s password=%s" % (admin_db_name, admin_db_user, db_host, admin_db_pass))
        self.conn.set_isolation_level(0)
        self.cur = self.conn.cursor()

    def query(self, query):
        self.cur.execute(query)
        return self.cur.rowcount > 0

    def close(self):
        self.cur.close()
        self.conn.close()

db = RdsCreds()
user_exists = db.query(check_user_cmd)

# PostgreSQL currently has no 'create role if not exists'
# So, we only want to create the role/user if not exists else psycopg2 
if (user_exists) is True:
    print("%s user_exists: %s" % (deis_app_user, user_exists))
    print("Idempotent: No credential modifications required. Exiting...")
    db.close()
else:
    print("%s user_exists: %s" % (deis_app_user, user_exists))
    print("Creating %s user now" % (deis_app_user))
    db.query(create_user_cmd)
    user_exists = db.query(check_user_cmd)
    db.close()
    print("%s user_exists: %s" % (deis_app_user, user_exists))