Can't connect to Azure Postgresql with a Python Function App

Question

I've followed this, this and this and a lot of other resources and still can't get connected to the database. That's why I've opened this question. I'm beyond frustrated. Hopefully someone can steer me into the right direction. Below is the steps that I've done.

1. I did steps 1-3 from this article. By the way should I use the Application ID of the azure function or the Principal ID of the System Assigned Identity when creating a role for the function in the database? I've used the Application ID.
2. I've added all the possible outbound ip addresses of the azure function to pass the database firewall.
3. Function is on Linux Consumption plan. According to this article you need to use the 2017-09-01 api version if function is on Linux Consumption plan.

I did not find anything in the function properties/configuration on os.environ["MSI_ENDPOINT"], os.environ["MSI_SECRET"] so I'm assuming that these are being assigned by microsoft when the function gets executed. Here's the Exception that I'm getting when running the function:

"Exception while executing function: Functions.FunctionTrigger Result: Failure Exception: UnboundLocalError: local variable 'connection' referenced before assignment. if connection:"

Furthermore, I can not see any logs even though I'm writing them in the function body. Not in the function insights nor in the storage account that is defined for the function. So basically I'm flying blind. Moreover, initially I was using psycopg2 and I was receiving the exception in here. Then I switched to psycopg2-binary and the exception went away. Any help would be appreciated.

import logging
import os
import azure.functions as func
import psycopg2
from psycopg2 import Error
import requests

def main(mytimer: func.TimerRequest) -> None:
    utc_timestamp = datetime.datetime.utcnow().replace(
        tzinfo=datetime.timezone.utc).isoformat()

    if mytimer.past_due:
        logging.info('The timer is past due!')

    logging.info('Python timer trigger function ran at %s', utc_timestamp)

    try:
        #get access token
        # identity_endpoint = os.environ["IDENTITY_ENDPOINT"]
        # identity_header = os.environ["IDENTITY_HEADER"]
        # resource_uri="https://database.windows.net/"
        # token_auth_uri = f"{identity_endpoint}?resource={resource_uri}&api-version=2019-08-01"
        # head_msi = {'X-IDENTITY-HEADER':identity_header}
        # resp = requests.get(token_auth_uri, headers=head_msi)
        # access_token = resp.json()['access_token']
        msi_endpoint = os.environ["MSI_ENDPOINT"]
        msi_header = os.environ["MSI_SECRET"]
        # resource_uri="https://database.windows.net/"
        resource_uri="https://ossrdbms-aad.database.windows.net"
        token_auth_uri = f"{msi_endpoint}?resource={resource_uri}&api-version=2017-09-01"
        head_msi = {'secret':msi_header}
        resp = requests.get(token_auth_uri, headers=head_msi)
        access_token = resp.json()['access_token']

        logging.info(msi_endpoint)
        logging.info(msi_header)
        logging.info(access_token)
        
        USER = 'name of the role that I created for the function'
        connection = psycopg2.connect(
            user = USER,
            password = access_token,
            host = HOST,
            database = DB,
            port = '5432'
        )

        cursor = connection.cursor()

        query = "SELECT * FROM table;"
        cursor.execute(query)


    except (Exception, Error) as error:
        print(error)
        logging.info(error)

    finally:
        if connection:
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")

Answer 1

I am not sure if your requirement is to connect to Azure PostgreSQL using Managed Identity. If not, then the possible issue is using psycopg2. use psycopg2-binary instead. Add psycopg2-binary to your requirements.txt. This will fix the issue.