How to pass arbitrary connection parameters when connecting to a Postgres database?

[Maniload]

Hello 👋,

I'm using Eloquent (standalone) to connect to a Postgres database. Unfortunately I need a few connection options that don't seem to be currently supported by the database connector (Illuminate\Database\Connectors\PostgresConnector).

The options I need include hostaddr, target_session_attrs, connect_timeout and sslcompression. But of course there are even more options defined here that don't seem to be supported.

Right now I'm using an ugly workaround that injects the options using the host parameter, which looks like this:

use Illuminate\Database\Capsule\Manager;

$capsule = new Manager();
$capsule->addConnection([
    'driver' => 'pgsql',
    'host' => 'host1,host2;hostaddr=12.34.56.78,12.34.56.79;target_session_attrs=read-write;connect_timeout=10;sslcompression=0',
    'port' => '54321',
    'username' => 'test',
    'database' => 'test',
    'sslmode' => 'verify-full',
    'sslcert' => '/path/to/sslcert',
    'sslkey' => '/path/to/sslkey',
    'sslrootcert' => '/path/to/sslrootcert',
]);
$capsule->setAsGlobal();
$capsule->bootEloquent();

But maybe I'm just missing something and there is a better way to do this already. Otherwise it would be helpful if there was a way to pass arbitrary connection parameters.

I hope you can help me!

[leigeber]

@Maniload I'm not using Eloquent standalone, but at first glance it looks like addConnection may accept the same values as the config file values in Laravel, since they all end up passed into the PDO connection.

So just passing connection parameters in "options". Untested, so I could be wrong.

Something like this...

$capsule->addConnection([
    'driver' => 'pgsql',
    // others...,
    'options' => [
        'connect_timeout' => 10 // Might need PDO::ATTR_TIMEOUT
    ]
]);

If that doesn't work. You might just build them from an array so they're more readable. Something like this...

$options = [
    'hostaddr' => '12.34.56.78,12.34.56.79',
    'target_session_attrs' => 'read-write',
    // others...
];

[
    'host' => 'host1,host2;' . http_build_query($options, '=', ';')
]

[Maniload]

Thanks for taking the time to reply. Unfortunately your proposed solution doesn't work after a quick test. I'm pretty sure options is only used for PDO attributes. As for your other solution, I feel like this is just a nicer way to use the workaround I explained above. In my opinion Laravel should support this without any hacks or workarounds.

Maybe a more general solution would be better here. My previous pull request basically had two problems:

• Some of the options could overlap with Laravel options. In this case it was options but it could affect other options as well or in the future.
• Every time Postgres adds a new option it has to be added manually here too to stay compatible.

Another idea I had was to add an option to define the DNS manually. There's already the url option which lets you specify a connection URL, but that only supports the same options as without it. With an option to define the DNS we wouldn't have to worry about overriding other Laravel-defined options and we would be forward-compatible with any new Postgres options.

I would be happy to open another pull request, if this is something the Laravel team and the community would be interested in.

[macropay-solutions]

We also tried this but it seems is disregarded for mysql at least. The connection is retried 3 times then it throws error.

    'options' => [PDO::ATTR_TIMEOUT => 5]