Database Credentials from AWS Secrets Manager

You're running flAPI in production. Two policies make life harder:

1. No plaintext credentials in Git. The compliance team will not approve a password: field in any committed YAML, even with environment-variable interpolation.
2. 30-day rotation. Every API user's password is rotated on a fixed cadence. Restarting flAPI on each rotation is acceptable; rebuilding the deployment is not.

flAPI's auth.from-aws-secretmanager block solves both. The user table lives in AWS Secrets Manager, flAPI loads it into an in-memory DuckDB table at startup, and Basic Auth lookups hit that table on every request. The wire format on AWS is JSON, so rotation is a single aws secretsmanager update-secret call.

How It Works

┌─────────────────────────────────────────────────────────┐
│                 AWS Secrets Manager                     │
│  flapi_endpoint_users                                   │
│    { "auth": [                                          │
│        { "username": "alice", "password": "...",        │
│          "roles": ["read"] },                           │
│        { "username": "bob",   "password": "...",        │
│          "roles": ["read","write"] }                    │
│      ] }                                                │
└────────────────────────────┬────────────────────────────┘
                             │
                             │ 1. On flAPI startup, GetSecretValue
                             │    using the DuckDB SECRET named
                             │    "flapi_endpoint_users" (TYPE S3).
                             ▼
┌─────────────────────────────────────────────────────────┐
│                    flAPI process                        │
│  DuckDB table  api_users(j JSON)                        │
│  populated by AwsHelper::refreshSecretJson()            │
└────────────────────────────┬────────────────────────────┘
                             │
                             │ 2. On each request, AuthMiddleware
                             │    runs findUserInSecretsTable():
                             │      SELECT password, roles
                             │      FROM api_users
                             │      WHERE username = '<header>'
                             ▼
┌─────────────────────────────────────────────────────────┐
│           HTTP 200  (valid user/pass)                   │
│           HTTP 401  (otherwise)                         │
└─────────────────────────────────────────────────────────┘

Two things worth memorizing:

• The DuckDB table (secret-table) is the runtime lookup target. It's an in-memory CREATE OR REPLACE TABLE <name>(j JSON) populated from the secret string.
• The DuckDB SECRET (created by init: SQL) is not the user list — it's how flAPI authenticates to AWS to read the user list. By convention its name matches secret-name (with non-alphanumeric characters replaced by _), which is what tryGetS3AuthParams looks up.

The Configuration

This is the pattern from examples/sqls/taxi/taxi.yaml.inactive, fleshed out for production:

# sqls/customers/customers.yaml
url-path: /customers/
method: GET
connection:
  - warehouse
template-source: customers.sql

auth:
  enabled: true
  type: basic
  from-aws-secretmanager:
    secret-name: flapi_endpoint_users
    secret-table: api_users
    region: us-east-1
    init: |
      CREATE OR REPLACE SECRET flapi_endpoint_users (
        TYPE S3,
        PROVIDER CREDENTIAL_CHAIN
      );

Every key here is parsed in config_manager.cpp lines 554-582 (parseEndpointAuth):

Key	What it is
secret-name	The AWS Secrets Manager secret identifier (name or ARN). Passed to GetSecretValueRequest::SetSecretId.
secret-table	The DuckDB table name flAPI materializes the secret into. Default is auth_<sanitized secret-name>; setting it explicitly is clearer.
region	AWS region for the Secrets Manager API call.
secret-id	(Optional) AWS access key ID. Omit to let PROVIDER CREDENTIAL_CHAIN use the standard AWS credential chain (IMDS, env vars, ~/.aws/credentials, IRSA).
secret-key	(Optional) AWS secret access key. Same caveat.
init	SQL that runs once on flAPI startup. Use it to define the DuckDB SECRET the auth layer uses to talk to AWS.

If you omit init: entirely, flAPI generates a default DuckDB SECRET for you using secret-id / secret-key (or PROVIDER CREDENTIAL_CHAIN if neither is set). The default is built by ConfigManager::createDefaultAuthInit. Explicit init: blocks override that.

The Secret Payload

The JSON stored in AWS Secrets Manager must contain an auth array of user objects:

{
  "auth": [
    {
      "username": "alice",
      "password": "5f4dcc3b5aa765d61d8327deb882cf99",
      "roles": ["read"]
    },
    {
      "username": "bob",
      "password": "9d4e1e23bd5b727046a9e3b4b7db57bd",
      "roles": ["read", "write"]
    }
  ]
}

Notes from auth_middleware.cpp::verifyPassword:

• A password is treated as an MD5 hash when it's exactly 32 hex characters. Anything else is compared as plaintext.
• Generate MD5 hashes with echo -n 'my-password' | md5sum. MD5 is supported for compatibility but is not strong by modern standards — prefer rotating frequently.
• The roles array becomes auth.roles (comma-joined) inside SQL templates.

IAM Permissions

The IAM principal that flAPI runs under (EC2 role, EKS service account via IRSA, or local profile) needs one permission:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ReadFlapiAuthSecret",
      "Effect": "Allow",
      "Action": "secretsmanager:GetSecretValue",
      "Resource": "arn:aws:secretsmanager:us-east-1:123456789012:secret:flapi_endpoint_users-*"
    }
  ]
}

Two things to know:

• AWS appends a 6-character random suffix to the secret's ARN (-aBcDeF). Use the -* wildcard in the resource ARN.
• If the secret is KMS-encrypted with a customer-managed key, also grant kms:Decrypt on that key.

Loading Users Into AWS

Initial load:

aws secretsmanager create-secret \
  --name flapi_endpoint_users \
  --region us-east-1 \
  --secret-string file://users.json

