def add_public_endpoint()

in mds_plugin/util.py [0:0]


def add_public_endpoint(**kwargs):
    """Creates a public endpoint using MySQL Router on a compute instance

    If no id is given, it will prompt the user for the id.

    Args:
        **kwargs: Optional parameters

    Keyword Args:
        instance_name (str): Name of the compute instance
        db_system_name (str): The new name of the DB System.
        db_system_id (str): The OCID of the db_system
        private_key_file_path (str): The file path to an SSH private key
        shape (str): The name of the shape to use
        cpu_count (int): The number of OCPUs
        memory_size (int): The amount of memory
        mysql_user_name (str): The MySQL user name to use for bootstrapping
        public_ip (bool): If set to true, a public IP will be assigned to the compute instance
        domain_name (str): The domain name of the compute instance
        port_forwarding (bool): Whether port forwarding of MySQL ports should be enabled
        mrs (bool): Whether the MySQL REST Service (MRS) should be enabled
        ssl_cert (bool): Whether SSL Certificates should be managed
        jwt_secret (str): The JWT secret for MRS
        compartment_id (str): The OCID of the compartment
        config (object): An OCI config object or None.
        config_profile (str): The name of an OCI config profile
        interactive (bool): Indicates whether to execute in interactive mode
        raise_exceptions (bool): If set to true exceptions are raised
        return_formatted (bool): If set to true, a list object is returned

    Returns:
       None
    """

    # cSpell:ignore OCPU
    instance_name = kwargs.get("instance_name")
    db_system_name = kwargs.get("db_system_name")
    db_system_id = kwargs.get("db_system_id")
    private_key_file_path = kwargs.get(
        "private_key_file_path", "~/.ssh/id_rsa")
    shape = kwargs.get("shape", "VM.Standard.E4.Flex")
    cpu_count = kwargs.get("cpu_count", 1)
    memory_size = kwargs.get("memory_size", 16)

    mysql_user_name = kwargs.get("mysql_user_name", "dba")
    mysql_user_password = core.prompt(
        f"Please enter the password for {mysql_user_name}",
        {"type": "password"})

    public_ip = kwargs.get("public_ip", True)
    domain_name = kwargs.get("domain_name")

    port_forwarding = kwargs.get("port_forwarding", True)
    mrs = kwargs.get("mrs", True)
    ssl_cert = kwargs.get("ssl_cert", False)
    jwt_secret = kwargs.get("jwt_secret")

    compartment_id = kwargs.get("compartment_id")
    config = kwargs.get("config")
    config_profile = kwargs.get("config_profile")

    interactive = kwargs.get("interactive", core.get_interactive_default())
    raise_exceptions = kwargs.get("raise_exceptions", not interactive)
    return_formatted = kwargs.get("return_formatted", interactive)

    # Get the active config and compartment
    try:
        config = configuration.get_current_config(
            config=config, config_profile=config_profile,
            interactive=interactive)
        compartment_id = configuration.get_current_compartment_id(
            compartment_id=compartment_id, config=config)
        db_system_id = configuration.get_current_db_system_id(
            db_system_id=db_system_id, config=config)

        from mds_plugin import compute
        import configparser
        import io
        import time
        import hashlib

        db_system = mysql_database_service.get_db_system(
            db_system_name=db_system_name, db_system_id=db_system_id,
            compartment_id=compartment_id, config=config,
            interactive=interactive,
            return_python_object=True)
        if db_system is None:
            raise ValueError("No DB System selected."
                             "Cancelling operation")

        if not jwt_secret:
            md5 = hashlib.md5()
            md5.update(db_system.id.encode())
            jwt_secret = md5.hexdigest()

        # Get the first active endpoint
        endpoints = [e for e in db_system.endpoints if e.status == 'ACTIVE']
        if len(endpoints) < 1:
            raise Exception(
                "This MySQL DB System has no active endpoints assigned.")
        endpoint = endpoints[0]

        # Get the compute instance (let the user create it
        # if it does not exist yet)
        jump_host = create_compute_instance_for_endpoint(
            instance_name=instance_name,
            private_key_file_path=private_key_file_path,
            db_system_id=db_system_id,
            shape=shape, memory_size=memory_size,
            cpu_count=cpu_count,
            dns_a_record_notification=ssl_cert,
            domain_name=domain_name,
            compartment_id=db_system.compartment_id, config=config,
            interactive=interactive,
            return_python_object=True)
        if not jump_host:
            raise Exception(f"Compute instance {instance_name} not available."
                            "Operation cancelled.")

        # Get the public IP of the instance
        public_ip = compute.get_instance_public_ip(
            instance_id=jump_host.id, compartment_id=db_system.compartment_id,
            config=config)
        if not public_ip:
            raise Exception(f"The public IP of the instance {instance_name} "
                            "could not be fetched.")

        sec_lists = compute.get_instance_vcn_security_lists(
            instance_id=jump_host.id,
            compartment_id=db_system.compartment_id,
            config=config, interactive=interactive,
            raise_exceptions=raise_exceptions,
            return_python_object=True)
        if sec_lists is None:
            raise Exception(
                "The network security lists could not be fetched.")

        # Allow traffic on port 80 in order to fetch the certs
        if mrs and public_ip:
            compute.add_ingress_port_to_security_lists(
                security_lists=sec_lists, port=80,
                description="MRS HTTP via Router",
                compartment_id=compartment_id, config=config,
                interactive=interactive,
                raise_exceptions=raise_exceptions)

        # Open an SSH connection to the instance
        if interactive:
            print("\nBootstrapping the MySQL Router.\n"
                  f"Connecting to {instance_name} instance at {public_ip}...")
        try:
            with compute.SshConnection(
                    username="opc", host=public_ip,
                    private_key_file_path=private_key_file_path) as conn:

                if interactive:
                    print(f"Connected to {instance_name} instance at "
                          f"{public_ip}.")

                # Get MySQL Router configuration from remote instance
                output = ""
                output = conn.execute(
                    'test -f /etc/mysqlrouter/'
                    'mysqlrouter.conf && echo "Available"').strip()
                if output != "Available":
                    # If the config is not available yet, give the instance time
                    # to complete setup
                    if interactive:
                        print(
                            f"Waiting for MySQL Router Configuration to become "
                            f"available.\nThis can take up to 2 minutes.",
                            end="")
                    try:
                        i = 0
                        while output != "Available" and i < 25:
                            output = conn.execute(
                                'test -f /etc/mysqlrouter/'
                                'mysqlrouter.conf && echo "Available"').strip()
                            if output != "Available":
                                time.sleep(5)
                                if interactive:
                                    print(".", end="")
                            i += 1
                    except:
                        pass

                if output == "":
                    raise Exception(
                        "Could not fetch MySQL Router configuration from remote instance.")

                if interactive:
                    print("Bootstrapping MySQL Router against "
                          f"{mysql_user_name}@{endpoint.ip_address}:{endpoint.port} "
                          f"using JWT secret {jwt_secret} ...")

                (success, output) = conn.executeAndSendOnStdin(
                    f"sudo mysqlrouter_bootstrap {mysql_user_name}@{endpoint.ip_address}:{endpoint.port} "
                    f"-u mysqlrouter "
                    f"--mrs --mrs-global-secret {jwt_secret} " if mrs else ""
                    "--https-port 8446 "
                    "--conf-set-option=http_server.ssl=0 "
                    "--conf-set-option=http_server.port=8446 ",
                    mysql_user_password)

                if not success:
                    if output:
                        print(output)
                    raise Exception("Bootstrap operation failed.")

                # Manually fix the MySQL Router config till bootstrap allows to disable SSL
                # Load config to in-memory stream
                conn.execute(
                    "sudo cp /etc/mysqlrouter/mysqlrouter.conf /home/opc/mysqlrouter.conf")
                conn.execute("sudo chown opc:opc /home/opc/mysqlrouter.conf")
                with io.BytesIO() as router_config_stream:
                    # Get the remote config file
                    try:
                        conn.get_remote_file_as_file_object(
                            "/home/opc/mysqlrouter.conf",
                            router_config_stream)
                    except Exception as e:
                        raise Exception("Could not get router config file. "
                                        f"{str(e)}")

                    # If there was an error, print it
                    last_error = conn.get_last_error()
                    if last_error != "":
                        raise Exception(f"Could not read router config file. "
                                        f"{last_error}")

                    # Load CLI config file
                    router_config = configparser.ConfigParser()
                    router_config.read_string(
                        router_config_stream.getvalue().decode("utf-8"))
                    router_config_stream.close()

                # # # Ensure that there is a section with the name of
                # # # "routing:classic"
                # # if "routing:classic" not in router_config.sections():
                # #     router_config["routing:classic"] = {}

                # # cnf = router_config["routing:classic"]
                # # cnf["routing_strategy"] = "round-robin"
                # # cnf["bind_address"] = "0.0.0.0"
                # # cnf["bind_port"] = "6446"
                # # cnf["destinations"] = f"{endpoint.ip_address}:{endpoint.port}"

                # # Ensure that there is a section with the name of "http_server"
                # if "http_server" not in router_config.sections():
                #     router_config["http_server"] = {}

                cnf = router_config["http_server"]
                cnf["port"] = "8446"
                cnf["ssl"] = "0"
                # cnf["ssl_cert"] = ""
                # cnf["ssl_key"] = ""
                # cnf["static_folder"] = "/var/run/mysqlrouter/www/"

                # # # Ensure that there is a section with the name of "routing:x"
                # # if "routing:x" not in router_config.sections():
                # #     router_config["routing:x"] = {}

                # # cnf = router_config["routing:x"]
                # # cnf["routing_strategy"] = "round-robin"
                # # cnf["bind_address"] = "0.0.0.0"
                # # cnf["bind_port"] = "6447"
                # # cnf["destinations"] = f"{endpoint.ip_address}:{endpoint.port_x}"

                # # # cSpell:ignore mrds SQLR
                # # # Ensure that there is a section with the name of
                # # # "mysql_rest_service"
                # # if "mysql_rest_service" not in router_config.sections():
                # #     router_config["mysql_rest_service"] = {}

                # # cnf = router_config["mysql_rest_service"]
                # # cnf["mysql_user"] = "dba"
                # # cnf["mysql_password"] = "MySQLR0cks!"
                # # cnf["mysql_read_only_route"] = "classic"
                # # cnf["mysql_read_write_route"] = "classic"

                if interactive:
                    print("Writing updated MySQL Router configuration file...")

                # Write config to in-memory stream
                with io.BytesIO() as router_config_bytes_stream:
                    with io.StringIO() as router_config_stream:
                        router_config.write(router_config_stream)

                        # Seek to the beginning of the text stream
                        router_config_bytes_stream.write(
                            router_config_stream.getvalue().encode("utf-8"))
                        router_config_bytes_stream.seek(0)

                        # Write out new config file to remote instance
                        try:
                            conn.put_local_file_object(
                                router_config_bytes_stream,
                                "/home/opc/mysqlrouter.conf")
                        except Exception as e:
                            raise Exception(
                                "Could not upload router config file. "
                                f"{str(e)}")

                # Move config to final place and fix privileges
                conn.execute(
                    "sudo cp /home/opc/mysqlrouter.conf /etc/mysqlrouter/mysqlrouter.conf")
                conn.execute(
                    "sudo chown mysqlrouter:mysqlrouter /etc/mysqlrouter/mysqlrouter.conf")
                conn.execute("sudo rm /home/opc/mysqlrouter.conf")

                # # If there was an error, print it
                # last_error = conn.get_last_error()
                # if last_error != "":
                #     raise Exception(
                #         "Could not upload router config file. "
                #         f"ERROR: {last_error}")

                # Install the SSL certificate
                if ssl_cert:
                    # Restart NGINX
                    conn.execute("sudo systemctl restart nginx.service")

                    ssl_cert_created = False
                    while not ssl_cert_created and domain_name != "":
                        # Check for one minute if the domain name points to the instance's ip address
                        if interactive:
                            print(
                                f"\nCreating the SSL certificate for {domain_name} ...")

                        try:
                            i = 0
                            while not ssl_cert_created and i < 5:
                                # cSpell:ignore certbot certonly webroot
                                # conn.execute(
                                #     f"sudo certbot certonly --webroot -w /usr/share/nginx/html -d {domain_name} --agree-tos "
                                #     "--register-unsafely-without-email --key-type rsa")
                                output = conn.execute(f"sudo /home/opc/.acme.sh/acme.sh --issue -d {domain_name} "
                                                      "--webroot /usr/share/nginx/html "
                                                      "--force --server letsencrypt --home /home/opc/.acme.sh")
                                last_error = conn.get_last_error()
                                if last_error != "":
                                    if i == 0 and interactive:
                                        print("\nATTENTION: Please create a DNS A record using the following values.\n"
                                              f"Domain: {domain_name}\n"
                                              f"Destination TCP/IP address: {public_ip}")
                                        print(
                                            f"\nWaiting for DNS A record creation ...", end="")
                                    time.sleep(10)
                                    if interactive:
                                        print(".", end="")
                                else:
                                    ssl_cert_created = True
                                i += 1
                        except:
                            pass

                        if not ssl_cert_created:
                            if interactive:
                                print(f"Failed to create the SSL certificate for {domain_name}. {output} {last_error}\n"
                                      "Please correct the domain name or leave empty to cancel.")

                            try:
                                domain_name = core.prompt(
                                    f"Domain Name for {public_ip}: ")
                                if not domain_name:
                                    if interactive:
                                        print(
                                            "Skipping SSL certificate generation.")
                                    domain_name = ""
                                    continue
                            except:
                                domain_name = ""
                                continue

                    if ssl_cert_created:
                        # Install Certificate
                        conn.execute("sudo mkdir -p /etc/pki/nginx/private")
                        # cSpell:ignore reloadcmd
                        output = conn.execute(f"sudo /home/opc/.acme.sh/acme.sh --install-cert -d {domain_name} "
                                              "--key-file /etc/pki/nginx/private/key.pem "
                                              "--fullchain-file /etc/pki/nginx/cert.pem "
                                              '--reloadcmd "sudo systemctl restart nginx.service" '
                                              "--force --home /home/opc/.acme.sh")
                        last_error = conn.get_last_error()
                        if last_error != "":
                            raise Exception(
                                f"Failed to install SSL certificate. {output} {last_error}")

                if interactive:
                    print("Writing web server configuration...")

                # cSpell:ignore letsencrypt fullchain privkey
                if domain_name != "":
                    ssl_cert_path = "/etc/pki/nginx/cert.pem"
                    ssl_cert_key_path = "/etc/pki/nginx/private/key.pem"
                    # ssl_cert_path = f"/etc/letsencrypt/live/{domain_name}/fullchain.pem"
                    # ssl_cert_key_path = f"/etc/letsencrypt/live/{domain_name}/privkey.pem"
                else:
                    ssl_cert_path = "/var/lib/mysqlrouter/router-cert.pem"
                    ssl_cert_key_path = "/var/lib/mysqlrouter/router-key.pem"

                nginx_config = f"""server {{
    listen 443 ssl http2;

    {f"server_name {domain_name};" if domain_name != "" else ""}

    ssl_certificate {ssl_cert_path};
    ssl_certificate_key {ssl_cert_key_path};

    # Allow large attachments
    client_max_body_size 128M;

    location / {{
        proxy_pass http://127.0.0.1:8446;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
    }}
}}
"""

                # Write config to in-memory stream
                with io.BytesIO() as nginx_config_bytes_stream:
                    with io.StringIO() as nginx_config_stream:
                        nginx_config_stream.write(nginx_config)

                        # Seek to the beginning of the text stream
                        nginx_config_bytes_stream.write(
                            nginx_config_stream.getvalue().encode("utf-8"))
                        nginx_config_bytes_stream.seek(0)

                        # Write out new config file to remote instance
                        try:
                            conn.put_local_file_object(
                                nginx_config_bytes_stream,
                                "/home/opc/mrs.nginx.conf")
                        except Exception as e:
                            raise Exception(
                                "Could not upload router config file. "
                                f"{str(e)}")

                # Move config to final place and fix privileges
                conn.execute(
                    "sudo cp /home/opc/mrs.nginx.conf /etc/nginx/conf.d/mrs.nginx.conf")
                conn.execute(
                    "sudo chown root:root /etc/nginx/conf.d/mrs.nginx.conf")
                conn.execute("sudo rm /home/opc/mrs.nginx.conf")

                # If there was an error, print it
                last_error = conn.get_last_error()
                if last_error != "":
                    raise Exception(
                        "Could not upload web server config file. "
                        f"ERROR: {last_error}")

                if interactive:
                    print("Opening Firewall ports...")

                # Open MySQL Router ports on the firewall
                if port_forwarding:
                    conn.execute(
                        "sudo firewall-cmd --zone=public --permanent --add-port=6446-6449/tcp")
                # If mrs was requested but no public_ip, open the port for a Load Balancer
                if mrs and not public_ip:
                    conn.execute(
                        "sudo firewall-cmd --zone=public --permanent --add-port=8446/tcp")
                conn.execute("sudo firewall-cmd --reload")

                # If mrs was requested but no public_ip, open the port for a Load Balancer
                # cSpell:ignore semanage mysqld
                if mrs and not public_ip:
                    conn.execute(
                        "sudo semanage port -a -t mysqld_port_t -p tcp 8446")

                if interactive:
                    print("Restarting MySQL Router...")

                # Restart mysqlrouter.service
                conn.execute("sudo systemctl restart mysqlrouter.service")

                if interactive:
                    print("Restarting web server...")

                # Restart NGINX
                conn.execute("sudo systemctl restart nginx.service")

            # Add ingress rules for MySQL ports to security list
            compute.add_ingress_port_to_security_lists(
                security_lists=sec_lists, port=6446,
                description="Classic MySQL Protocol RW via Router",
                compartment_id=compartment_id, config=config,
                interactive=interactive,
                raise_exceptions=raise_exceptions)
            compute.add_ingress_port_to_security_lists(
                security_lists=sec_lists, port=6447,
                description="Classic MySQL Protocol RO via Router",
                compartment_id=compartment_id, config=config,
                interactive=interactive,
                raise_exceptions=raise_exceptions)
            compute.add_ingress_port_to_security_lists(
                security_lists=sec_lists, port=6448,
                description="MySQL X Protocol RW via Router",
                compartment_id=compartment_id, config=config,
                interactive=interactive,
                raise_exceptions=raise_exceptions)
            compute.add_ingress_port_to_security_lists(
                security_lists=sec_lists, port=6449,
                description="MySQL X Protocol RO via Router",
                compartment_id=compartment_id, config=config,
                interactive=interactive,
                raise_exceptions=raise_exceptions)
            if mrs and not public_ip:
                compute.add_ingress_port_to_security_lists(
                    security_lists=sec_lists, port=8446,
                    description="MRS HTTP via Router",
                    compartment_id=compartment_id, config=config,
                    interactive=interactive,
                    raise_exceptions=raise_exceptions)
            elif mrs:
                compute.add_ingress_port_to_security_lists(
                    security_lists=sec_lists, port=443,
                    description="MRS HTTPS via Router",
                    compartment_id=compartment_id, config=config,
                    interactive=interactive,
                    raise_exceptions=raise_exceptions)
                compute.add_ingress_port_to_security_lists(
                    security_lists=sec_lists, port=80,
                    description="MRS HTTP via Router",
                    compartment_id=compartment_id, config=config,
                    interactive=interactive,
                    raise_exceptions=raise_exceptions)

            if interactive:
                endpoint_address = domain_name if domain_name != "" else public_ip
                print("\nNew endpoint successfully created.\n")
                if port_forwarding:
                    print(f"    Classic MySQL Protocol: {endpoint_address}:6446\n"
                          f"    MySQL X Protocol: {endpoint_address}:6448\n")
                if mrs:
                    print(
                        f"    MySQL REST Service HTTPS: https://{endpoint_address}/\n")
                if port_forwarding:
                    print(
                        f"Example:\n    mysqlsh mysql://{mysql_user_name}@{endpoint_address}:6446")

            if not return_formatted:
                return {
                    "ip": public_ip,
                    "domainName": domain_name,
                    "port": 6446,
                    "port_x": 6447,
                    "rest_http": 443
                }
        except Exception as e:
            raise Exception(
                f"Could not configure the compute instance '{instance_name}' "
                f"at {public_ip}.\n{str(e)}")
    except Exception as e:
        if raise_exceptions:
            raise
        print(f"ERROR: {str(e)}")