Freshrss: Postgres connection error in Heroku

[pthall]

Hi, I could use some help debugging this connection error.

The error at setup for either the CLI or web install is consistently

Blast! Verify your database information. : Access to database is denied for [username omitted]: SQLSTATE[08006] [7] FATAL: permission denied for database "postgres" DETAIL: User does not have CONNECT privilege.

psql command with the same database connection details works. I've also added a pgtest.php file with

<?php
    $connection = pg_connect (getenv("DATABASE_URL"));
    if($connection) {
       echo 'connected';
    } else {
        echo 'there has been an error connecting';
    } 
?>

When I load the page it is successful.

Step 2 of the install is all ok - modules are loaded.

If I understand FreshRSS uses PDO and not the pgsql extension. I have pdo_pgsql and pgsql modules loaded.

This test pdotest.php also returns connected

<?php
    $db = parse_url(getenv("DATABASE_URL"));

    $pdo = new PDO("pgsql:" . sprintf(
        "host=%s;port=%s;user=%s;password=%s;dbname=%s",
        $db["host"],
        $db["port"],
        $db["user"],
        $db["pass"],
        ltrim($db["path"], "/")
    ));

    if($pdo) {
       echo 'connected';
    } else {
        echo 'there has been an error connecting';
    }
?>

If it helps I can also share access to phpinfo.php and whatever else will help project developers.
Thank you for any information on what may be wrong.

[Alkarex]

Hello,
Which version of FreshRSS are you trying?

It looks wrong to use the postgres database. It should be a project-specific one such as freshrss. You can either create it or give the freshrss user the right to autocreate the database

CREATE DATABASE "freshrss" ENCODING 'UTF8';

Here is where the connection string is built in FreshRSS:
https://github.com/FreshRSS/FreshRSS/blob/cf135073499856abb009fb9e6f297344745adf41/lib/Minz/ModelPdo.php#L74-L82

To see the generated connection string and related parameters, you can insert a debug line just before:

https://github.com/FreshRSS/FreshRSS/blob/cf135073499856abb009fb9e6f297344745adf41/lib/Minz/ModelPdo.php#L121

such as

syslog(LOG_DEBUG, ' DSN: ' . json_encode([$dsn, $username, $passwd, $options]));

or

echo ' DSN: ' . json_encode([$dsn, $username, $passwd, $options]), "\n";

[Alkarex]

Ah, sorry, that might be something else. You are not trying to use the postgres database, are you?

[Alkarex]

I think I might have found the reason during the night :-P Patch coming

[Alkarex]

@pthall Could you please try this patch? https://github.com/FreshRSS/FreshRSS/pull/3013

[pthall]

Thanks for looking at this. It does appear that the Heroku Postgres DB does not have the required permissions. With your patch I receive the same error:

Blast! Verify your database information. : Access to database is denied for [username]: SQLSTATE[08006] [7] FATAL: permission denied for database "postgres" DETAIL: User does not have CONNECT privilege.

This is using the freshrss:1.16.0-alpine container, so I tested the same container on my own (non-Heroku) machine. That container has the same error connecting to the Heroku Postgres DB. If I connect to a postgres container on my machine, it works.

Thanks again, I will find a non-Heroku hosting option. I like your FreshRSS project a lot and this might waste your time. You can close, but if there is something you want me to test I will help.

[Alkarex]

I am at the moment trying to play with PostgreSQL rights to reproduce your bug but without success so far. Could you try to comment out all those 15 lines?

https://github.com/FreshRSS/FreshRSS/blob/19b94b648bf9e8dbc79ccc1765a295660a85e1f8/lib/lib_install.php#L90-L104

[Alkarex]

I think I have a better fix. New version coming

[pthall]

This worked :)

I am at the moment trying to play with PostgreSQL rights to reproduce your bug but without success so far. Could you try to comment out all those 15 lines?

https://github.com/FreshRSS/FreshRSS/blob/19b94b648bf9e8dbc79ccc1765a295660a85e1f8/lib/lib_install.php#L90-L104

[Alkarex]

@pthall Please try again with this (proper) fix https://github.com/FreshRSS/FreshRSS/pull/3013

[pthall]

3013 with your revised patch is working for me

[Alkarex]

Thanks for the feedback. Merged in /master branch. Will be in FreshRSS 1.16.1