This post is a part of the AI-200: Developing AI Cloud Solutions on Azure  Exam Prep Hub.
This topic falls under these sections:
Develop AI solutions by using Azure data management services (25–30%)
   --> Develop AI solutions by using Azure Database for PostgreSQL
      --> Connect and query Azure Database for PostgreSQL by using SDKs

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Overview

Azure Database for PostgreSQL is a fully managed PostgreSQL service that provides a familiar PostgreSQL database engine while Azure manages much of the underlying infrastructure, availability, maintenance, and scaling.

For AI-200, developers need to understand how applications connect to Azure Database for PostgreSQL and how they use programming-language client libraries to execute SQL statements.

The key idea is:

Your application normally connects to Azure Database for PostgreSQL through a PostgreSQL client library/driver, establishes a secure connection, executes parameterized SQL commands, processes the results, and properly manages connections and transactions.

Azure Database for PostgreSQL supports commonly used PostgreSQL client interfaces including:

• Python — psycopg
• C#/.NET — Npgsql
• Java — JDBC
• Node.js — pg
• Go — PostgreSQL drivers such as pgx or pq
• PHP — php-pgsql
• Ruby — pg
• C/C++ — PostgreSQL client libraries
• ODBC — psqlODBC

These are PostgreSQL client libraries rather than an Azure-specific database SDK. (Microsoft Learn)

1. Understand the Connection Architecture

A typical application architecture looks like this:

Application
    |
    | PostgreSQL client library
    | (Npgsql, psycopg, JDBC, pg, etc.)
    v
Secure connection
    |
    | TLS
    v
Azure Database for PostgreSQL
    |
    v
PostgreSQL database
    |
    +-- Tables
    +-- Views
    +-- Indexes
    +-- Functions
    +-- Extensions

The application is responsible for using a PostgreSQL-compatible client library. Azure provides the managed PostgreSQL server.

For example:

C# application
      |
      v
   Npgsql
      |
      v
Azure Database for PostgreSQL

or:

Python application
      |
      v
   psycopg
      |
      v
Azure Database for PostgreSQL

This distinction is important for the exam.

Azure SDKs are commonly used to manage Azure resources and services.

PostgreSQL client libraries are used to communicate with the PostgreSQL database itself.

2. Obtain the Connection Information

An application generally needs:

• Server hostname
• Database name
• Port
• Username
• Authentication information
• TLS/SSL configuration

The standard PostgreSQL port is:

5432

An Azure Database for PostgreSQL server typically has a hostname similar to:

myserver.postgres.database.azure.com

A connection string might look conceptually like:

host=myserver.postgres.database.azure.com
port=5432
dbname=mydatabase
user=myuser
password=<secret>
sslmode=require

The exact connection-string syntax varies by client library.

Azure’s current guidance shows PostgreSQL connections using TLS and port 5432. (Microsoft Learn)

3. Secure Connections with TLS

Applications should connect to Azure Database for PostgreSQL using encrypted connections.

Azure Database for PostgreSQL supports TLS 1.2 and TLS 1.3 and rejects TLS 1.0 and 1.1. (Microsoft Learn)

For example, a connection string can include:

sslmode=require

This tells the client to use an encrypted connection.

More stringent certificate validation can be configured using settings such as:

sslmode=verify-ca

or:

sslmode=verify-full

verify-full provides stronger validation because it verifies both the certificate chain and the server hostname.

Exam tip

If a question describes:

“The application must communicate with PostgreSQL securely.”

Look for TLS/SSL configuration rather than simply changing the database port.

Changing the port does not provide encryption.

4. Authentication Options

Applications can authenticate to Azure Database for PostgreSQL in several ways.

Common approaches include:

PostgreSQL authentication

The application supplies a PostgreSQL username and password.

Conceptually:

Application
    |
    | username + password
    v
PostgreSQL

This is straightforward but requires careful secret management.

Microsoft Entra authentication

Applications can also authenticate using Microsoft Entra identities.

This allows applications to obtain an access token rather than embedding a PostgreSQL password in application code.

Azure supports both system-assigned and user-assigned managed identities for authentication to Azure Database for PostgreSQL. (Microsoft Learn)

