Question

Question

Prisma consistently fails to establish SSL connections to PostgreSQL with P1010 "User was denied access on the database" error, while other PostgreSQL clients (psql, Node.js pg library) connect successfully using the same SSL certificates and connection parameters.

This prevents using SSL encryption for database connections in production environments that require client certificate authentication, creating a security gap where other PostgreSQL clients can connect securely but Prisma cannot.

Could someone please investigate why Prisma's SSL implementation fails with client certificate authentication when other PostgreSQL clients succeed with identical certificates and configuration? This appears to be a Prisma-specific SSL handling issue rather than a certificate or server configuration problem.

How to reproduce (optional)

How to Reproduce

Environment Setup

• PostgreSQL server with SSL enabled and client certificate authentication required
• Client certificates issued by intermediate CA (self-signed)
• Certificate chain: Client Cert → Intermediate CA (self-signed) → Root CA (self-signed)

Steps to Reproduce

1. Setup SSL certificates:

   # Client certificate and key
   /etc/postgres/tls.crt    # Client certificate (CN=client-app)
   /etc/postgres/tls.key    # Client private key
   /etc/postgres/ca/ca.crt  # Root CA certificate

2. Test with PostgreSQL client (works):

   PGPASSWORD=password psql 'postgresql://user:password@host:5432/database?sslmode=require&sslcert=/etc/postgres/tls.crt&sslkey=/etc/postgres/tls.key&sslrootcert=/etc/postgres/ca/ca.crt'
   # Result: SUCCESS - connects and executes queries

3. Test with Prisma (fails):

   export DATABASE_URL='postgresql://user:password@host:5432/database?sslmode=require&sslcert=/etc/postgres/tls.crt&sslkey=/etc/postgres/tls.key&sslrootcert=/etc/postgres/ca/ca.crt'
   npx prisma db execute --url="$DATABASE_URL" --file=test.sql
   # Result: Error: P1010 - User was denied access on the database `(not available)`

4. Test with Node.js pg library (works):

   const { Client } = require('pg');
   const fs = require('fs');
   
   const client = new Client({
     host: 'host',
     port: 5432,
     database: 'database',
     user: 'user',
     password: 'password',
     ssl: {
       cert: fs.readFileSync('/etc/postgres/tls.crt'),
       key: fs.readFileSync('/etc/postgres/tls.key'),
       ca: fs.readFileSync('/etc/postgres/ca/ca.crt'),
       rejectUnauthorized: true
     }
   });
   // Result: SUCCESS - connects and executes queries

Certificate Chain Analysis

# Client certificate validation against intermediate CA
openssl verify -CAfile /tmp/intermediate_ca.crt /etc/postgres/tls.crt
# Result: /etc/postgres/tls.crt: OK

# Certificate details
Client Certificate:
  Subject: CN=client-app
  Issuer: CN=intermediate-ca

Intermediate CA:
  Subject: CN=intermediate-ca  
  Issuer: CN=intermediate-ca  # Self-signed

Root CA:
  Subject: CN=internal-root-ca
  Issuer: CN=internal-root-ca      # Self-signed

Expected behavior (optional)

Expected Behavior

Prisma should successfully establish SSL connections to PostgreSQL when:

• Valid client certificates are provided
• PostgreSQL server accepts the certificates
• Other PostgreSQL clients work with the same configuration

Actual Behavior

• Prisma: P1010 error - "User was denied access on the database (not available)"
• PostgreSQL client: ✅ Connects successfully
• Node.js pg library: ✅ Connects successfully
• Non-SSL connections: ✅ Work with Prisma

Information about Prisma Schema, Client Queries and Environment (optional)

Information about Prisma Schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
}

Client Queries

# Simple query that fails with SSL
echo 'SELECT current_user, current_database();' > test.sql
npx prisma db execute --url="$DATABASE_URL" --file=test.sql

Environment & Setup

• Container: Alpine Linux with Node.js 18.20.8
• PostgreSQL Server: Kubernetes-hosted with SSL client certificate authentication
• SSL Configuration: Client certificates required, intermediate CA chain
• Network: Internal Kubernetes cluster communication

Prisma Version

prisma                  : 6.15.0
@prisma/client          : Not found
Computed binaryTarget   : linux-musl-openssl-3.0.x
Operating System        : linux
Architecture            : x64
Node.js                 : v18.20.8
Query Engine (Node-API) : libquery-engine 85179d7826409ee107a6ba334b5e305ae3fba9fb
Schema Engine           : schema-engine-cli 85179d7826409ee107a6ba334b5e305ae3fba9fb

Additional Testing

Tested Prisma Versions

• ❌ Prisma 6.15.0: P1010 error
• ❌ Prisma 5.19.1: P1010 error
• ❌ Prisma 4.16.2: P1010 error
• ❌ Prisma 3.15.2: P1010 error

Tested SSL Parameters

All combinations failed with P1010 error:

• sslmode=require|prefer|allow
• sslaccept=accept_invalid_certs
• rejectUnauthorized=false
• checkServerIdentity=false
• Combined CA certificate bundles
• Different certificate formats (PEM)

File Access Verification

• ✅ Prisma process can read all certificate files
• ✅ Correct file permissions (644 for certs, 600 for keys)
• ✅ Valid PEM format certificates
• ✅ Process runs with sufficient privileges (root)

Workaround

Currently using non-SSL connections as the only viable solution:

DATABASE_URL='postgresql://user:password@host:5432/database?sslmode=disable'