Short answer (assuming Prefect 3.x): the Prefect DB user does not need superuser. It needs enough privileges to run migrations, create required extensions, and read/write Prefect’s tables. Minimum privileges for the prefect user on Azure Database for PostgreSQL: - CONNECT on the Prefect database - CREATE on the database (so migrations can CREATE EXTENSION pgcrypto and pg_trgm) - USAGE and CREATE on the target schema (Prefect uses the public schema by default) - ALL privileges on all tables and sequences in that schema - Default privileges so future tables/sequences created by migrations are accessible You can apply this with an admin user:

-- create database and user (example)
CREATE DATABASE prefect;
CREATE USER prefect WITH PASSWORD 'your_secure_password';

-- grant database-level access
GRANT CONNECT ON DATABASE prefect TO prefect;
GRANT CREATE ON DATABASE prefect TO prefect;  -- needed for CREATE EXTENSION

-- switch into the prefect database
\c prefect

-- schema privileges (Prefect uses public)
GRANT USAGE ON SCHEMA public TO prefect;
GRANT CREATE ON SCHEMA public TO prefect;

-- current objects
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO prefect;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO prefect;

-- future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO prefect;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO prefect;

Notes: - Required extensions: pgcrypto (for gen_random_uuid) and pg_trgm (for trigram indexes). With CREATE on the database, Prefect’s migrations will install them automatically: - CREATE EXTENSION IF NOT EXISTS pgcrypto; - CREATE EXTENSION IF NOT EXISTS pg_trgm; - If your org does not allow granting CREATE on the database to the application user, have an admin create those two extensions once, then you can omit the database-level CREATE for the prefect user. - Azure managed Postgres supports both pgcrypto and pg_trgm. Use SSL in your connection string (e.g.,

?sslmode=require

). Refs: - Prefect Server concepts - Prefect database maintenance If you want, share how you’re provisioning the database (psql, Azure Portal, Terraform, etc.) and I can tailor the exact commands.

Here are a few ready-to-use examples for PREFECT_API_DATABASE_CONNECTION_URL. PostgreSQL (asyncpg) – local dev:

<postgresql+asyncpg://prefect:prefect@localhost:5432/prefect>

PostgreSQL on Azure Flexible Server:

<postgresql+asyncpg://prefect:YourStrongP@ss!@your-server-name.postgres.database.azure.com:5432/prefectdb?sslmode=require>

PostgreSQL on Azure Single Server (username includes @server):

<postgresql+asyncpg://prefect%40your-server-name:YourStrongP%40ss!@your-server-name.postgres.database.azure.com:5432/prefectdb?sslmode=require>

SQLite (file-based):

sqlite+aiosqlite:///~/.prefect/prefect.db

Notes: - Prefect 3.x requires async drivers: use

postgresql+asyncpg

for Postgres and

sqlite+aiosqlite

for SQLite. - If your password or username contains special characters (like @, :, !), URL-encode them in the URI (as shown in the Single Server example). -

?sslmode=require

is recommended for managed Postgres (Azure, RDS, Cloud SQL). - Prefect also accepts

PREFECT_SERVER_DATABASE_CONNECTION_URL

as an alias, but

PREFECT_API_DATABASE_CONNECTION_URL

is preferred.

You're hitting this because

asyncpg.connect()

does not accept an

sslmode

keyword argument. When you use SQLAlchemy with the

postgresql+asyncpg

dialect, any URL query parameters are passed as keyword args to

asyncpg.connect()

. As a result,

sslmode=require

becomes

sslmode=<...>

in kwargs and triggers: TypeError: connect() got an unexpected keyword argument 'sslmode' What to do instead: - For asyncpg, enable TLS with the

ssl

parameter (boolean or an SSLContext), not

sslmode

. Quick fixes: - Easiest (TLS on, no extra verification controls):

postgresql+asyncpg://<user>:<pass>@<server>.<http://postgres.database.azure.com:5432/<db>?ssl=true|postgres.database.azure.com:5432/<db>?ssl=true>

- Programmatic via SQLAlchemy:

from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    "postgresql+asyncpg://<user>:<pass>@<server>.<http://postgres.database.azure.com:5432/<db|postgres.database.azure.com:5432/<db>>",
    connect_args={"ssl": True},
)

- If you need certificate verification, pass an SSLContext:

import ssl
from sqlalchemy.ext.asyncio import create_async_engine

