Tag: technology

Generate User creation script for Redshift

"""
This script generates a user creation script and a list of ALTER GROUP scripts to add the user to the corresponding groups.

Sample Execution:
python Generate_user_creation_script.py
Enter the username for the new user: test_user
Enter the schema names (comma-separated, e.g., 'etl,hr,public'): "etl","hr","public"

Output:
CREATE USER "test_user" PASSWORD 'Hidden1!';
ALTER GROUP etl_ro ADD USER "test_user";
ALTER GROUP hr_ro ADD USER "test_user";
ALTER GROUP public_ro ADD USER "test_user";

"""

def strip_and_parse_schemas(schemas_string):
    """
    Strips the double quotes and commas from a given string of schema names and returns a list of schema names.
    
    :param schemas_string: String of schema names, e.g. '"etl","hr","public"'
    :return: List of schema names ['etl', 'hr', 'public']
    """
    schemas_list = schemas_string.replace('"', '').split(',')
    return schemas_list

def generate_create_user_script(username, password):
    """
    Generates SQL script to create a new user with a given username and password.
    
    :param username: The username of the new user
    :param password: The password for the new user
    :return: SQL statement for user creation
    """
    create_user_script = f'CREATE USER "{username}" PASSWORD \'{password}\';'
    return create_user_script

def generate_alter_group_scripts(schemas, username):
    """
    Generates SQL statements to add the user to the pre-existing groups for each schema.
    
    :param schemas: List of schema names
    :param username: The username to be added to the groups
    :return: List of ALTER GROUP SQL statements
    """
    alter_group_scripts = []
    for schema in schemas:
        group_name = f"{schema}_ro"  # Dynamically generate the group name (e.g., "etl_ro")
        alter_group_script = f'ALTER GROUP {group_name} ADD USER "{username}";'
        alter_group_scripts.append(alter_group_script)
    return alter_group_scripts

def print_scripts(schemas, username, password):
    """
    Prints the user creation script and the ALTER GROUP scripts to add the user to the corresponding groups.
    
    :param schemas: List of schema names
    :param username: The username of the new user
    :param password: The password for the new user
    """
    print(generate_create_user_script(username, password))
    alter_group_scripts = generate_alter_group_scripts(schemas, username)
    for script in alter_group_scripts:
        print(script)

def main():
    # Prompt for username input
    username = input("Enter the username for the new user: ")
    
    # Prompt for schema array input (comma-separated string)
    schemas_string = input("Enter the schema names (comma-separated, e.g., 'etl,hr,public'): ")
    
    # Strip and parse the schema names
    schemas = strip_and_parse_schemas(schemas_string)
    
    # Set the password (or prompt for it if you'd like)
    password = "Hidden1!"  # You can customize this if needed
    
    # Generate and print the scripts
    print_scripts(schemas, username, password)

if __name__ == "__main__":
    main()

Tomcat 5/6/7/8 – How To Install An SSL Certificate

Tomcat–not my favorite.

General Steps

  1. Create a Key and Certificate Request
  2. Issue the Certificate from your favorite Registrar
  3. Merge the Certificate into a Tomcat File

Create a Key and Certificate Request

On your favorite Linux or Windows box, make sure you have OpenSSL.

I am making a directory called /home/keystore.  Seems fitting.

So:

mkdir /home/keystore

First we need a Private Key.  This is yours and yours alone.

So, the private key is critical.  It’s your unique identifier for this SSL cert.

Next, we need to generate the request to send to GoDaddy, InstantSSL, etc

Now, the command:

You are going to be prompted for all of the details as follows. For Wildcard, use *.mydomain.com. For other hosts, just use the hostname. ie mydomain.com (you will get www automatically)


Country Name (2 letter code) [XX]:US
State or Province Name (full name) []:Texas
Locality Name (eg, city) [Default City]:Tim
Organization Name (eg, company) [Default Company Ltd]:My Domain Inc
Organizational Unit Name (eg, section) []:IT
Common Name (eg, your name or your server's hostname) []:*.mydomain.com
Email Address []:support@mydomain.com

Press Enter on the Extra fields, no password needed.

Ok, once finished, take your CSR and submit to your provider. Once you submit, you wait and then you will get your certificate. You may have to check email to approve it.

Now the Easy Part!

Merge the Certificate into a Tomcat File

If you are a GoDaddy Customer, you will get two files. Other providers might send you on a wild goose chase for the Bundle file.

6e00664a60ac4578.crt  - This is the Actual Certificate
gd_bundle-g2-g1.crt   - This is your Bundle file with all the certificate chain data from GoDaddy

For simplicity and understanding, let’s rename the file:

mv 6e00664a60ac4578.crt mydomain.crt

Now, let’s make the Tomcat keystore container

openssl pkcs12 -export -chain -CAfile gd_bundle-g2-g1.crt -in mydomain.crt -inkey private.key -out keystore.tomcat -name tomcat -passout pass:changeit

Ok, you have everything you need. Now, setup Tomcat.

Installing the Certificate in Tomcat

Let’s copy the file to our tomcat installation configuration directory.  My tomcat was in /usr/local/tomcat5

cp keystore.tomcat /usr/local/tomcat5/conf

Now, we need to enable SSL.  So, we need to edit the server-wide server.xml file.  Find the section like this:

<!-- Define a SSL Coyote HTTP/1.1 Connector on port 8443 -->
<!--
<Connector port="8443"
maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
enableLookups="false" disableUploadTimeout="true"
acceptCount="100" debug="0" scheme="https" secure="true"
clientAuth="false" sslProtocol="TLS" />
-->

Replace it.  Mine looks like this:


<!-- Define a SSL Coyote HTTP/1.1 Connector on port 8443 -->
<Connector port="443"
maxHttpHeaderSize="8192" maxThreads="250" minSpareThreads="25" maxSpareThreads="75"
enableLookups="false" disableUploadTimeout="true"
acceptCount="100" debug="0" scheme="https" secure="true" SSLEnabled="true"
clientAuth="false" sslProtocol="TLS" keyAlias="tomcat"
keystoreFile="/usr/local/tomcat5/conf/keystore.tomcat"
keystorePass="changeit"
keystoreType="PKCS12" />

Lastly, find any other references to port 8443 in the server.xml file and replace them with just 443.

Now, restart Tomcat and enjoy your newly functioning wildcard certificate.

I hope this bridges the gaps on some of the other articles out there.