LiberAnalytics

Freeing analytics from the ordinary.

Connecting Python to a Postgresql database

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

Connecting Python to a Postgresql database

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 script is an example you can adapt. Replace placeholders like 'region' and 'keyid' with values from your own AWS and PostgreSQL setup. You may also want to add your own error handling and logging.

# ------------------------------------------------------------------

# 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  =  os . getenv ( '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,  0o000)

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 running SQL queries. The below aws_postgresql_connect.py program reads the encrypted data from the config file created by the aws_postgresql_config.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 library to connect to our PostgreSQL cluster in AWS EC2 and run a sample query. This connect script assumes you already ran aws_postgresql_config.py with the same host and database name, and that your AWS credentials allow calls to KMS.

# ------------------------------------------------------------------

# 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.

# Replace 'your-db-host' and 'your-db-name' with the values

# you used when running aws_postgresql_config.py.

config_dir  =  os . getenv ( 'HOME' )  +  '/'  +  'conn'  +  '/'  +  'your-db-host'

config_file  =  config_dir   +  '/'   +  'your-db-name'   +  '.ini'

# Make config file readable.

os. chmod( config_file,  0o440)

# 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,  0o000)

# 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