Allow passwordless postgres

[siccegge]

Hi!

Would be cool if one can use simplesamlphp with postgres' peer authentication (i.e. simplesamlphp is authenticated to access the database due to being run by the correct unix account on the system).

This avoids creating passwords that are then stored plain on the server where they don't add any extra security but still (somewhat) interfere with e.g. configuration management.

As far as I can tell the only change needed is allow having username and password empty when connecting to the database.

https://www.postgresql.org/docs/10/auth-methods.html#AUTH-PEER

[tvdijen]

Hi @siccegge ! I'm sorry we never responded to this.. What exactly is the problem here because, looking at the code, it's perfectly possible to leave the password empty in the configuration?

[nathanjrobertson]

I've just spent an hour or so trying to reproduce this. What @siccegge is trying to do is use a UNIX socket instead of a TCP socket to connect to the database. I've just got it working, so I'll share the configuration for those who find this on Google.

The authsources.php entry for the sqlauth authentication method:

<?php

$config = [
    'example-unix-socket-sql' => [
        'sqlauth:SQL',
        'dsn' => 'pgsql:host=/var/run/postgresql;dbname=simplesaml',
        'username' => 'www-data',
        'password' => 'this-is-ignored',
        'query' => 'SELECT uid, givenName, email, eduPersonPrincipalName FROM users WHERE uid = :username ' .
            'AND password = SHA2(CONCAT((SELECT salt FROM users WHERE uid = :username), :password), 256);',
    ],
[...]

The key thing is to make the host parameter of the dsn be the directory that the UNIX socket is located in. That's defined in the unix_socket_directories parameter in postgresql.conf on the server (/etc/postgresql/17/main/postgresql.conf on Debian Trixie with the distro package for postgresql).

The username should be set to the UNIX username of the user running SimpleSAMLphp (ie. the web server user or the php-fpm user, depending on your setup). The query parameter is as per any other sqlauth configuration.

The password parameter is ignored (so you can put any placeholder string value in there), although if you don't specify it then you get an error:

Backtrace:
2 src/SimpleSAML/Error/ExceptionHandler.php:36 (SimpleSAML\Error\ExceptionHandler::customExceptionHandler)
1 /var/www/simplesamlphp_idp/vendor/symfony/error-handler/ErrorHandler.php:538 (Symfony\Component\ErrorHandler\ErrorHandler::handleException)
0 [builtin] (N/A)
Caused by: Exception: Missing required attribute 'password' for authentication source example-unix-socket-sql
Backtrace:
8 modules/sqlauth/src/Auth/Source/SQL.php:87 (SimpleSAML\Module\sqlauth\Auth\Source\SQL::__construct)
7 src/SimpleSAML/Auth/Source.php:314 (SimpleSAML\Auth\Source::parseAuthSource)
6 src/SimpleSAML/Auth/Source.php:355 (SimpleSAML\Auth\Source::getById)
5 modules/core/src/Controller/Login.php:113 (SimpleSAML\Module\core\Controller\Login::loginuserpass)
4 /var/www/simplesamlphp_idp/vendor/symfony/http-kernel/HttpKernel.php:181 (Symfony\Component\HttpKernel\HttpKernel::handleRaw)
3 /var/www/simplesamlphp_idp/vendor/symfony/http-kernel/HttpKernel.php:76 (Symfony\Component\HttpKernel\HttpKernel::handle)
2 /var/www/simplesamlphp_idp/vendor/symfony/http-kernel/Kernel.php:197 (Symfony\Component\HttpKernel\Kernel::handle)
1 src/SimpleSAML/Module.php:234 (SimpleSAML\Module::process)
0 public/module.php:17 (N/A)

Setting password to null also gives a stacktrace:

Backtrace:
2 src/SimpleSAML/Error/ExceptionHandler.php:36 (SimpleSAML\Error\ExceptionHandler::customExceptionHandler)
1 /var/www/simplesamlphp_idp/vendor/symfony/error-handler/ErrorHandler.php:538 (Symfony\Component\ErrorHandler\ErrorHandler::handleException)
0 [builtin] (N/A)
Caused by: Exception: Expected parameter 'password' for authentication source example-unix-socket-sql to be a string. Instead it was: NULL
Backtrace:
8 modules/sqlauth/src/Auth/Source/SQL.php:92 (SimpleSAML\Module\sqlauth\Auth\Source\SQL::__construct)
7 src/SimpleSAML/Auth/Source.php:314 (SimpleSAML\Auth\Source::parseAuthSource)
6 src/SimpleSAML/Auth/Source.php:355 (SimpleSAML\Auth\Source::getById)
5 modules/core/src/Controller/Login.php:113 (SimpleSAML\Module\core\Controller\Login::loginuserpass)
4 /var/www/simplesamlphp_idp/vendor/symfony/http-kernel/HttpKernel.php:181 (Symfony\Component\HttpKernel\HttpKernel::handleRaw)
3 /var/www/simplesamlphp_idp/vendor/symfony/http-kernel/HttpKernel.php:76 (Symfony\Component\HttpKernel\HttpKernel::handle)
2 /var/www/simplesamlphp_idp/vendor/symfony/http-kernel/Kernel.php:197 (Symfony\Component\HttpKernel\Kernel::handle)
1 src/SimpleSAML/Module.php:234 (SimpleSAML\Module::process)
0 public/module.php:17 (N/A)

Strictly speaking, the password parameter should be optional, only required if TCP sockets. The reality is that using UNIX sockets would be a very niche use case, and I think the workaround of specifying a dummy string there to make that use case work should be ok.

@tvdijen If anything this one is something missing in the documentation, not an actual bug. Would you like a PR to update the documentation, or do you think this is an uncommon configuration, so perhaps not worth bloating out the documentation with?

[tvdijen]

Oh yes, please write a little bit of documentation for this if possible!
I don't think using sockets is that uncommon.