Postgresql support

Hi,

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

Kind regards
Marcel

Hello @marcel,

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

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

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 :slight_smile: it will be tested on postgresql (and it will be very much appreciated).

Thank you for your interest in passbolt.

Cédric

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

1 Like

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

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

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.

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?

1 Like

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

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…

1 Like

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

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