SSL connection to PostgreSQL via certificate

axel_tsys

Did someone have a working configuration for a SSL connection from the Webserver to a Postgresql server?
I use Editor 2.4, Php 8.2 and postgresql 17.2
But I am not able to find the correct way to use the certificate configuration in the config.php from editor.
It looks like the configuration via dns or PDO will not work.
Whats wondering most is that the most examples work with

PDO::PGSQL_ATTR_SSL_...

But I got a failure
Uncaught Error: Undefined constant PDO::PGSQL_ATTR_SSL_MODE
PDO Driver for PostgreSQL in php are enabled for the correct postgresql version.

The certificates itself are running if I use a test script and start a PDO connection.

allan

Hi,

I can't find any reference to "PGSQL_ATTR_SSL_MODE" as an available attribute. Have you got a link to any PHP documentation that makes mention of it?

This StackOverflow thread suggests that you should be able to make an SSL connection using the full DSN.

Looking at how the PDO connection is made by Editor for Postgres you should be able to add the extra information needed via the 'dsn option in config.php - e.g.:

$sql_details = [
    'type' => 'Postgres',     // Database type: "Mysql", "Postgres", "Sqlserver", "Sqlite" or "Oracle"
    'user' => 'myUser',          // Database user name
    'pass' => 'myPass',          // Database password
    'host' => 'localhost', // Database host
    'port' => '',          // Database connection port (can be left empty for default)
    'db' => 'test',          // Database name
    'dsn' => ';ssl=true;...',          // PHP DSN extra information. Set as `charset=utf8mb4` if you are using MySQL
    'pdoAttr' => [],   // PHP PDO attributes array. See the PHP documentation for all options
];

Allan

axel_tsys

Hi Alan,
it looks like this is the correct way.
In the dsn string you must NOT describe again the user, password, dbname like all AI will prompt to you.
This is my config that worked at the end:

$dsn="sslmode=require;sslcert=/xxx/cert.crt;sslkey=/xxx/cert.key;sslrootcert=/xxx/root.crt";

$sql_details = array(
    "type" => "Postgres",     // Database type: "Mysql", "Postgres", "Sqlserver", "Sqlite" or "Oracle"
    "user" => "user",          // Database user name
    "pass" => "",                // Database password
    "host" => "127.0.0.1", // Database host
    "port" => "5432",          // Database connection port (can be left empty for default)
    "db"   => "public",
    "dsn"  => $dsn,          // PHP DSN extra information. Set as `charset=utf8mb4` if you are using MySQL
    "pdoAttr" => array()   // PHP PDO attributes array. See the PHP documentation for all options
);

and this is the related entry in pg_hba.conf

hostssl     public           user            samenet        cert

Thanks
Axel

allan

Looks good - thanks for sharing that with us.

Yup, the AI doesn't correctly parse and understand the code, so it isn't too surprising it doesn't get it right in this case.

Allan