All articles

How to configure Passbolt with Postgresql (experimental)

7 min. read

Jean-Christophe Vassort

Jean-Christophe Vassort

28 February, 2022

Since the latest v3.5.0 passbolt version, it is possible to use PostgreSQL as a database backend (experimental) for passbolt. Let’s see how to do it.


💡
WARNING: Configuring PostgreSQL is only intended for fresh installations. Do not configure it on an existing installation, as it's likely going to cause system instability or break functionality.

Option 1 — the easy way: docker

curl -Ls https://raw.githubusercontent.com/passbolt/passbolt_docker/master/docker-compose/docker-compose-ce-postgresql.yaml -o docker-compose.yaml
[ "$(sha256sum docker-compose.yaml | awk '{print $1}')" = "56b04379d6ccf3faff4b7a7d62b48929941513301b3552cc32aeea355e8a5504" ] && echo "Checksum OK" || (echo "Bad checksum. Aborting" && rm -f docker-compose.yaml)
docker-compose up -d
docker-compose exec passbolt su -m -c
"/usr/share/php/passbolt/bin/cake \
passbolt register_user \
-u <[email protected]> \
-f <yourname> \
-l <surname> \
-r admin" -s /bin/sh www-data

Option 2 — the hacker way

wget https://raw.githubusercontent.com/passbolt/passbolt-dep-scripts/main/passbolt-repo-setup.ce.sh
[ "$(sha256sum passbolt-repo-setup.ce.sh | awk '{print $1}')" = "ce96ab921e2fa448d48da018e3be0e9646791629dffb13707bbc49b55c739490" ] && sudo bash ./passbolt-repo-setup.ce.sh || echo "Bad checksum. Aborting" && rm -f passbolt-repo-setup.ce.sh
sudo DEBIAN_FRONTEND=noninteractive apt-get install --no-install-recommends passbolt-ce-server
sudo apt install postgresql php-pgsql
$ sudo su - postgres
postgres:~$ psql
psql (13.5 (Debian 13.5–0+deb11u1))
Type "help" for help.
postgres=# create database passboltdb;
CREATE DATABASE
postgres=# create user passboltuser with encrypted password 'passboltpassword';
CREATE ROLE
postgres=# grant all privileges on database passboltdb to passboltuser;
GRANT
postgres=# quit
sudo -H -u www-data bash -c "gpg --homedir /var/lib/passbolt/.gnupg --batch --no-tty --gen-key <<EOF
Key-Type: default
Key-Length: 4096
Subkey-Type: default
Subkey-Length: 4096
Name-Real: John Doe
Name-Email: [email protected]
Expire-Date: 0
%no-protection
%commit
EOF"
$ sudo -H -u www-data bash -c "gpg --homedir /var/lib/passbolt/.gnupg --armor --export-secret-keys [email protected]" > /etc/passbolt/gpg/serverkey_private.asc
$ sudo -H -u www-data bash -c "gpg --homedir /var/lib/passbolt/.gnupg --armor --export [email protected]" > /etc/passbolt/gpg/serverkey.asc
sudo -H -u www-data bash -c "gpg --homedir /var/lib/passbolt/.gnupg --show-keys /etc/passbolt/gpg/serverkey.asc"
pub rsa2048 2022–02–23 [SC]
B594D78399DE0074E000F191FD9B23E52AA2C15D
uid John Doe <[email protected]>
sub rsa2048 2022–02–23 [E]
sudo dpkg-reconfigure passbolt-ce-server
sudo rm /etc/nginx/sites-enabled/default
sudo systemctl reload nginx
sudo cp /etc/passbolt/passbolt.default.php /etc/passbolt/passbolt.php
  • your fullBaseUrl
  • postgresql database credentials
  • Your email settings
  • OpenPGP serverkeys fingerprint:
<?php

