PostgreSQL connection via environment variables for local and production environments.

Published 1 week ago by ElijahPaul

How would i configure the config/database.php file to account for an unsecured local PostgreSQL connection and a secure production one?

My current config:

'pgsql' => [
            'driver' => 'pgsql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '5432'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'schema' => 'public',
            'sslmode' => 'prefer',
        ],

If I add the following to the database.php config file...

.....
'sslcert' => env('DB_SSLCERT', ''),
'sslkey' => env('DB_SSLKEY', ''),
'sslrootcert' => env('DB_SSLROOTCERT', ''),
.....

And set the environment variables (only in production)...

DB_SSLMODE=verify-full
DB_SSLCERT=/path/to/mycert.pem
DB_SSLKEY=/path/to/mykey.pem
DB_SSLROOTCERT=/path/to/myrootcert.pem

The local connection (without adding the corresponding environment variables) fails, but the production environment (with the variables set) connects successfully.

So how should i configure config/database.php to accommodate both local (unsecured) and production (secure) database connection requirements?

Best Answer (As Selected By ElijahPaul)
Cronix

you could make a 2nd connection and use one or the other depending on the environment

'pgsql' => [
            'driver' => 'pgsql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '5432'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'schema' => 'public',
            'sslmode' => 'prefer',
        ],

'pgsql_production' => [
            'driver' => 'pgsql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '5432'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'schema' => 'public',
            'sslmode' => 'prefer',
            'options' => [
               // your ssl options
            ]
        ],

and in .env for production

DB_CONNECTION=pgsql_production

and for dev

DB_CONNECTION=pgsql
Cronix
Cronix
1 week ago (334,240 XP)

you could make a 2nd connection and use one or the other depending on the environment

'pgsql' => [
            'driver' => 'pgsql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '5432'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'schema' => 'public',
            'sslmode' => 'prefer',
        ],

'pgsql_production' => [
            'driver' => 'pgsql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '5432'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'schema' => 'public',
            'sslmode' => 'prefer',
            'options' => [
               // your ssl options
            ]
        ],

and in .env for production

DB_CONNECTION=pgsql_production

and for dev

DB_CONNECTION=pgsql
ElijahPaul

@Cronix I feel stupid for not thinking of that. So simple! Thank you.

Cronix
Cronix
1 week ago (334,240 XP)

You're welcome!

Please sign in or create an account to participate in this conversation.