Connecting Python to a Postgresql database

July 8, 2025

Discover how to connect Python to a Postgresql database without storing credentials or keys on filesystem.

Connecting Python to Postgresql is made seamless with the psycopg2 library. This article demonstrates how to connect to a Postgresql database using Python without storing credentials in plain text or in the .pgpass file. The architecture we will be using consists of a Postgresql database hosted on an Ubuntu 24 (Noble) Amazon Web Services (AWS) EC2 instance and Python 3.10 running on Ubuntu 22 (Jammy). We will use a symmetric AWS KMS key to encrypt and decrypt database credentials.

Requirements

Python 3 (preferably running on Ubuntu)
Python libraries: psycopg2, boto3
AWS: Key Management Service (KMS) Customer managed symmetric key
Postgresql database cluster (needs to accept TCP/IP connections)

Get database connection parameters

First we will need to know the database host and database name which will be used to create a config file with .ini extension in the user's home directory. Then we will prompt for the user name and password using Python's getpass to avoid echoing back the data input by the user. We'll use our AWS KMS key to perform encryption of the user name and password. Lastly the encrypted data will be written to the config file w/ permissions set to non-readable, non-writable, and non-executable. The following code shows how we can do the aforementioned in Python.

    # ------------------------------------------------------------------
					
    # aws_postgresql_config.py
    #
    # Creates a .ini file containing encrypted credentials used
					
    # to connect to an AWS EC2 hosted postgresql database.
					
    # ------------------------------------------------------------------
					

    import pwd
    import sys
    import os
    from getpass import getpass
    import json
    import traceback
    import base64
    import boto3

    def main():

        # Instantiate an AWS KMS client instance.
        aws_kms = boto3.client('kms', 'region')

        # KMS key id.
        the_key = 'keyid'

        # Prompt for the connection params.
        print('Please provide connection parameters for database:' + '\n')
        db_host = getpass('Please enter database host:')
        db_name = getpass('Please enter database name:')
        db_user = getpass('Please enter database user name:')
        db_auth = getpass('Please enter user password:')

        # Config file.
        config_dir = ['HOME'] + '/' + 'conn' +'/' + db_host
        config_file = config_dir + '/' + db_name + '.ini'

        # Concat + delimit params, convert to bytes, then base64 encode.
					
        delim = chr(7)
        params = db_host + delim + db_name + delim + db_user + delim + db_auth
        byte_params= params.encode('utf-8')
        encode_params = base64.b64encode(byte_params)

        # Encrypt the params.
        encryption_data = aws_kms.encrypt(
            KeyId = the_key,
            Plaintext = encode_params,
            EncryptionAlgorithm = 'SYMMETRIC_DEFAULT'
        )

        # Write the encryption params as binary to the config file.
					
        with open(config_file, 'wb') as file:
            file.write(encryption_data['CiphertextBlob'])

        # Reset file perms.
        os.chmod(config_file, 000)

Connecting to the postgresql database

Now that we have the connection config file created we are ready to connect to the postgresql database cluster and begin sql'ing away. The below aws_postgresql_connect.py program reads the encrypted data from the config file created by the aws_postgresql_connect_init.py program then decrypts the byte data with the same AWS KMS key. It is important to include the encryption algorithm (EncryptionAlgorithm='RSAES_OAEP_SHA_256') in the decrypt call if using an asymmetric key in AWS KMS. In this case we are using a symmetric key so the EncryptionAlgorithm='SYMMETRIC_DEFAULT' encryption algorithm property isn't needed but we still include it for posterity sake. The data read is then split by delimiter to derive user name/password and the config file permissions are disabled. Finally we use the imported psycopg2 libarary to connect to our postgresql cluster in AWS EC2 and run a sample query.

    # ------------------------------------------------------------------
					
    # aws_postgresql_connect.py
    # 
    # Connects to an AWS EC2 hosted postgresql database by reading
					
    # and decrypting credentials stored in a config file. Dependent
					
    # on the aws_postgresql_config.py having been run first.
					
    # ------------------------------------------------------------------
					

    import pwd
    import sys
    import os
    import base64
    import boto3
    import psycopg2

    def main():

        # Instantiate an AWS KMS client instance.
        aws_kms = boto3.client('kms', 'region')

        # KMS key id.
        the_key = 'keyid'

        # Config file.
        config_dir = ['HOME'] + '/' + 'conn' +'/' + db_host
        config_file = config_dir + '/' + db_name + '.ini'

        # Make config file readable.
        os.chmod(config_file, 440)

        # Get the connection credentials from config file.
					
        with open(config_file, 'rb') as file:
            credentials_bytes = file.read()

        # Reset config file perms.
        os.chmod(config_file, 000)        
        
        # Decrypt the connection credentials.
        credentials_decrypt = aws_kms.decrypt(
            CiphertextBlob = credentials_bytes,
            KeyId = f'{the_key}',
					
            EncryptionAlgorithm='SYMMETRIC_DEFAULT'
        )

        # Decode the credentials and split by delimiter.
					
        delimiter = chr(7)
        credentials_decode = base64.b64decode(credentials_decrypt['Plaintext'])
        credentials = bytes.decode(credentials_decode, 'utf-8')
        db_data = credentials.split(delimiter)
        db_host = db_data[0]
        db_name = db_data[1]
        db_user = db_data[2]
        db_auth = db_data[3]

        # Form a connection string & connect.
        connection_string = \
        " host=" + db_host + \
        " dbname=" + db_name + \
        " user=" + db_user + \
        " password=" + db_auth + \
        " port=5432"
        conn = psycopg2.connect(connection_string)

        # Example query.
        cur = conn.cursor()
        cur.execute('SELECT CURRENT_DATE;')
        rs = cur.fetchall()

        # Iterate through resultset.
        for row in rs:
            print(row)

        # Close cursor and connection.
        cur.close()
        conn.close()

Key Takeaways

In this article we have connected to a postgresql database cluster hosted in AWS EC2 without storing database credentials or encryption keys anywhere on the filesystem. Although the code presented could be refactored to eliminate redundancies or abstracted into a class, the key points listed below could help in securely connecting to databases.

Minimize visibility and access to database credentials
If hosting in a cloud service provider, make use of symmetric keys to protect sensitive data such as user names or passwords
Use encryption when storing database credentials on filesystem
Try to avoid storing encryption keys or plain text user names/passwords

LiberAnalytics