A managed-identity architecture can look like:

Azure App Service / VM / Function / Container
             |
             | Managed identity
             v
       Microsoft Entra ID
             |
             | Access token
             v
Azure Database for PostgreSQL

This can eliminate the need to store a database password in the application.

Exam tip

If a question says:

“The application is hosted in Azure and should access PostgreSQL without storing credentials.”

The likely direction is Microsoft Entra authentication with a managed identity, assuming the relevant service and database configuration support it.

5. Network Connectivity Matters

Successful SDK code does not guarantee a successful connection.

The application must also have network access to the PostgreSQL server.

Azure Database for PostgreSQL Flexible Server supports two primary networking approaches:

• Public access, where allowed IP addresses are controlled through firewall rules
• Private access, using virtual network integration

(Microsoft Learn)

Therefore, when troubleshooting a connection, consider:

Application
   |
   +--> DNS resolution
   |
   +--> Network routing
   |
   +--> Firewall / network rules
   |
   +--> TLS
   |
   +--> Authentication
   |
   +--> Database authorization
   |
   v
PostgreSQL

A connection failure does not necessarily mean the SDK code is incorrect.

6. Python and psycopg

For Python applications, psycopg is a current PostgreSQL client library.

The basic pattern is:

import psycopg
conn = psycopg.connect(
    "host=myserver.postgres.database.azure.com "
    "port=5432 "
    "dbname=mydatabase "
    "user=myuser "
    "password=<password> "
    "sslmode=require"
)
cursor = conn.cursor()
cursor.execute(
    "SELECT id, name FROM products WHERE category = %s",
    ("AI",)
)
rows = cursor.fetchall()
for row in rows:
    print(row)
cursor.close()
conn.close()

The important concepts are:

1. Create a connection.
2. Create a cursor.
3. Execute SQL.
4. Retrieve results.
5. Commit changes when appropriate.
6. Close resources.

Microsoft’s current Python guidance uses psycopg and demonstrates parameterized SQL through cursor.execute(). (Microsoft Learn)

7. Parameterized Queries

One of the most important development practices is to avoid constructing SQL by concatenating user input.

Avoid:

name = request.args["name"]
sql = "SELECT * FROM products WHERE name = '" + name + "'"
cursor.execute(sql)

This can expose the application to SQL injection.

Instead, use parameters:

cursor.execute(
    "SELECT * FROM products WHERE name = %s",
    (name,)
)

The database driver handles the parameter separately from the SQL statement.

Why this matters

Parameterized queries provide:

• Better security
• Safer handling of user input
• Cleaner code
• Better separation between SQL and data

Exam clue

If the question says:

“The application accepts user-provided values and must prevent SQL injection.”

The answer should generally involve parameterized queries, not string concatenation.

8. C#/.NET and Npgsql

For .NET applications, Npgsql is the commonly recommended PostgreSQL ADO.NET data provider.

(Microsoft Learn)

Install it using:

dotnet add package Npgsql

A basic example is:

using Npgsql;
var connectionString =
    "Host=myserver.postgres.database.azure.com;" +
    "Port=5432;" +
    "Database=mydatabase;" +
    "Username=myuser;" +
    "Password=<password>;" +
    "SSL Mode=Require;";
await using var connection =
    new NpgsqlConnection(connectionString);
await connection.OpenAsync();
await using var command =
    new NpgsqlCommand(
        "SELECT id, name FROM products WHERE category = @category",
        connection);
command.Parameters.AddWithValue("category", "AI");
await using var reader =
    await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    Console.WriteLine(
        $"{reader.GetInt32(0)} - {reader.GetString(1)}");
}

Notice the use of:

@category

instead of concatenating a value into the SQL string.

9. JDBC for Java Applications

Java applications commonly use the PostgreSQL JDBC driver.

A conceptual example is:

String url =
    "jdbc:postgresql://myserver.postgres.database.azure.com:5432/mydatabase"
    + "?sslmode=require";
Connection connection =
    DriverManager.getConnection(
        url,
        username,
        password);
PreparedStatement statement =
    connection.prepareStatement(
        "SELECT id, name FROM products WHERE category = ?");
