I'm using OpenProject via its Docker container (using the openproject/community:8 image). I'm trying to figure out what the password for the Postgres database is (e.g., for connecting using psql).

Is there a default username and password for the Postgres database?

Hi Jacob,

the postgres password is determined by $PGPASSWORD and defaults back to postgres

https://github.com/opf/openproject/blob/dev/docker/supervisord#L18

Best,

Oliver

Thank you very much for your response!

For anyone else reading this in the future, I haven't yet been able to get password-based authentication as described above working (with psql -U postgres -W using the root account of my Docker container). However, following this guide, I was able to connect by running su postgres; psql.

Hi @Jacob A Oliver Günther,

When I migrating my packaged OpenProject installation (master on Windows Hyper-V virtual system Debian 12) to another environment (direct copy slave on Windows Hyper-V virtual system Debian 12):

Here is my steps:

openproject config:get DATABASE_URL on master: postgres://openproject:uCpl9lFVpYj07j8OAaddQm5KEUTXSLYQ@127.0.0.1:45432/openproject

openproject config:get DATABASE_URL on slave:  postgres://openproject:WwjMpsjVnWGMWB88AjVvq2GhQAiWYEsS@127.0.0.1:45432/openproject

upload four archive files from master backup to slave backup:

attachments-20231022112754.tar.gz
conf-20231022112754.tar.gz
postgresql-dump-20231022112754.pgdump
svn-repositories-20231022112754.tar.gz

run restore commands for 3 kind of archives:

tar xzf /var/db/openproject/backup/attachments*.tar.gz -C /var/db/openproject/files
tar xzf /var/db/openproject/backup/conf*.tar.gz -C /etc/openproject
tar xzf /var/db/openproject/backup/svn-repositories*.tar.gz -C /var/db/openproject/svn

modify configure files:

/etc/openproject/installer.dat:

postgres/db_password uCpl9lFVpYj07j8OAaddQm5KEUTXSLYQ   --(to)-->   postgres/db_password WwjMpsjVnWGMWB88AjVvq2GhQAiWYEsS

/etc/openproject/conf.d/00_addon_postgres:

export DATABASE_URL="postgres://openproject:uCpl9lFVpYj07j8OAaddQm5KEUTXSLYQ@127.0.0.1:45432/openproject"    --(to)-->   
export DATABASE_URL="postgres://openproject:WwjMpsjVnWGMWB88AjVvq2GhQAiWYEsS@127.0.0.1:45432/openproject"

then run database restore command:

pg_restore --clean --if-exists --dbname $(openproject config:get DATABASE_URL) postgresql-dump-20231022112754.pgdump
openproject configure

But I get a error message, what can I do to fix this problem:

Launching installer for openproject...
chown: warning: '.' should be ':': ‘openproject.openproject’
chown: warning: '.' should be ':': ‘openproject.openproject’
chown: warning: '.' should be ':': ‘openproject.openproject’
Selected addons: legacy-installer openproject-edition postgres apache2 repositories memcached openproject
[legacy-installer] ./bin/configure
[openproject-edition] ./bin/configure
DONE
[postgres] ./bin/configure
Postgres connection OK
DONE
[apache2] ./bin/configure
DONE
[repositories] ./bin/configure
DONE
[memcached] ./bin/configure
DONE
[openproject] ./bin/configure
DONE
[legacy-installer] ./bin/preinstall
[openproject-edition] ./bin/preinstall
[postgres] ./bin/preinstall
INFO: autoinstall=install. A new PostgreSQL v13 server will be installed locally unless a conflicting installation is found.
INFO: existing installation found. Skipping to next step.
INFO: Checking connection to PostgreSQL server... psql: error: connection to server at "127.0.0.1", port 45432 failed: FATAL:  password authentication failed for user "openproject"
connection to server at "127.0.0.1", port 45432 failed: FATAL:  password authentication failed for user "openproject"
KO
ERROR: Connection KO. Aborting postgres installation. You should check the wizard configuration in /etc/openproject/installer.dat

Hi @Jacob A,

I can give the answer after testing, you can get the account and password of PostgreSQL openproject database like this:

root@Debian12:/var/www/html# openproject config:get DATABASE_URL
postgres://openproject:WwjMpsjVnWGMWB88AjVvq2GhQAiWYEsS@127.0.0.1:45432/openproject

The "openproject" is default account name, the "WwjMpsjVnWGMWB88AjVvq2GhQAiWYEsS" is the clear-text password.

In fact, you can use this command to log into the database as root account without the password entered:

root@Debian12:/var/www/html# psql postgres://openproject:WwjMpsjVnWGMWB88AjVvq2GhQAiWYEsS@127.0.0.1:45432/openproject
psql (13.12 (Debian 13.12-1.pgdg120+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

openproject=# \l
                                    List of databases
    Name     |    Owner    | Encoding |   Collate   |    Ctype    |   Access privileges
-------------+-------------+----------+-------------+-------------+-----------------------
 openproject | openproject | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres    | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0   | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
             |             |          |             |             | postgres=CTc/postgres
 template1   | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
             |             |          |             |             | postgres=CTc/postgres
(4 rows)