How to connect laravel nginx droplet (Digital Ocean) to remote managed postgres database.

mrtechmaker's photo
mrtechmaker
·

2 min read

You can see the connection parameters of the managed database in the digital ocean admin panel.

so you copy those params over to the laravel .env file like so :

DB_CONNECTION=pgsql

DB_HOST=karigaridb-do-user-1.b.db.ondigitalocean.com

DB_PORT=25060

DB_DATABASE=defaultdb

DB_USERNAME=doadmin

DB_PASSWORD=

Now you will get error as permission denied - is your database running on the host , port and accepting tcp connections.

To solve this you can disable selinux on your droplet or enable firewall permission.

After this you will get error that pg_hba.conf is not configured for the ip of your droplet. This error is due to the fact that ssl is not configured on your droplet to connect to postgres sql database.

So you can now configure the ssl cert of the managed db on your droplet as follows :

First go to the digital ocean admin panel -> manage -> databases and download the ssl ca certificate on your local machine. Now transfer this ssl certificate onto your droplet using scp command.

Now ssh to your laravel droplet and go to the config/database.php file of your laravel project and add these two lines :

       'pgsql' => [

       'driver' => 'pgsql',

        'url' => env('DATABASE_URL'),

        'host' => env('DB_HOST', '127.0.0.1'),

        'port' => env('DB_PORT', '5432'),

        'database' => env('DB_DATABASE', 'forge'),

        'username' => env('DB_USERNAME', 'forge'),

        'password' => env('DB_PASSWORD', ''),

        'charset' => 'utf8',

        'prefix' => '',

        'prefix_indexes' => true,

        'search_path' => 'public',

     **  sslmode' => env('SSL_MODE'),**

   **   'sslrootcert' => env('PGSQL_SSL_CA')**
    ],

Now add the below two lines in your .env file :

DB_CONNECTION=pgsql

DB_HOST=karigaridb-do-user-0.b.db.ondigitalocean.com

DB_PORT=25060

DB_DATABASE=defaultdb

DB_USERNAME=doadmin

DB_PASSWORD=AVNS_6DVeQ0ujVsFFi5oerjv

SSL_MODE=verify-ca

PGSQL_SSL_CA=/var/www/html/ca-certificate.crt // --- add the file path of the cert stored in your droplet

Now It Works :)

Cheers, Happy Coding.

DigitalOceanLaravel