statement.setString(1, "AI");
ResultSet results = statement.executeQuery();
while (results.next()) {
    System.out.println(results.getString("name"));
}

The important pattern is:

Connection
    ↓
PreparedStatement
    ↓
Parameters
    ↓
executeQuery()
    ↓
ResultSet

Exam tip

If you see:

PreparedStatement

think:

Parameterized SQL and protection against SQL injection.

10. Node.js and the pg Package

Node.js applications can use the PostgreSQL pg package.

Conceptually:

const { Client } = require("pg");
const client = new Client({
    host: "myserver.postgres.database.azure.com",
    port: 5432,
    database: "mydatabase",
    user: "myuser",
    password: "<password>",
    ssl: true
});
await client.connect();
const result = await client.query(
    "SELECT id, name FROM products WHERE category = $1",
    ["AI"]
);
console.log(result.rows);
await client.end();

Notice that PostgreSQL parameters use placeholders such as:

$1
$2
$3

rather than constructing SQL dynamically.

11. Querying Data

Applications can use the client library to execute standard PostgreSQL SQL.

For example:

SELECT id, name, price
FROM products
WHERE category = 'AI'
ORDER BY price DESC;

The client library sends the SQL statement to PostgreSQL and returns the results to the application.

A typical workflow is:

Build SQL
   ↓
Bind parameters
   ↓
Execute command
   ↓
Database processes query
   ↓
Return rows
   ↓
Application processes rows

12. Executing INSERT, UPDATE, and DELETE

SDK/client libraries aren’t limited to SELECT.

They can execute data modification statements.

INSERT

INSERT INTO products (name, category, price)
VALUES ($1, $2, $3);

UPDATE

UPDATE products
SET price = $1
WHERE id = $2;

DELETE

DELETE FROM products
WHERE id = $1;

Applications must properly handle transactions for operations where multiple changes need to succeed or fail together.

13. Transactions

A transaction groups multiple database operations into a logical unit.

For example:

BEGIN
   |
   +--> INSERT order
   |
   +--> INSERT order item
   |
   +--> UPDATE inventory
   |
COMMIT

If something fails:

BEGIN
   |
   +--> INSERT order
   |
   +--> INSERT order item
   |
   +--> ERROR
   |
ROLLBACK

This provides atomicity.

Typical transaction pattern

with psycopg.connect(connection_string) as conn:
    with conn.cursor() as cursor:
        cursor.execute(
            "INSERT INTO orders(customer_id) VALUES (%s)",
            (customer_id,)
        )
        cursor.execute(
            "UPDATE inventory SET quantity = quantity - %s "
            "WHERE product_id = %s",
            (quantity, product_id)
        )

If an exception occurs within the transaction context, the transaction can be rolled back rather than leaving partially applied changes.

14. Connection Pooling

Opening a new database connection for every request can be inefficient.

Consider a web API receiving 1,000 requests:

Request 1 → Open connection → Query → Close
Request 2 → Open connection → Query → Close
Request 3 → Open connection → Query → Close
...

This creates unnecessary connection overhead.

A connection pool instead maintains a set of reusable connections:

                Connection Pool
              +------------------+
Request ----->| Connection 1     |
Request ----->| Connection 2     |
Request ----->| Connection 3     |
Request ----->| Connection 4     |
              +------------------+

The application:

1. Requests a connection.
2. Uses it.
3. Returns it to the pool.

Benefits

Connection pooling can:

• Reduce connection establishment overhead
• Improve application performance
• Handle concurrent workloads more efficiently
• Reduce unnecessary database connection churn

Important distinction

A connection pool is not the same thing as a database transaction.

A pool manages reusable connections.

A transaction manages the atomicity of database operations.

15. Asynchronous Database Operations

Modern applications often use asynchronous database operations.

For example, .NET applications can use:

await connection.OpenAsync();

and:

await command.ExecuteReaderAsync();

This helps applications avoid blocking a thread while waiting for database I/O.

This can be particularly important for:

• Web APIs
• Serverless applications
• High-concurrency applications
• AI applications processing many requests

16. Handling Query Results

