# Generated by Django 3.1.7 on 2021-03-03 19:26
import logging
import os

from django.conf import settings
from django.db import migrations
from django.db.utils import ProgrammingError
from psycopg2.errors import DuplicateDatabase
from psycopg2.errors import DuplicateObject
from psycopg2.errors import InsufficientPrivilege


LOG = logging.getLogger(__name__)


def create_hive_db(apps, schema_editor):
    rolname = settings.HIVE_DATABASE_USER
    datname = settings.HIVE_DATABASE_NAME
    kokudb = settings.DATABASES.get("default").get("NAME")
    kokudbuser = settings.DATABASES.get("default").get("USER")
    db_password = settings.DATABASES.get("default").get("PASSWORD")
    hive_db_password = settings.HIVE_DATABASE_PASSWORD

    role_create_sql = f"""
        create role "{rolname}" with login encrypted password '{{hivepw}}';
    """

    role_public_revoke_sql = """
        revoke connect on database "{}" from "public";
    """

    role_revoke_sql = f"""
        revoke connect on database "{kokudb}" from "{rolname}";
    """

    role_grant_sql = f"""
        grant connect on database "{datname}" to "{kokudbuser}";
    """

    db_create_sql = f"""
        create database "{datname}" owner "{rolname}";
    """

    db_access_check_sql = """
        select has_database_privilege(%s, %s, 'connect');
    """

    try:
        conn = schema_editor.connection.connection.__class__(
            "postgresql://{user}:{password}@{host}:{port}/{dbname}".format(
                password=db_password, **schema_editor.connection.connection.get_dsn_parameters()
            )
        )
        conn.autocommit = True
        with conn.cursor() as cur:
            LOG.info(f"Creating role {rolname}.")
            try:
                cur.execute(role_create_sql.format(hivepw=hive_db_password))
            except (ProgrammingError, InsufficientPrivilege, DuplicateObject) as e:
                LOG.info(e)
                LOG.info(f"Role {rolname} exists.")

            try:
                LOG.info(f"""Granting role "{rolname}" membership to "{kokudbuser}".""")
                cur.execute(f"""grant "{rolname}" to "{kokudbuser}"; """)
            except (ProgrammingError, InsufficientPrivilege) as e:
                LOG.info(e)

            try:
                LOG.info(f"Creating database {rolname}.")
                cur.execute(db_create_sql)
            except (ProgrammingError, InsufficientPrivilege, DuplicateDatabase) as e:
                LOG.info(e)
                LOG.info(f"Database {rolname} exists.")

            # Revoke access to koku db from public
            try:
                cur.execute(db_access_check_sql, ("public", kokudb))
                if cur.fetchone()[0]:
                    LOG.info(f"Revoking public access to {kokudb}.")
                    cur.execute(role_public_revoke_sql.format(kokudb))
            except (ProgrammingError, InsufficientPrivilege) as e:
                LOG.info(e)

            # Revoke access to hive db from public
            try:
                cur.execute(db_access_check_sql, ("public", datname))
                if cur.fetchone()[0]:
                    LOG.info(f"Revoking public access to {datname}.")
                    cur.execute(role_public_revoke_sql.format(datname))
            except (ProgrammingError, InsufficientPrivilege) as e:
                LOG.info(e)

            # Revoke access to koku db from hive user
            try:
                cur.execute(db_access_check_sql, (rolname, kokudb))
                if cur.fetchone()[0]:
                    LOG.info(f"Revoking {rolname} access to {kokudb}.")
                    cur.execute(role_revoke_sql)
            except (ProgrammingError, InsufficientPrivilege) as e:
                LOG.info(e)

            # Grant access to hive db from koku user
            try:
                cur.execute(db_access_check_sql, (kokudbuser, datname))
                if not cur.fetchone()[0]:
                    LOG.info(f"Granting {kokudbuser} access to {datname}.")
                    cur.execute(role_grant_sql)
            except (ProgrammingError, InsufficientPrivilege) as e:
                LOG.info(e)

            try:
                LOG.info(f"""Revoking role "{rolname}" membership from "{kokudbuser}".""")
                cur.execute(f"""revoke "{rolname}" from "{kokudbuser}"; """)
            except (ProgrammingError, InsufficientPrivilege) as e:
                LOG.info(e)
    finally:
        if conn:
            conn.close()


class Migration(migrations.Migration):

    dependencies = [("api", "0038_drop_app_needs_migrations_func")]

    operations = [migrations.RunPython(create_hive_db)]

# Generated by Django 3.1.7 on 2021-03-03 19:26
import logging
import os

from django.conf import settings
from django.db import migrations
from django.db.utils import ProgrammingError
from psycopg2.errors import DuplicateDatabase
from psycopg2.errors import DuplicateObject
from psycopg2.errors import InsufficientPrivilege


LOG = logging.getLogger(__name__)


