Fixing Apache Guacamole’s PostgreSQL database error after upgrading to 1.6

Recently upgraded my Apache Guacamole and was surprised with the following error:

guacamole-1  | 17:58:15.547 [http-nio-8080-exec-5] WARN  o.a.g.e.AuthenticationProviderFacade - The "postgresql" authentication provider has encountered an internal error which will halt the authentication process. If this is unexpected or you are the developer of this authentication provider, you may wish to enable debug-level logging. If this is expected and you wish to ignore such failures in the future, please set "skip-if-unavailable: postgresql" within your guacamole.properties.
guacamole-1  | 17:58:15.554 [http-nio-8080-exec-5] ERROR o.a.g.rest.RESTExceptionMapper - Unexpected internal error: 
guacamole-1  | ### Error querying database.  Cause: org.postgresql.util.PSQLException: The server requested SCRAM-based authentication, but the password is an empty string.
guacamole-1  | ### The error may exist in org/apache/guacamole/auth/jdbc/user/UserMapper.xml
guacamole-1  | ### The error may involve org.apache.guacamole.auth.jdbc.user.UserMapper.selectOne
guacamole-1  | ### The error occurred while executing a query
guacamole-1  | ### Cause: org.postgresql.util.PSQLException: The server requested SCRAM-based authentication, but the password is an empty string.

Navigating to Apache Guacamole, it also only displayed an error:

Turns out, it is actually pretty easy to fix. I don’t know why, but I used environment variables starting with POSTGRES_ instead of the official POSTGRESQL_ for the guacamole/guacamole container.

Changing these variables worked:

Old value	New value
POSTGRES_DATABASE	POSTGRESQL_DATABASE
POSTGRES_HOSTNAME	POSTGRESQL_HOSTNAME
POSTGRES_PASSWORD	POSTGRESQL_PASSWORD
POSTGRES_USER	POSTGRESQL_USER

Stop your Guacamole instance and start it again. If everything works as expected now, you’re good to go.

If that doesn’t work…

Turns out that Apache Guacamole might not execute a needed database upgrade query automatically.
See the docs for version 1.6 at https://guacamole.apache.org/doc/1.6.0/gug/postgresql-auth.html#upgrading-an-existing-guacamole-database:

• Download guacamole-auth-jdbc-1.6.0.tar.gz from https://guacamole.apache.org/releases/1.6.0
• Open the file with your favorite archive manager and grab hold of postgresql/schema/upgrade/pre-1.6.0.sql
• Execute the statements in the file

In my case, I was running the needed PostgreSQL database in the same docker-compose.yml file, so I logged in on my PostgreSQL server:

docker compose exec -it postgres psql -U guacamole -W guacamole

If prompted, enter the password for the database – should be defined in your compose file too.
Execute the statement:

ALTER TYPE guacamole_system_permission_type
    ADD VALUE 'AUDIT'
    BEFORE 'ADMINISTER';

Press CTRL + D to exit. Restart your Apache Guacamole instance:

docker compose restart

Thanks to @jimsgarage_ on X/Twitter for pointing this out in https://x.com/jimsgarage_/status/1937878663900324095!