A database query may return:

• Zero rows
• One row
• Many rows

Applications should not assume that a result always exists.

For example:

SELECT id, name
FROM products
WHERE id = $1;

The application should handle the case where no matching product exists.

For multiple rows, the application generally iterates over a cursor, reader, or result set.

17. Avoid Retrieving More Data Than Necessary

A common application mistake is:

SELECT *
FROM products;

when the application only needs two columns.

Prefer:

SELECT id, name
FROM products;

Similarly, use filtering:

SELECT id, name
FROM products
WHERE category = $1;

rather than retrieving an entire table and filtering the results in application code.

This reduces:

• Data transferred over the network
• Application memory usage
• Database processing in some scenarios
• Unnecessary work

18. Use the Database to Perform Database Work

Suppose an application needs the average product price.

Avoid:

Retrieve every product
       ↓
Send all products to application
       ↓
Calculate average in application

Prefer:

SELECT AVG(price)
FROM products;

The database is optimized to perform database operations.

Other useful SQL operations include:

COUNT()
SUM()
AVG()
MIN()
MAX()
GROUP BY
ORDER BY
JOIN

This is particularly relevant to AI applications because unnecessarily moving large datasets into application memory can become expensive and slow.

19. Stored Procedures and Functions

PostgreSQL supports database-side functions and procedures.

An application can invoke them through its client library.

For example:

SELECT calculate_customer_score($1);

This can be useful when business or database logic is intentionally centralized in PostgreSQL.

However, don’t automatically move all application logic into database functions.

Consider:

• Maintainability
• Performance
• Security
• Deployment complexity
• Transaction requirements
• Whether the logic belongs in the database or application

20. Connection Lifecycle

A reliable application should carefully manage database resources.

The general lifecycle is:

Create/acquire connection
          ↓
Open connection
          ↓
Create command/cursor
          ↓
Execute SQL
          ↓
Process results
          ↓
Commit or rollback
          ↓
Close/release resources

Using language-supported resource-management features is preferable.

For example, C# uses:

await using

and Python can use:

with

This reduces the chance of leaking connections or other resources.

21. Secrets Should Not Be Hard-Coded

Avoid:

password = "MySuperSecretPassword123!"

inside application source code.

Instead, use a secure configuration mechanism.

For Azure applications, a common architecture is:

Application
     |
     v
Managed Identity
     |
     v
Azure Key Vault
     |
     v
Database credentials/secrets

Or, when using Microsoft Entra authentication, eliminate the need for a database password where appropriate.

This is especially important in production AI applications because database credentials can provide access to sensitive business information.

22. Common Connection Problems

When an application cannot connect, troubleshoot systematically.

Problem 1: Incorrect hostname

Verify the server’s fully qualified domain name.

For example:

myserver.postgres.database.azure.com

Problem 2: Firewall restriction

With public access, the application’s source IP must be allowed by the server’s firewall configuration.

Problem 3: Private networking

If the server uses private access, the application must have appropriate connectivity to the virtual network.

Problem 4: Authentication failure

Verify:

• Username
• Password or token
• Authentication method
• Database permissions

Problem 5: TLS configuration

Verify the client supports the required TLS configuration and that the connection string is configured appropriately.

Problem 6: Wrong database

The server may be reachable, but the requested database may not exist or the user may not have access.

23. Connection Failure vs. Authorization Failure

This distinction is important for troubleshooting questions.

Connection failure

The application cannot establish a connection to PostgreSQL.

Possible causes:

DNS
Firewall
Network
Port
TLS
Server availability

Authentication failure

The server is reachable, but the credentials or authentication mechanism are invalid.

"Who are you?"
       ↓
Authentication

Authorization failure

The user successfully authenticated but doesn’t have permission to perform the requested operation.

"Who are you?"
       ↓
Authentication
       ↓
"What are you allowed to do?"
       ↓
Authorization

A question that says:

“The application successfully connects but receives a permission-denied error when querying a table.”

should lead you toward database permissions, not firewall configuration.

24. SDK/Client Library Selection

A useful AI-200 mental model is:

Azure’s current connection-library guidance lists these types of client interfaces for Azure Database for PostgreSQL Flexible Server. (Microsoft Learn)

