Postgresql support

marcel · March 21, 2018, 1:49pm

Hi,

Does Passbolt support Postgresql as a database?
I noticed in the github code a few occurrences of Postgresql

Kind regards
Marcel

cedric · March 23, 2018, 4:25am

Hello @marcel,

Not yet, but we’d like to.
The charset we use for mysql is not compatible with postgresql.

You can see here () we use the charset utf8mb4. But postgresql would require utf8 which is already in 4 bytes encoding.

github.com

passbolt/passbolt_api/blob/develop/config/Migrations/20170830064410_V162InitialMigration.php#L63


    // Check the latest 1.x migration is done
    $latestMigrationName = 'Migration_1_6_1';
    $schemaMigrationResult = $this->query("SELECT * FROM schema_migrations WHERE class='$latestMigrationName'");
    $schemaMigrationRows = $schemaMigrationResult->fetchAll();
    if (!count($schemaMigrationRows)){
        throw new Exception('Can not upgrade. Please upgrade to the latest 1.x version first and retry. See https://help.passbolt.com/hosting/update.');
    }
}


// Reset the collation just in case
$this->execute('ALTER DATABASE ' . $databaseName . ' COLLATE utf8mb4_unicode_ci');


// If this is an upgrade from v1
if ($tableCount > 0) {
    // Alter collation
    foreach ($tables as $table) {
        $this->execute('ALTER TABLE ' . $table . ' COLLATE utf8mb4_unicode_ci');
    }
    return;
}

There are few changes to make it compatible with postgresql.
We already setup the travis to test it, this means that if you send us a pull request it will be tested on postgresql (and it will be very much appreciated).

Thank you for your interest in passbolt.

Cédric

adrianschneider · May 1, 2019, 8:26am

Hi @cedric,

I want to use passbolt in our company, however we default to postgres. As I want to avoid maintaining two replicated and backed up databases, I would like to implement the postgres compatibility of passbolt. Could you advise me what has to be done? Is the encoding point the only one?

Best wishes
Adrian

cedric · May 2, 2019, 6:02am

Hello @adrianschneider,

This improvement will be very much welcomed!

The last time we checked the postgres compatibility, the encoding was the first blocker.
See: https://travis-ci.org/passbolt/passbolt_api/jobs/492625639

All our SQL requests are handled by the CakePHP ORM so we should not have too many issues on that side.

The database is handled by phinx through multiple migration scripts that you will find in the folder config/Migrations.

You can start by tackling the encoding issue, and then if you encounter any other issue, let us now. We’ll be please to help you.

Good luck,
Cedric

jsm · April 6, 2021, 10:28am

Hi
How is the current CI tests set up? I would like to work on postgres support, but it seems you have abandoned travis?

remy · April 6, 2021, 10:48am

Hi @jsm yes we abandoned travis since we have a more complex build now, as we’re building ubuntu and debian packages. These are running with the tests on a gitlab CI. You can find the config at the root: passbolt_api/.gitlab-ci.yml at common · passbolt/passbolt_api · GitHub

As you can see on common branch we’ve made the switch to CakePHP4 which will be ready in the next release (most likely next week). Since CakePHP are now relying on Github Actions for testing, so we could enable it / replicate their setup as well for the convenience of people who want to develop without having to build a dedicated test environment.

jsm · April 7, 2021, 12:44am

I changed the test environment to work on gitlab.com without access to the test-php-images. It is not yet working for postgresql since as of now to much ci code is for mysql only. Some conditionals might be able to fix that. But some avatar creation tests are failing. You can have a look at my work here Jobs · Jesper Schmitz Mouridsen / passbolt_api · GitLab and here GitHub - jsm222/passbolt_api: Passbolt CE Backend, a JSON API written with Cakephp I would like to know what you think and whether you are planning to keep gitlab-ci or to switch to github actions completely?

remy · April 7, 2021, 6:08am

We’re going to keep gitlab-ci for sure, but we’re going to try to enable github actions for the community to test.

jsm · April 9, 2021, 10:52am

Hi again. I manged to setup CI for postgres. And got a local test setup working using postgres. I get a lo t of
SQLSTATE[42P01] "Undefined table: 7 ERROR: missing FROM-clause entry for table"
They seem all to be related to the permission relation. A hack for vendor/bin/phpunit --filter '/.*/' tests/TestCase/Controller/Resources/ResourcesAddControllerTest.php is to use the permissions (note plural) relation and add the permission as permissions[0] (which is properly a wrong way, but perhaps it could shed some light on the problem… I have tested with Cake Php 4 on the common branch it also has the problem…
Perhaps there is a proper workaround? Thanks…

remy · April 9, 2021, 11:57am

We’ll schedule a chat to try to get to the bottom of it.

remy · February 28, 2022, 1:57pm

Currently available in Beta. See. How to configure Passbolt with Postgresql (experimental) | by Jean-Christophe Vassort | Feb, 2022 | passbolt