Rotation (just push a new payload):

aws secretsmanager update-secret \
  --secret-id flapi_endpoint_users \
  --region us-east-1 \
  --secret-string file://users-2026-05-15.json

# Pick up the new payload (the secret is read at startup):
systemctl restart flapi    # or: kubectl rollout restart deploy/flapi

flAPI reads secret-name exactly once during AuthMiddleware::initialize — there is no hot reload. A restart (or pod rollout) is what activates the new user list. If you need zero-downtime rotation, run flAPI behind a load balancer and do a rolling restart.

Test It

Start flAPI and watch for the secret being loaded:

flapi --log-level debug --config flapi.yaml
# ... Initializing AWS Secrets Manager for endpoint: /customers/
# ... Retrieving secret 'flapi_endpoint_users' -> 'flapi_endpoint_users' from AWS Secrets Manager
# ... Successfully retrieved secret 'flapi_endpoint_users': *****[247]

Unauthenticated request — 401

curl -i http://localhost:8080/customers/
# HTTP/1.1 401 Unauthorized
# WWW-Authenticate: Basic realm="flAPI"

Wrong password — 401

curl -i -u alice:nope http://localhost:8080/customers/
# HTTP/1.1 401 Unauthorized

Valid credentials — 200

curl -s -u alice:password http://localhost:8080/customers/ | jq .
# {
#   "data": [ ... ],
#   "next": null,
#   "total_count": 12
# }

You can confirm role-based filtering in your SQL template by checking auth.roles:

-- sqls/customers/customers.sql
SELECT customer_id, company_name, country
FROM warehouse.public.customers
WHERE 1=1
  -- Only users with the 'write' role see internal-only rows.
  AND (
       '{{auth.roles}}' LIKE '%write%'
    OR is_internal = false
  )

Troubleshooting

Run flAPI with --log-level debug for diagnostic output. Common failures:

• No AWS auth params found for secret '<name>' — the DuckDB SECRET created by init: doesn't match the secret name. flAPI sanitizes secret-name (non-alphanumeric → _) and looks up a DuckDB secret of that name. Make sure your CREATE SECRET <name> matches.
• Error retrieving secret '<name>': User: arn:aws:... is not authorized — missing IAM permission, or the secret ARN in the policy doesn't include the -* suffix.
• Failed to refresh JSON table '<table>' — the secret payload isn't valid JSON, or doesn't contain an auth array of objects with username / password / roles.
• All requests 401, even with correct credentials — the secret payload is malformed (no auth array key) or the password's MD5 hash doesn't match what you generated. Run echo -n 'my-password' | md5sum and compare.

Operational Notes

• Rotation cadence: monthly is the typical target. The AWSCURRENT / AWSPREVIOUS staging labels on Secrets Manager give you a one-version safety net.
• Multiple endpoints, one secret: flAPI iterates every endpoint with from-aws-secretmanager at startup. Sharing one secret across endpoints is fine; flAPI will call refreshSecretJson once per endpoint config.
• Local development: use inline users: instead of AWS to avoid the IAM dance. The endpoint's auth: block selects the lookup path: inline users: is tried first, AWS only if no inline match exists (see AuthMiddleware::authenticateBasic).
• HTTPS: Basic Auth sends the credential pair base64-encoded — never plaintext-equivalent. Set enforce-https.enabled: true and put flAPI behind a TLS terminator.
• Connection-level credentials: this recipe covers endpoint user rotation. If you also need to rotate the database password your connection uses, manage that DuckDB secret directly in the connection's init: block — out of scope here, since flAPI doesn't have a built-in helper for it.

Why a DuckDB SECRET, Not Just IAM?

The init: SQL block is the part of the configuration most people stumble on. It creates a DuckDB SECRET, even when the goal is to call AWS Secrets Manager (not S3). Why?

The AWS extension that flAPI's AwsHelper uses authenticates via DuckDB's unified secret store. By creating a SECRET named after secret-name (sanitized), you give AwsHelper::tryGetS3AuthParams a lookup key — that's where it pulls the key_id, secret, session_token, and region from. The TYPE S3 is a bit of a misnomer: it just means "AWS credentials," not "S3 access."

The simplest form, suitable for any environment with an IAM role attached (EC2, ECS, EKS with IRSA, Lambda, or a developer machine with AWS_PROFILE set):

init: |
  CREATE OR REPLACE SECRET flapi_endpoint_users (
    TYPE S3,
    PROVIDER CREDENTIAL_CHAIN
  );

PROVIDER CREDENTIAL_CHAIN tells DuckDB to walk the standard AWS chain: environment variables, EC2/ECS metadata, IRSA, then ~/.aws/credentials. This is the production-recommended path — no static keys in config.

For local development without an AWS profile, you can pin the keys explicitly:

init: |
  CREATE OR REPLACE SECRET flapi_endpoint_users (
    TYPE S3,
    KEY_ID '{{env.AWS_ACCESS_KEY_ID}}',
    SECRET '{{env.AWS_SECRET_ACCESS_KEY}}',
    REGION 'us-east-1'
  );

Or skip the init: entirely and let flAPI's createDefaultAuthInit generate it from secret-id / secret-key. The explicit form is preferred — it's easier to grep for and reason about.

See Also

• Authentication reference — the full key list for every auth scheme, including the inline-users: shorthand for non-AWS environments.
• Cloud storage and DuckDB secrets — same CREATE OR REPLACE SECRET ... TYPE S3 pattern, applied to data access instead of auth.
• Multi-Tenant SaaS API with OIDC — sibling recipe for delegating authentication entirely to an external IdP.