Remember:

The client library communicates with PostgreSQL; it isn’t primarily an Azure resource-management SDK.

25. AI Application Considerations

This topic becomes especially important in AI applications.

A typical AI application might look like:

User
  |
  v
AI application
  |
  +--> Azure OpenAI
  |
  +--> Azure Database for PostgreSQL
  |       |
  |       +--> Application data
  |       +--> Embeddings
  |       +--> Vector indexes
  |
  +--> Azure Storage

The application may use PostgreSQL for:

• Relational application data
• Conversation history
• User information
• AI-generated metadata
• Document metadata
• Embeddings
• Vector search

The SDK/client library provides the application with the database connection needed to execute SQL and, when configured, vector-related PostgreSQL operations.

26. Key Exam Takeaways

For AI-200, remember these relationships:

Connection

Application
    ↓
PostgreSQL client library
    ↓
TLS connection
    ↓
Azure Database for PostgreSQL

Python

psycopg

.NET

Npgsql

Java

JDBC

Node.js

pg

Security

TLS
+
secure credential management
+
Microsoft Entra authentication where appropriate
+
managed identities where appropriate

Query security

Parameterized queries
        ↓
Avoid SQL injection

Performance

Connection pooling
+
asynchronous I/O
+
efficient SQL
+
retrieve only required data

Transactions

BEGIN
   ↓
Multiple operations
   ↓
COMMIT
or
ROLLBACK

Troubleshooting

Network
  ↓
TLS
  ↓
Authentication
  ↓
Authorization
  ↓
SQL/query behavior

Practice Exam Questions

Question 1

A Python application hosted in Azure must connect to Azure Database for PostgreSQL and execute parameterized SQL queries. Which client library should the developer use?

A. psycopg
B. azure-storage-blob
C. azure-cosmos
D. redis-py

Answer: A

Explanation

psycopg is a PostgreSQL client library for Python. It provides the functionality required to establish PostgreSQL connections and execute SQL statements.

The other libraries target different Azure services or technologies:

• azure-storage-blob — Azure Blob Storage
• azure-cosmos — Azure Cosmos DB
• redis-py — Redis

The important distinction is that Azure Database for PostgreSQL is accessed using a PostgreSQL client library.

Question 2

A web application accepts a product name from users and uses that value in a PostgreSQL query. Which approach provides the best protection against SQL injection?

A. Use a parameterized query and bind the product name as a parameter.

B. Encode the product name using Base64 before concatenating it into the SQL statement.

C. Store the product name in an Azure Storage blob before executing the query.

D. Disable TLS for the database connection.

Answer: A

Explanation

Parameterized queries separate SQL code from user-supplied values.

For example:

cursor.execute(
    "SELECT * FROM products WHERE name = %s",
    (product_name,)
)

The value is treated as data rather than executable SQL.

Base64 encoding does not prevent SQL injection, and neither Blob Storage nor TLS configuration solves SQL injection.

Question 3

An application is deployed using Azure Database for PostgreSQL with public network access. The application receives a connection timeout. The database server is running and the connection string contains the correct hostname. What should the developer investigate first?

A. Whether the SQL query uses a parameterized statement

B. Whether the database table has an index

C. Whether the application’s source IP address is allowed by the PostgreSQL firewall rules

D. Whether the application has enough memory to process query results

Answer: C

Explanation

With public access, Azure Database for PostgreSQL uses firewall rules to control allowed client IP addresses.

A timeout before a database connection is established points toward network connectivity rather than SQL query construction or database indexing.

The troubleshooting sequence should include:

DNS
→ Network
→ Firewall
→ TLS
→ Authentication
→ Authorization
→ Query

Question 4

A .NET application needs to connect to Azure Database for PostgreSQL and execute SQL statements. Which library is the appropriate PostgreSQL client?

A. Azure.Storage.Blobs

B. Azure.Messaging.ServiceBus

C. Microsoft.Data.SqlClient

D. Npgsql

Answer: D

Explanation

Npgsql is the PostgreSQL data provider for .NET and is used to connect to PostgreSQL databases and execute PostgreSQL SQL statements.

