Allow database authentication using `pg_service.conf`

[ivanov17]

🚀 Feature

Hello! Unfortunately, it is currently not possible to authenticate to a PostgreSQL database using the credentials stored in the pg_service.conf file. However, this is a more secure method than storing credentials in the application configuration, even if it is not located at the root of the web server.

As far as I can see, the application currently requires storing database credentials in the application configuration.

https://github.com/cypht-org/cypht/blob/3579a081b544179bce26a7cacf0fce51ad2f9dbf/lib/db.php#L18

This leads to the fact that despite a successful IMAP login, I can't to login to application.

    [0] => Using Hm_DB_Session with Hm_Auth_IMAP
    [1] => Using DB user configuration
    [2] => Already registered module for message re-attempted: imap_show_message
    [3] => Already registered module for message re-attempted: imap_message_list_type
    [4] => Already registered module for message re-attempted: imap_server_ids
    [5] => Using sapi: cli-server
    [6] => Request type: HTTP
    [7] => Request path: /
    [8] => TLS request: 0
    [9] => Mobile request: 0
    [10] => Page ID: home
    [11] => Using Hm_Cache for cache
    [12] => CACHE backend using: noop
    [13] => Setting cookie: name: hm_id, lifetime: 0, path: /, domain: mail.anarhist.net, secure: , html_only 1
    [14] => Missing configuration setting for db_user
    [15] => Missing configuration setting for db_pass
    [16] => Missing configuration setting for db_name
    [17] => Missing configuration setting for db_host
    [18] => SQLSTATE[08006] [7] could not match 1 host names to 2 hostaddr values
    [19] => Setting cookie: name: hm_msgs, lifetime: 0, path: /, domain: mail.anarhist.net, secure: , html_only 1
    [20] => Redirecting to /
    [21] => PHP version 8.2.11
    [22] => Zend version 4.2.11
    [23] => Peak Memory: 2048
    [24] => PID: 834
    [25] => Included files: 89

At the same time, on the Cypht login page I see the message Invalid username or password, which is incorrect because the IMAP server logs indicate the opposite.

Design, Layout, Architecture

I think making the db_user, db_pass, db_name and db_host parameters optional would be sufficient. Or maybe the application should try to connect to the database without using any parameters, and only if there is no connection should it try to use the credentials from the configuration.

Thank you.

[ivanov17]

I'm not familiar with PHP at all, so I can't suggest a working patch. But I remember that fluxbb forum application was able to connect to the database without any application-side configuration at all. That was great.

Unfortunately, fluxbb did not use the php-pdo module, it used php-pgsql, php-mysqli, etc. But at first glance there is nothing special here: https://github.com/fluxbb/fluxbb/blob/master/include/dblayer/pgsql.php#L41-L76 (code is GPL-licensed). Hopefully the same principle will work for PDO.

[marclaporte]

@ivanov17 Thank you for the report. AFAIK, PostgreSQL is not currently used by the most active Cypht developers so I don't expect a quick resolution. But I'll tag with "help wanted" so the right person can step up.

[ivanov17]

@marclaporte Thank you. Maybe there is some workaround for now to not store passwords in the INI file? Unfortunately, I couldn't find any information about using environment variables to pass Cypht configuration variables. Please tell me, is this possible?

And another question, does the INI file support variable substitution?

[marclaporte]

"Persistent data stored between logins is encrypted with a key derived from your clear text password, which is obviously not stored anywhere" Reference: https://cypht.org/security.html

[ivanov17]

@marclaporte Thanks for the link, but in this case I mean that the database credentials, including passwords, are stored in hm3.ini file.

In my setup, all containerized web applications read the standard PGSERVICE environment variable and then take the database connection information from the pg_service.conf file. For now, with the exception of Cypht.

I can easily add other environment variables for the application servers, but would like to avoid storing credentials in the configuration file, which in my case is stored in a Git repository.

That's why I ask about environment variables 🙂

[marclaporte]

@Shadow243 Can you look into environment variables ?

In Tiki Manager, we use https://packagist.org/packages/symfony/dotenv as per https://gitlab.com/tikiwiki/tiki-manager/-/blob/master/composer.json#L24 and I'd like to add support to Tiki as well. It would make sense for Cypht, Tiki and Tiki Manager to harmonize on a solution.

Thanks!

[marclaporte]

@ivanov17 Please test the newly added environment variables :-)