return [
  'App' => [
    // A base URL to use for absolute links.    
    // The fully qualified domain name (including protocol) to your application’s root    
    // e.g. where the passbolt instance will be reachable to your end users.    
    // This information is need to render images in emails for example.    
    'fullBaseUrl' => 'https://passbolt.domain.tld',
  ],  
  // Database configuration.
  'Datasources' => [
    'default' => [
      'driver' => \Cake\Database\Driver\Postgres::class,
      'host' => '127.0.0.1',
      'port' => '5432',
      'username' => 'passboltuser',
      'password' => 'passboltpassword',
      'database' => 'passboltdb',
    ],
  ],
  // Email configuration.
  'EmailTransport' => [
    'default' => [
      'host' => 'localhost',
      'port' => 25,
      'username' => 'user',
      'password' => 'secret',
      // Is this a secure connection? true if yes, null if no.
      'tls' => null,
      //'timeout' => 30,
      //'client' => null,
      //'url' => null,
    ],
  ],
  'Email' => [
    'default' => [
      // Defines the default name and email of the sender of the emails.    
      'from' => ['passbolt@your_organization.com' => 'Passbolt'],
      //'charset' => 'utf-8',
      //'headerCharset' => 'utf-8',
    ],
  ],
  'passbolt' => [
    'gpg' => [
      'serverKey' => [
        // Server private key fingerprint.
        'fingerprint' => 'B594D78399DE0074E000F191FD9B23E52AA2C15D',
        'public' => CONFIG . 'gpg' . DS . 'serverkey.asc',
        'private' => CONFIG . 'gpg' . DS . 'serverkey_private.asc',
      ],
    ],
  ],
];
sudo chown root:www-data /etc/passbolt/passbolt.php
sudo -H -u www-data bash -c "/usr/share/php/passbolt/bin/cake passbolt install --no-admin"
sudo -H -u www-data bash -c "/usr/share/php/passbolt/bin/cake passbolt register_user -u [email protected] -f Admin -l Passbolt -r admin"
     ____                  __          ____
    / __ \____  _____ ____/ /_  ____  / / /_
   / /_/ / __ `/ ___/ ___/ __ \/ __ \/ / __/
  / ____/ /_/ (__  |__  ) /_/ / /_/ / / /
 /_/    \__,_/____/____/_.___/\____/_/\__/
Open source password manager for teams
-------------------------------------------------------------------------------
User saved successfully.
To start registration follow the link provided in your mailbox or here:
https://passbolt.domain.tld/setup/install/d2d1680c-bec6-4138-b40d-c451a0294713/1ba2158a-d1e2-4a4d-94fc-9ef1757042b9

Option 3 — migrating to PostgreSQL

sudo apt install postgresql php-pgsql pgloader
  • postgresql: this package will install postgresql server
  • php-pgsql: the PHP postgresql extension
  • pgloader: It is the tool who will migrate your MySQL data to PostgreSQL: https://pgloader.io/
$ sudo su - postgres
postgres:~$ psql
psql (13.5 (Debian 13.5–0+deb11u1))
Type "help" for help.
postgres=# create database passboltdb;
CREATE DATABASE
postgres=# create user passboltuser with encrypted password 'passboltpassword';
CREATE ROLE
postgres=# grant all privileges on database passboltdb to passboltuser;
GRANT
postgres=# quit
load database
from mysql://<user>:<password>@localhost/<dbname>
into postgresql://<user>:<password>@localhost/<dbname>
alter schema 'passbolt' rename to 'public';
sudo -H -u postgres pgloader passbolt.load
(…)
'Datasources' => [
  'default' => [
    'driver' => \Cake\Database\Driver\Postgres::class,
    'host' => 'localhost',
    'port' => '5432',
    'username' => 'passboltuser',
    'password' => 'passboltpassword',
    'database' => 'passboltdb',
  ],
],
(…)
sudo -H -u www-data bash -c "/usr/share/php/passbolt/bin/cake passbolt migrate_postgres"
     ____                  __          ____
    / __ \____  _____ ____/ /_  ____  / / /_
   / /_/ / __ `/ ___/ ___/ __ \/ __ \/ / __/
  / ____/ /_/ (__  |__  ) /_/ / /_/ / / /
 /_/    \__,_/____/____/_.___/\____/_/\__/
Open source password manager for teams
-------------------------------------------------------------------------------
using migration paths
 - /etc/passbolt/Migrations
using seed paths
 - /etc/passbolt/Seeds
using environment default
using adapter pgsql
using database passboltdb
ordering by creation time
== 20211121231300 V340MigrateASCIIFieldsEncoding: migrating
== 20211121231300 V340MigrateASCIIFieldsEncoding: migrated 0.1839s
== 20211122732400 V350ConvertIdFieldsToUuidFields: migrating
== 20211122732400 V350ConvertIdFieldsToUuidFields: migrated 0.0142s
All Done. Took 0.2275s
Passbolt can now be used with Postgres.

Going further

Fig. HA cluster with streaming replication driven by Patroni
Fig. HA cluster with streaming replication driven by Patroni

Conclusion

Continue reading

Managing Secrets in Ansible using passbolt

7 min. read

Managing Secrets in Ansible using passbolt

Ansible is an open source IT automation engine sponsored by Red Hat to enable devops, developers and system administrators to automate the…

Jean-Christophe Vassort

Jean-Christophe Vassort

7 April, 2022

Security and compliance roundup

4 min. read

Security and compliance roundup

A summary of the recent achievements on the security and compliance front: SOC2 Type II, independent audits, Security Made in Europe label

Passbolt team

Passbolt team

14 February, 2022

Flag of European UnionMade in Europe. Privacy by default.