def create_hive_db(apps, schema_editor):
    rolname = settings.HIVE_DATABASE_USER
    datname = settings.HIVE_DATABASE_NAME
    kokudb = settings.DATABASES.get("default").get("NAME")
    kokudbuser = settings.DATABASES.get("default").get("USER")
    db_password = settings.DATABASES.get("default").get("PASSWORD")
    hive_db_password = settings.HIVE_DATABASE_PASSWORD

    role_create_sql = f"""
        create role "{rolname}" with login encrypted password '{{hivepw}}';
    """

    role_public_revoke_sql = """
        revoke connect on database "{}" from "public";
    """

    role_revoke_sql = f"""
        revoke connect on database "{kokudb}" from "{rolname}";
    """

    role_grant_sql = f"""
        grant connect on database "{datname}" to "{kokudbuser}";
    """

    db_create_sql = f"""
        create database "{datname}" owner "{rolname}";
    """

    db_access_check_sql = """
        select has_database_privilege(%s, %s, 'connect');
    """

    try:
        conn = schema_editor.connection.connection.__class__(
            "postgresql://{user}:{password}@{host}:{port}/{dbname}".format(
                password=db_password, **schema_editor.connection.connection.get_dsn_parameters()
            )
        )
        conn.autocommit = True
        with conn.cursor() as cur:
            LOG.info(f"Creating role {rolname}.")
            try:
                cur.execute(role_create_sql.format(hivepw=hive_db_password))
            except (ProgrammingError, InsufficientPrivilege, DuplicateObject) as e:
                LOG.info(e)
                LOG.info(f"Role {rolname} exists.")

            try:
                LOG.info(f"""Granting role "{rolname}" membership to "{kokudbuser}".""")
                cur.execute(f"""grant "{rolname}" to "{kokudbuser}"; """)
            except (ProgrammingError, InsufficientPrivilege) as e:
                LOG.info(e)

            try:
                LOG.info(f"Creating database {rolname}.")
                cur.execute(db_create_sql)
            except (ProgrammingError, InsufficientPrivilege, DuplicateDatabase) as e:
                LOG.info(e)
                LOG.info(f"Database {rolname} exists.")

            # Revoke access to koku db from public
            try:
                cur.execute(db_access_check_sql, ("public", kokudb))
                if cur.fetchone()[0]:
                    LOG.info(f"Revoking public access to {kokudb}.")
                    cur.execute(role_public_revoke_sql.format(kokudb))
            except (ProgrammingError, InsufficientPrivilege) as e:
                LOG.info(e)

            # Revoke access to hive db from public
            try:
                cur.execute(db_access_check_sql, ("public", datname))
                if cur.fetchone()[0]:
                    LOG.info(f"Revoking public access to {datname}.")
                    cur.execute(role_public_revoke_sql.format(datname))
            except (ProgrammingError, InsufficientPrivilege) as e:
                LOG.info(e)

            # Revoke access to koku db from hive user
            try:
                cur.execute(db_access_check_sql, (rolname, kokudb))
                if cur.fetchone()[0]:
                    LOG.info(f"Revoking {rolname} access to {kokudb}.")
                    cur.execute(role_revoke_sql)
            except (ProgrammingError, InsufficientPrivilege) as e:
                LOG.info(e)

            # Grant access to hive db from koku user
            try:
                cur.execute(db_access_check_sql, (kokudbuser, datname))
                if not cur.fetchone()[0]:
                    LOG.info(f"Granting {kokudbuser} access to {datname}.")
                    cur.execute(role_grant_sql)
            except (ProgrammingError, InsufficientPrivilege) as e:
                LOG.info(e)

            try:
                LOG.info(f"""Revoking role "{rolname}" membership from "{kokudbuser}".""")
                cur.execute(f"""revoke "{rolname}" from "{kokudbuser}"; """)
            except (ProgrammingError, InsufficientPrivilege) as e:
                LOG.info(e)
    finally:
        if conn:
            conn.close()


class Migration(migrations.Migration):

    dependencies = [("api", "0038_drop_app_needs_migrations_func")]

    operations = [migrations.RunPython(create_hive_db)]

# Generated by Django 3.1.7 on 2021-03-03 19:26 create role "{rolname}" with login encrypted password '{{hivepw}}'; revoke connect on database "{}" from "public"; revoke connect on database "{kokudb}" from "{rolname}"; grant connect on database "{datname}" to "{kokudbuser}"; create database "{datname}" owner "{rolname}"; select has_database_privilege(%s, %s, 'connect'); Granting role "{rolname}" membership to "{kokudbuser}". grant "{rolname}" to "{kokudbuser}"; # Revoke access to koku db from public # Revoke access to hive db from public # Revoke access to koku db from hive user # Grant access to hive db from koku user Revoking role "{rolname}" membership from "{kokudbuser}". revoke "{rolname}" from "{kokudbuser}";