Skip to content

login-instructions.md

Latest commit

 

History

History
181 lines (131 loc) · 6.92 KB

login-instructions.md

File metadata and controls

181 lines (131 loc) · 6.92 KB
Raw

CockroachDB Passwordless Login Instructions

Current Status

  • ✅ User dalekurtm configured with PASSWORD NULL (certificate-only authentication)
  • ✅ 3 trusted CA certificates uploaded to CockroachDB Cloud:
    1. CN=amazingduck-dev-use1-self-signed-ca
    2. CN=amazingbadger-shared-use1-self-signed-ca
    3. O=teleport.sharedservices.frame.io, CN=teleport.sharedservices.frame.io

Quick Start: Automated Setup (Recommended)

For the easiest setup experience, use the complete automation script:

./scripts/cockroachdb/auth/setup-passwordless-login.sh --username dalekurtm

This script will:

  1. Configure user with PASSWORD NULL
  2. Generate client certificate (if needed)
  3. Test connection
  4. Provide connection instructions

Option 1: Generate Client Certificate Using Kubernetes CA (Recommended)

Using amazingbadger CA (Current Context)

# 1. Ensure you're on the correct cluster
kubectl config use-context teleport.sharedservices.frame.io-amazingbadger-shared-use1

# 2. Extract CA certificate and key
mkdir -p certificates/clients
kubectl get secret amazingbadger-shared-use1-ca-secret -n cert-manager -o jsonpath='{.data.ca\.crt}' | base64 -d > certificates/clients/ca.crt
kubectl get secret amazingbadger-shared-use1-ca-secret -n cert-manager -o jsonpath='{.data.tls\.key}' | base64 -d > certificates/clients/ca.key

# 3. Generate client private key
openssl genrsa -out certificates/clients/dalekurtm.key 2048
chmod 600 certificates/clients/dalekurtm.key

# 4. Create certificate signing request
openssl req -new -key certificates/clients/dalekurtm.key -out certificates/clients/dalekurtm.csr -subj "/CN=dalekurtm"

# 5. Sign certificate with CA
openssl x509 -req -in certificates/clients/dalekurtm.csr \
  -CA certificates/clients/ca.crt \
  -CAkey certificates/clients/ca.key \
  -CAcreateserial \
  -out certificates/clients/dalekurtm.crt \
  -days 365

# 6. Clean up sensitive files
rm -f certificates/clients/ca.key certificates/clients/dalekurtm.csr certificates/clients/ca.srl

Using amazingduck CA

# Switch to amazingduck cluster
kubectl config use-context teleport.sharedservices.frame.io-amazingduck-dev-use1

# Extract CA (same steps as above, but use amazingduck secret names)
kubectl get secret amazingduck-dev-use1-ca-secret -n cert-manager -o jsonpath='{.data.ca\.crt}' | base64 -d > certificates/clients/ca.crt
kubectl get secret amazingduck-dev-use1-ca-secret -n cert-manager -o jsonpath='{.data.tls\.key}' | base64 -d > certificates/clients/ca.key

# Then follow steps 3-6 above

Option 2: Use Automated Scripts

Complete Setup (Recommended)

./scripts/cockroachdb/auth/setup-passwordless-login.sh --username dalekurtm

Step-by-Step Setup

Step 1: Configure User

./scripts/cockroachdb/auth/configure-user-passwordless.sh --username dalekurtm

Step 2: Generate Certificate

./scripts/cockroachdb/certificates/generate-client-cert.sh --username dalekurtm --ca amazingbadger --output-dir certificates/clients

Step 3: Connect

./scripts/cockroachdb/auth/connect-cockroachdb.sh --username dalekurtm

The scripts will guide you through the process interactively.

Option 3: Use Teleport Database Connection (If Available)

If Teleport has a database resource configured for CockroachDB:

# List available databases
tsh db ls

# Connect (Teleport handles certificates automatically)
tsh db connect massdriver-development-crdb

