Last active
November 9, 2021 12:58
-
-
Save cmcconnell1/8d5fa5d7fe000f92213b0f34374428f8 to your computer and use it in GitHub Desktop.
Provides idempotent remote (RDS) PostgreSQL create role/user from python without CM modules, etc.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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)) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment