Issues with Dédalo installation – Database import failure - .pgpass

novice

I'm having trouble installing Dédalo and I'm stuck at the database installation step.

Without .pgpass in /var/www/, I get the following error:

Error. Database import failed! Verify your .pgpass file and look for errors in php error file. - PHP get_current_user: miusuario - PHP whoami: www-data - PHP home: /var/www - .pgpass file permissions: file not found!

With .pgpass in /var/www/, I get this error:

Error. Database import failed! Verify your .pgpass file and look for errors in php error file. - PHP get_current_user: miusuario - PHP whoami: www-data - PHP home: /var/www - .pgpass file permissions: 0600

Previously, I managed to import the database, but I couldn't continue because it didn't recognize the root password. Now, I can't even install the database.

If I run:
PGPASSFILE=~/.pgpass psql -U myuser -d dedalo_xxww -h localhost
It only connects if there is a .pgpass file in /var/www/.

According to the installation guide, the .pgpass file should only be located in: /home/myuser/.pgpass
I think I might be missing something related to .pgpass.

Any help would be greatly appreciated. Thanks in advance!

DEDALO VERSION 6
Ubuntu 24.04.2 LTS
PHP 8.3.17 (cli) (built: Feb 15 2025 09:09:25) (NTS) with Zend OPcache v8.3.17, Copyright (c), by Zend Technologies
psql (PostgreSQL) 17.3 (Ubuntu 17.3-3.pgdg24.04+1)
Apache/2.4.58 (Ubuntu)

Alex

novice

Hi!

Something about .pgpass file. As postgres documentation say here:

1 the file need to be located at home of the user. So review if the dedalo user (GNU/Linux user) home is /var/www/ (as I read in the error your GNU/Linux user is miusuario)
2 file format is:

hostname:port:database:username:password

So you will need wire something as:

127.0.0.1:5434:My_dedalo_db:my_dedalo_db_user:My_Super_pasword!54216

Note1: review the port, when you have a previous PostgreSQL, most installers will choose the next free port, usually 5433.
Note2: .pgpass It need a return at the end of the line (this is not documented by postgreSQL)

3 permissions of the file need to be 600.

chmod 0600 .pgpass

4 User needs to be the Linx/GNU user, in your case miusuario. The group need to be root

5 The PHP-FPM pool user need to be the Linux/GNU user, in your case miusuario. PHP-FPM needs to read the .pgpass as owner.

6 Apache Directory of the virtual hosts needs to be pointed to a any directory inside of the user home, you can see a typical installation tree here.

For example:

<Directory /var/www/httpdocs></Directory>

Apache should not be able to access the user's home, it should only be able to access httpdocs.

Note: this is a way to configure it, but you can change if you want use Apache and PHP with other directories...

I hope it helps.

novice

Hi Alex, thanks a lot for your help so far! I really appreciate all your suggestions.

I’ve followed the indicated steps and haven’t found anything unusual.
(Important: .pgpass needs a newline character at the end of the file — this isn’t documented by PostgreSQL!)

I created a very simple PHP file and placed it in /var/www/html/. Then I assigned permissions like so:

sudo chown www-data:www-data /var/www/html/testconnexio3.php
sudo chmod 644 /var/www/html/testconnexio3.php
Here is the code I used:

<?php
// Connection string without a password, because it will be read from .pgpass
$connString = "host=localhost dbname=dedalo_xxx user=myuser";

// Attempt to connect
$conn = pg_connect($connString);
if (!$conn) {
    die("Error connecting to the database: " . pg_last_error() . "\n");
}
echo "Connection established successfully!\n\n";

// 1) Create a test table if it doesn't exist
$createTableSql = "
    CREATE TABLE IF NOT EXISTS public.prova (
        id SERIAL PRIMARY KEY,
        nom TEXT NOT NULL,
        edat INT
    );
";
$resultCreate = pg_query($conn, $createTableSql);
if (!$resultCreate) {
    die("Error creating the table: " . pg_last_error($conn) . "\n");
}
echo "Table 'prova' created (or it already existed).\n\n";

// 2) Insert some sample data (add a couple of example records)
$insertSql = "
    INSERT INTO public.prova (nom, edat)
    VALUES
        ('John Doe', 30),
        ('Maria Lluna', 25)
    RETURNING id;
";
$resultInsert = pg_query($conn, $insertSql);
if (!$resultInsert) {
    die("Error inserting data: " . pg_last_error($conn) . "\n");
}
$numInserted = pg_num_rows($resultInsert);
echo "$numInserted new records were inserted into the 'prova' table.\n\n";

// 3) Read the data to verify
$querySelect = "SELECT * FROM public.prova ORDER BY id;";
$resultSelect = pg_query($conn, $querySelect);
if (!$resultSelect) {
    die("Error querying the table: " . pg_last_error($conn) . "\n");
}

echo "Records found in 'public.prova':\n";
while ($row = pg_fetch_assoc($resultSelect)) {
    echo "ID: {$row['id']}, Nom: {$row['nom']}, Edat: {$row['edat']}\n";
}

