Setting environment variables for postgres running in docker

When running PostgreSQL in Docker, setting environment variables correctly is crucial, especially for sensitive data like API keys. After extensive testing, here’s the solution that worked for us:

Docker Compose Configuration

In your docker-compose.dev.local.yml:

services:
  db:
    environment:
      - STRIPE_SECRET_KEY=${STRIPE_SECRET_KEY}
      - PGOPTIONS="-c custom.stripe_key=${STRIPE_SECRET_KEY}"

Database Initialization

In your backend/db/init.sql:

DO $$ 
DECLARE
    stripe_key text;
BEGIN 
    -- Try to get Stripe key from custom parameter
    BEGIN
        stripe_key := current_setting('custom.stripe_key');
        
        IF stripe_key IS NOT NULL AND stripe_key != '' THEN
            -- Set for database level
            EXECUTE format('ALTER DATABASE %I SET custom.stripe_key = %L', 
                          current_database(),
                          stripe_key);
            -- Set for current session
            EXECUTE format('SET custom.stripe_key = %L', stripe_key);
            
            RAISE NOTICE 'Successfully found custom.stripe_key: %', left(stripe_key, 4);
        ELSE
            RAISE WARNING 'custom.stripe_key is empty or undefined';
        END IF;
    EXCEPTION 
        WHEN undefined_object THEN
            RAISE WARNING 'custom.stripe_key not found';
    END;
END $$;

-- Set proper permissions
REVOKE ALL ON PARAMETER custom.stripe_key FROM PUBLIC;
GRANT SET ON PARAMETER custom.stripe_key TO webuser;

Verification

To verify the setup is working, use:

curl http://localhost:5020/postgrest/rpc/debug_env

You should see output like:

{
    "stripe_env": "missing",
    "stripe_custom": "present (first 4 chars: sk_t)",
    "current_database": "commtact",
    "current_user": "webuser",
    "session_user": "webuser"
}

How It Works

1. The Docker Compose file sets the Stripe key as both an environment variable and a PostgreSQL custom parameter via PGOPTIONS
2. The initialization SQL script:
   • Retrieves the key from the custom parameter
   • Sets it at both database and session level
   • Configures proper permissions for the webuser role
3. The debug endpoint confirms the key is accessible within PostgreSQL

After making any changes, remember to restart your containers:

docker compose -f docker-compose.dev.local.yml down -v && docker compose -f docker-compose.dev.local.yml up -d --build

This solution ensures your environment variables are properly set and accessible within PostgreSQL functions while maintaining security best practices.