sslctx = ssl.create_default_context()  # configure as needed (e.g., load CA)
engine = create_async_engine(
    "postgresql+asyncpg://<user>:<pass>@<server>.<http://postgres.database.azure.com:5432/<db|postgres.database.azure.com:5432/<db>>",
    connect_args={"ssl": sslctx},
)

Alternative: - If you want to keep

sslmode=require

semantics, switch to a libpq-based driver like psycopg:

postgresql+psycopg://<user>:<pass>@<server>.<http://postgres.database.azure.com:5432/<db>?sslmode=require|postgres.database.azure.com:5432/<db>?sslmode=require>

References: - SQLAlchemy issue showing this exact error and cause: sslmode query param does not work for asyncpg DBAPI - asyncpg issue discussing the same: asyncpg does not work with "sslmode" query param when called from SQLAlchemy - SQLAlchemy asyncpg dialect docs: SQLAlchemy asyncpg dialect If you share which driver and SQLAlchemy version you’re on, I can tailor the exact connection string or code sample.

Short answer: Yes. Prefect Server (Prefect 3.x) can connect to Azure Database for PostgreSQL Flexible Server over SSL, but you should not rely on putting sslmode/SSL options in the connection string. Prefect builds its own SSL context from dedicated environment variables. If you only tweaked the URL, that explains why “none of them work.” What to set - Connection URL (must use asyncpg dialect):

PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://USERNAME%40SERVERNAME:PASSWORD@SERVERNAME.postgres.database.azure.com:5432/prefect>

- Enable TLS and point to your CA cert (mount the CA file into the container):

PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_TLS_ENABLED=true
PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_TLS_CA_FILE=/etc/ssl/certs/azure-pg-ca.crt
# Optional (mTLS if required):
# PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_TLS_CERT_FILE=/etc/ssl/certs/client.crt
# PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_TLS_KEY_FILE=/etc/ssl/private/client.key
# Optional tuning:
# PREFECT_API_DATABASE_CONNECTION_TIMEOUT=10.0
# PREFECT_API_DATABASE_TIMEOUT=30.0
# PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_PREPARED_STATEMENT_CACHE_SIZE=0

Notes for Azure Flexible Server - Azure usernames are of the form

user@servername

. In URLs, the

@

must be percent-encoded as

%40

(see example above). - Use the Azure-provided root CA certificate for your server and mount it inside the container; set its path in

...TLS_CA_FILE

. - Do not append

?sslmode=require

or similar to the URL; Prefect does not parse those and instead uses the SSL context built from the TLS settings above. - Use the server’s FQDN (e.g.,

<http://myserver.postgres.database.azure.com|myserver.postgres.database.azure.com>

) so hostname verification succeeds. Known caveat (heads-up) - Prefect’s SQLAlchemy/asyncpg engine honors the TLS settings above (used for all normal DB access). - However, the separate LISTEN/NOTIFY “postgres listener” connection currently does not pass an SSL context to asyncpg. On a TLS-required server, you may see warnings about the listener failing to connect. Core API and database operations still work; some real-time event features may be degraded. - Code references: - TLS settings/engine SSL: https://github.com/PrefectHQ/prefect/blob/main/src/prefect/settings/models/server/database.py#L18-L96 - Engine applies SSL context: https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/database/configurations.py#L266-L287 - Postgres listener (no SSL passed): https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/postgres_listener.py Minimal docker-compose-style example

services:
  prefect-server:
    image: prefecthq/prefect:3-latest
    environment:
      PREFECT_API_DATABASE_CONNECTION_URL: "<postgresql+asyncpg://USERNAME%40SERVERNAME:PASSWORD@SERVERNAME.postgres.database.azure.com:5432/prefect>"
      PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_TLS_ENABLED: "true"
      PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_TLS_CA_FILE: "/etc/ssl/certs/azure-pg-ca.crt"
    volumes:
      - ./azure-pg-ca.crt:/etc/ssl/certs/azure-pg-ca.crt:ro
    command: ["prefect", "server", "start"]

References - Settings and TLS options: https://docs.prefect.io/v3/api-ref/settings-ref - TLS settings class: https://github.com/PrefectHQ/prefect/blob/main/src/prefect/settings/models/server/database.py#L18-L96 - Engine SSL handling: https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/database/configurations.py#L266-L287 - Postgres listener implementation: https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/postgres_listener.py If it’s still failing, can you share: - Your Prefect version (e.g., 3.x.y) - The exact error from the server container logs - The URL you’re using (redact password) and the TLS env vars you set I’ll help pinpoint whether it’s URL formatting, CA path/hostname verification, or the listener connection.