Microsoft.Data.SqlClient is designed for SQL Server/Azure SQL rather than PostgreSQL.

Question 5

An application performs five related database operations. If the third operation fails, none of the previous operations should remain committed. Which database capability should the developer use?

A. A transaction

B. A connection string

C. A firewall rule

D. A connection pool

Answer: A

Explanation

A transaction allows multiple operations to be treated as a single logical unit.

For example:

BEGIN
  Operation 1
  Operation 2
  Operation 3 ← failure
ROLLBACK

The rollback prevents earlier operations in the transaction from remaining committed.

A connection pool manages reusable connections; it does not provide transaction semantics.

Question 6

A high-traffic web API opens a new PostgreSQL connection for every HTTP request and closes it immediately after the query. The application experiences unnecessary connection overhead. What should the developer consider?

A. Disable TLS

B. Use connection pooling

C. Replace PostgreSQL with Blob Storage

D. Increase the database query timeout

Answer: B

Explanation

Connection pooling allows the application to reuse established database connections instead of repeatedly creating and destroying them.

This can reduce connection-establishment overhead and improve performance for applications handling many requests.

Question 7

An Azure-hosted application needs to access Azure Database for PostgreSQL without storing a database password in application source code. Which authentication approach is most appropriate when supported by the application’s hosting environment and database configuration?

A. Hard-code the administrator password in the application

B. Store the password in a source-code configuration file

C. Use Microsoft Entra authentication with a managed identity

D. Disable authentication on the PostgreSQL server

Answer: C

Explanation

Managed identities allow Azure resources to authenticate to supported services without developers embedding credentials in application code.

Azure Database for PostgreSQL supports Microsoft Entra authentication and managed identities. (Microsoft Learn)

Hard-coding credentials is insecure, and disabling authentication is not an appropriate solution.

Question 8

A Java application needs to execute the following query using a user-provided value:

SELECT *
FROM documents
WHERE category = ?

Which Java API should the developer use to safely bind the value?

A. PreparedStatement

B. StringBuilder

C. System.out

D. FileOutputStream

Answer: A

Explanation

PreparedStatement is designed for parameterized SQL.

The application can bind the parameter rather than concatenate user input into the SQL string.

For example:

PreparedStatement statement =
    connection.prepareStatement(
        "SELECT * FROM documents WHERE category = ?");
statement.setString(1, category);

This is safer than dynamically constructing SQL with user input.

Question 9

An application successfully establishes a connection to Azure Database for PostgreSQL. However, when it attempts to query a table, PostgreSQL returns a permission-denied error. Which area should the developer investigate?

A. DNS resolution

B. Azure Storage firewall rules

C. Database authorization and user permissions

D. PostgreSQL server hostname

Answer: C

Explanation

The application has already successfully connected, so basic network connectivity and server resolution are working.

A permission-denied error after connection generally indicates an authorization problem.

The developer should investigate:

• Database user
• Role membership
• Table permissions
• Schema permissions
• Required privileges

This is different from authentication, which establishes who the user is.

Question 10

An application retrieves only the name and category of a product. Which query is generally preferable when those are the only required values?

A.

SELECT *
FROM products;

B.

SELECT *
FROM products
WHERE id = $1;

C.

SELECT name, category
FROM products
WHERE id = $1;

D.

SELECT *
FROM products
ORDER BY name;

Answer: C

Explanation

The application only needs name and category, so the query should retrieve only those columns and filter to the required row.

SELECT name, category
FROM products
WHERE id = $1;

This minimizes unnecessary data retrieval and uses a parameterized value.

The other queries retrieve unnecessary columns or, in some cases, unnecessary rows.

Final AI-200 Study Summary

For this topic, the most important thing to remember is that Azure Database for PostgreSQL is PostgreSQL, so applications generally communicate with it through standard PostgreSQL client libraries.

The core exam concepts can be condensed to:

The exam is especially likely to test whether you can distinguish the database client library, authentication, networking, authorization, query security, and connection management. Those concepts are easy to mix together, so keeping those boundaries clear is valuable.

Go to the AI-200 Exam Prep Hub main page