Note: This requires the Teleport database resource to be configured and accessible.

Connecting to CockroachDB

Once you have the certificate files (dalekurtm.crt and dalekurtm.key):

Using Connection Helper (Recommended)

./scripts/cockroachdb/auth/connect-cockroachdb.sh --username dalekurtm

This automatically includes all required certificate parameters.

Using cockroach CLI (Manual)

cockroach sql --url "postgresql://dalekurtm@massdriver-development-s4k.aws-us-east-1.cockroachlabs.cloud:26257/massdriver_development?sslmode=verify-full&sslrootcert=$HOME/Library/CockroachCloud/certs/1ed91ecb-08a2-4886-b724-ddcd7bda41c3/massdriver-development-ca.crt&sslcert=certificates/clients/dalekurtm.crt&sslkey=certificates/clients/dalekurtm.key"

Using psql

export PGSSLCERT=certificates/clients/dalekurtm.crt
export PGSSLKEY=certificates/clients/dalekurtm.key
export PGSSLROOTCERT=$HOME/Library/CockroachCloud/certs/1ed91ecb-08a2-4886-b724-ddcd7bda41c3/massdriver-development-ca.crt

psql "postgresql://dalekurtm@massdriver-development-s4k.aws-us-east-1.cockroachlabs.cloud:26257/massdriver_development?sslmode=verify-full"

Using Environment Variables

export COCKROACH_SSL_CERT=certificates/clients/dalekurtm.crt
export COCKROACH_SSL_KEY=certificates/clients/dalekurtm.key
export COCKROACH_SSL_ROOT_CERT=$HOME/Library/CockroachCloud/certs/1ed91ecb-08a2-4886-b724-ddcd7bda41c3/massdriver-development-ca.crt

cockroach sql --url "postgresql://dalekurtm@massdriver-development-s4k.aws-us-east-1.cockroachlabs.cloud:26257/massdriver_development?sslmode=verify-full"

Troubleshooting

"password authentication failed"

  • ✅ Ensure user has PASSWORD NULL: 
    ./configure-user-passwordless.sh --username dalekurtm
    Or manually: SELECT usename, passwd IS NULL FROM pg_shadow WHERE usename = 'dalekurtm';
  • ✅ Verify certificate CN matches username exactly: openssl x509 -in certificates/clients/dalekurtm.crt -noout -subject
  • ✅ Ensure certificate is signed by one of the 3 trusted CAs
  • ✅ Important: Connection string must include sslcert and sslkey parameters. Use ./connect-cockroachdb.sh to ensure correct connection string.

"certificate signed by unknown authority"

  • ✅ Verify the CA certificate is uploaded to CockroachDB Cloud: ./scripts/cockroachdb/certificates/check-cockroachdb-ca-cert.sh --cluster massdriver-development --api-key <API_KEY>
  • ✅ Wait a few minutes after uploading CA certificate for changes to propagate

"connection refused" or network errors

  • ✅ Ensure you're connected to VPN or have network access to the PrivateLink endpoint
  • ✅ Verify DNS resolution: dig massdriver-development-s4k.aws-us-east-1.cockroachlabs.cloud

Security Notes

  • ⚠️ Never commit private keys (*.key files) to git
  • ⚠️ Delete CA private keys after generating client certificates
  • ⚠️ Set proper permissions: chmod 600 certificates/clients/*.key
  • ⚠️ Rotate certificates periodically (default validity: 365 days)

Additional Resources

  • Complete Setup Guide: See passwordless-login-setup.md for comprehensive documentation
  • Scripts:
    • scripts/cockroachdb/auth/setup-passwordless-login.sh - Complete automated setup
    • scripts/cockroachdb/auth/configure-user-passwordless.sh - Configure users
    • scripts/cockroachdb/certificates/generate-client-cert.sh - Generate certificates
    • scripts/cockroachdb/auth/connect-cockroachdb.sh - Connection helper