text: import boto3
  import logging
  import psycopg2
  import json
  from botocore.exceptions import ClientError
  from psycopg2.extensions import AsIs
  from cfn_resource_provider import ResourceProvider
  
  log = logging.getLogger()
  
  request_schema = {
      "$schema": "http://json-schema.org/draft-04/schema#",
      "type": "object",
      "oneOf": [
          {"required": ["Database", "User"]},
      ],
      "properties": {
          "Database": {"$ref": "#/definitions/connection"},
          "User": {
              "type": "string",
              "pattern": "^[_A-Za-z][A-Za-z0-9_$]*$",
              "description": "the user to create"
          },
          "Password": {
              "type": "string",
              "description": "the password for the user"
          },
          "PasswordParameterName": {
              "type": "string",
              "minLength": 1,
              "description": "the name of the password in the Parameter Store."
          },
          "PasswordSecretName": {
              "type": "string",
              "description": "the name of the secret storing the password"
          },
          "WithLogin": {
              "type": "boolean",
              "default": True,
              "description": "allow the user to login"
          },
          "WithDatabase": {
              "type": "boolean",
              "default": True,
              "description": "create a database with the same name, or only a user"
          },
          "WithCreateDb": {
              "type": "boolean",
              "default": False,
              "description": "grant createdb permission to the user"
          },
          "DeletionPolicy": {
              "type": "string",
              "default": "Retain",
              "enum": ["Drop", "Retain"]
          }
      },
      "definitions": {
          "connection": {
              "type": "object",
              "oneOf": [
                  {"required": ["DBName", "Host", "Port", "User", "Password"]},
                  {"required": ["DBName", "Host", "Port", "User", "PasswordParameterName"]},
                  {"required": ["DBName", "Host", "Port", "User", "PasswordSecretName"]}
              ],
              "properties": {
                  "DBName": {
                      "type": "string",
                      "description": "the name of the database"
                  },
                  "Host": {
                      "type": "string",
                      "description": "the host of the database"
                  },
                  "Port": {
                      "type": "integer",
                      "default": 5432,
                      "description": "the network port of the database"
                  },
                  "User": {
                      "type": "string",
                      "description": "the username of the database owner"
                  },
                  "Password": {
                      "type": "string",
                      "description": "the password of the database owner"
                  },
                  "PasswordParameterName": {
                      "type": "string",
                      "description": "the name of the database owner password in the Parameter Store."
                  },
                  "PasswordSecretName": {
                      "type": "string",
                      "description": "the name of the secret storing the database owner password"
                  }
              }
          }
      }
  }
  
  
  class PostgreSQLUser(ResourceProvider):
  
      def __init__(self):
          super(PostgreSQLUser, self).__init__()
          self.ssm = boto3.client('ssm')
          self.secrets = boto3.client('secretsmanager')
          self.connection = None
          self.request_schema = request_schema
  
      def convert_property_types(self):
          self.heuristic_convert_property_types(self.properties)
  
      def get_param_password(self, name):
          try:
              response = self.ssm.get_parameter(Name=name, WithDecryption=True)
              return response['Parameter']['Value']
          except ClientError as e:
              raise ValueError('Could not obtain password using name {}, {}'.format(name, e))
  
      def get_secrets_password(self, name):
          data = json.loads(self._get_secret(name))
  
          return data['password']
  
      def _get_secret(self, name):
          try:
              response = self.secrets.get_secret_value(SecretId=name)
              if 'SecretString' in response:
                  return response['SecretString']
  
              return response['SecretBinary']
          except ClientError as e:
              raise ValueError('Could not obtain password using name {}, {}'.format(name, e))
  
      def extract_password(self, props):
          if 'Password' in props:
              return props['Password']
          elif 'PasswordParameterName' in props:
              return self.get_param_password(props['PasswordParameterName'])
          elif 'PasswordSecretName' in props:
              return self.get_secrets_password(props['PasswordSecretName'])
  
          return None
  
      @property
      def user_password(self):
          return self.extract_password(self.properties)
  
      @property
      def dbowner_password(self):
          db = self.get('Database')
  
          return self.extract_password(db)
  
      @property
      def user(self):
          return self.get('User')
  
      @property
      def host(self):
          return self.get('Database', {}).get('Host', None)
  
      @property
      def port(self):
          return self.get('Database', {}).get('Port', 5432)
  
      @property
      def dbname(self):
          return self.get('Database', {}).get('DBName', None)
  
      @property
      def dbowner(self):
          return self.get('Database', {}).get('User', None)
  
      @property
      def with_createdb(self):
          return self.get('WithCreateDb', False)
  
      @property
      def with_database(self):
          return self.get('WithDatabase', False)
  
      @property
      def with_login(self):
          return self.get('WithLogin', False)
  
      @property
      def deletion_policy(self):
          return self.get('DeletionPolicy')
  
      @property
      def connect_info(self):
          return {'host': self.host, 'port': self.port, 'dbname': self.dbname,
                  'user': self.dbowner, 'password': self.dbowner_password}
  
      @property
      def allow_update(self):
          return self.url == self.physical_resource_id
  
      @property
      def url(self):
          if self.with_database:
              return 'postgresql:%s:%s:%s:%s:%s' % (self.host, self.port, self.dbname, self.user, self.user)
          else:
              return 'postgresql:%s:%s:%s::%s' % (self.host, self.port, self.dbname, self.user)
  
      def connect(self):
          log.info('connecting to database %s on port %d as user %s', self.host, self.port, self.dbowner)
          try:
              self.connection = psycopg2.connect(**self.connect_info)
              self.connection.set_session(autocommit=True)
          except Exception as e:
              raise ValueError('Failed to connect, %s' % e)
  
      def close(self):
          if self.connection:
              if self.status == 'SUCCESS':
                  self.connection.commit()
              else:
                  self.connection.rollback()
              self.connection.close()
  
      def db_exists(self):
          with self.connection.cursor() as cursor:
              cursor.execute(
                  "SELECT FROM pg_catalog.pg_database WHERE datname = %s", [self.user])
              rows = cursor.fetchall()
              return len(rows) > 0
  
      def role_exists(self):
          with self.connection.cursor() as cursor:
              cursor.execute(
                  "SELECT FROM pg_catalog.pg_roles WHERE rolname = %s", [self.user])
              rows = cursor.fetchall()
              return len(rows) > 0
  
      def drop_user(self):
          with self.connection.cursor() as cursor:
              if self.deletion_policy == 'Drop':
                  log.info('drop role  %s', self.user)
                  cursor.execute('DROP ROLE %s', [AsIs(self.user)])
              else:
                  log.info('disable login of  %s', self.user)
                  cursor.execute("ALTER ROLE %s NOLOGIN", [AsIs(self.user)])
  
      def drop_database(self):
          if self.deletion_policy == 'Drop':
              log.info('drop database of %s', self.user)
              with self.connection.cursor() as cursor:
                  cursor.execute('GRANT %s TO %s', [
                      AsIs(self.user), AsIs(self.dbowner)])
                  cursor.execute('DROP DATABASE %s', [AsIs(self.user)])
          else:
              log.info('not dropping database %s', self.user)
  
      def update_password(self):
          log.info('update password of role %s', self.user)
          with self.connection.cursor() as cursor:
              if self.with_login and self.user_password:
                  cursor.execute("ALTER ROLE %s LOGIN ENCRYPTED PASSWORD %s", [
                      AsIs(self.user), self.user_password])
              elif self.with_login:
                  cursor.execute("ALTER ROLE %s LOGIN", [AsIs(self.user)])
              else:
                  cursor.execute("ALTER ROLE %s NOLOGIN", [AsIs(self.user)])
  
      def create_role(self):
          log.info('create role %s ', self.user)
          with self.connection.cursor() as cursor:
              cursor.execute('CREATE ROLE %s', [AsIs(self.user)])
  
              if self.with_login and self.user_password:
                  cursor.execute("ALTER ROLE %s LOGIN ENCRYPTED PASSWORD %s", [
                      AsIs(self.user), self.user_password])
              elif self.with_login:
                  cursor.execute("ALTER ROLE %s LOGIN", [AsIs(self.user)])
              else:
                  cursor.execute("ALTER ROLE %s NOLOGIN", [AsIs(self.user)])
  
              if self.with_createdb:
                  cursor.execute('ALTER ROLE %s CREATEDB', [AsIs(self.user)])
  
      def create_database(self):
          log.info('create database %s', self.user)
          with self.connection.cursor() as cursor:
              cursor.execute('GRANT %s TO %s', [
                  AsIs(self.user), AsIs(self.dbowner)])
              cursor.execute('CREATE DATABASE %s OWNER %s', [
                  AsIs(self.user), AsIs(self.user)])
  
      def grant_ownership(self):
          log.info('grant ownership on %s to %s', self.user, self.user)
          with self.connection.cursor() as cursor:
              cursor.execute('GRANT %s TO %s', [
                  AsIs(self.user), AsIs(self.dbowner)])
              cursor.execute('ALTER DATABASE %s OWNER TO %s', [
                  AsIs(self.user), AsIs(self.user)])
  
      def drop(self):
          if self.with_database and self.db_exists():
              self.drop_database()
          if self.role_exists():
              self.drop_user()
  
      def create_user(self):
          if self.role_exists():
              self.update_password()
          else:
              self.create_role()
  
          if self.with_database:
              if self.db_exists():
                  self.grant_ownership()
              else:
                  self.create_database()
  
      def create(self):
          try:
              self.connect()
              self.create_user()
              self.physical_resource_id = self.url
          except Exception as e:
              self.physical_resource_id = 'could-not-create'
              self.fail('Failed to create user, %s' % e)
          finally:
              self.close()
  
      def update(self):
          try:
              self.connect()
              if self.allow_update:
                  self.update_password()
              else:
                  self.fail('Only the password of %s can be updated' % self.user)
          except Exception as e:
              self.fail('Failed to update the user, %s' % e)
          finally:
              self.close()
  
      def delete(self):
          if self.physical_resource_id == 'could-not-create':
              self.success('user was never created')
  
          try:
              self.connect()
              self.drop()
          except Exception as e:
              return self.fail(str(e))
          finally:
              self.close()
  
      def is_supported_resource_type(self):
          return self.resource_type in  ['Custom::PostgreSQLUser', 'Custom::PostgresDBUser']
  
  
  provider = PostgreSQLUser()
  
  
  def handler(request, context):
      return provider.handle(request, context)
  