// Close the connection
pg_free_result($resultSelect);
pg_close($conn);
?>

The output of this file shows that I can connect, write, and read from the database using .pgpass:

Connection established successfully! Table 'prova' created (or it already existed). 2 new records were inserted into the 'prova' table. Records found in 'public.prova': ID: 1, Nom: John Doe, Edat: 30 ID: 2, Nom: Maria Lluna, Edat: 25

However, during the installer process, I still get this message:

Error. Database import failed! Verify your .pgpass file and look for errors in php error file. - PHP get_current_user: myuser - PHP whoami: www-data - PHP home: /var/www - .pgpass file permissions: 0600

I’m not sure if it’s a file permission issue. I haven’t created any specific new linux user. Maybe I should make all the files owned by www-data? Or maybe I must delete the entire folder /var/www/html/dedalo/ and start from 0 (again).

Any advice or pointers would be greatly appreciated. Thank you all in advance for your help!

Alex

novice

Hi again... 😉

to check your .pgpass you will need to use it. the PHP script that you test use its own connection, so try to do something as:

1 change to the GNU/Linux user.

su myuser

2 run psql

psql -d dedalo_xxx -U myuser -h localhost -p 5432

You should enter into the database without inserting the password.

If not, something is wrong in the .pgpass
If all works, the PHP user could not correct. Review the PHP user. I read in the error:

Error. Database import failed! Verify your .pgpass file and look for errors in php error file. - PHP get_current_user: myuser - PHP whoami: www-data - PHP home: /var/www - .pgpass file permissions: 0600

Note that PHP get_current_user and PHP whoami are not the same... myuser vs www-data

I hope it helps

Best

jordi_coopdevs

Hi!

This is my first Dedalo installation.
Following the manual installation video, I'm getting the same error and can't seem to fix it.

The error message is:
Error. Database import failed! Verify your .pgpass file and look for errors in the PHP error file. - PHP get_current_user: dedalo - PHP whoami: www-data - PHP home: /var/www - .pgpass file permissions: 0600

If I run:
sudo -u www-data psql -d dedalo_test_db -U dedalo_db -h localhost -p 5432

sudo -u dedalo psql -d dedalo_test_db -U dedalo_db -h localhost -p 5432

It successfully accesses the database, so the .pgpass file seems to be correct.

I see that get_current_user and PHP whoami are different. Should they be the same? In that case, should it be Dedalo or www-data?

Can you give me any pointers on what might be wrong?

Thank you very much!

Alex

Hi!

It's important to understand how PHP-FPM users works and how to deal with them.

PHP requires a GNU/Linux user to control the user's permissions and actions. This is crucial because using a general user profile for PHP will result in losing control over the user's management, such as specifying a specific .pgpass file. Therefore, it's essential to create a dedicated environment for the PHP user.

As Dédalo will execute some PostgreSQL management tasks in the CLI, it requires access to the .pgpass file as the owner. Therefore, the GNU/Linux user must be the PHP-FPM user and the .pgpass user owner. It is essential to ensure that the environment is consistent.

Ok, step by step:
1 create a GNU/Linux user for Dédalo. It will create a $HOME for this user (and this directory could be your project domain as dedalo.dev)
2 assign this user to PHP-FPM (the group could be the generic www-data, but the user must be the GNU/Linux user created for Dédalo)
3 The $HOME of this user will be the scope for PHP-FPM, so, you can include the .pgpass in this directory.
4 DON'T point the Apache DocumentRoot directive to the$HOME directory, this point is super important, because if you point it you are allowing to show the environment!!, so, to create a httpdocs inside the$HOME directory.
6 Point the Apache vhosts to the $HOME/httpdocs directory. Any files outside of httpdocs will be hidden from the outside world, so you can store logs and other non-public files in the $HOME directory.
7 Include the /dedalo directory that was downloaded into the httpdocs and run the installer.

This is a typical vhosts configuration.

/home/gnu_linux_dedalo_user/ httpdocs/dedalo

e.g:

/home/dedalo.dev/.pgpass
/home/dedalo.dev/log
/home/dedalo.dev/httpdocs/dedalo
/home/dedalo.dev/httpdocs/dedalo/core
/home/dedalo.dev/httpdocs/dedalo/libs
/home/dedalo.dev/httpdocs/web
/home/dedalo.dev/httpdocs/web/index.html
...

If you require additional virtual hosts, you can create another GNU/Linux user, configure it as a PHP-FPM user, and assign it its own .pgpass file (and database). Both users can coexist on the same machine without any conflicts.

Can you do it in other way? yes, of course, and maybe could be better in some situations, e.g. development environment that is not important to maintain hidden the .pgpass or sessions, this is only a recommendation, but, is important that you control how deal with the users, vhosts, PHP environment, PostgreSQL environment, etc...

Little more explanation about this here.

I hope is clear and helps.

Note: Perhaps should we enhance the installation documentation?. I understand that you have the freedom to install the software as you please. You can set the www-data user to the root directory if you wish, or modify the paths in config.php. However, perhaps we should be more explicit in the install documentation??

Best

jordi_coopdevs

It works!

Thank you very much