Gitea can't authenticate against Postgres 13

[stop5]

Gitea Version

1.17.2

Git Version

2.30.2

Operating System

Debian GNU/Linux Bullseye (11)

How are you running Gitea?

currently with sudo manually.
but it is indipendent from the running method.

Database

PostgreSQL

Can you reproduce the bug on the Gitea demo site?

No

Log Gist

https://gist.github.com/stop5/ccfbc4eed19bfa4dfc3754c234a55cac

Description

I have set up gitea and the database as the description.
But the Authentication fails every time.
The authentication with the postgres client runs without problems. The authentication that i recorded using tcpdump also looks ok.

Screenshots

No response

[zeripath]

How are you connecting to postgres both as the postgres client and for Gitea?

Do not assume that just because psql gitea gitea works that the gitea user is allowed to connect to postgres over tcp. psql will default to using the unix socket - which users often have different permissions for.

Gitea can connect over the unix socket - just set [database] HOST to the socket path.

[stop5]

My Postgres DB runs on an other server. For now i am not using an encrypted connection, so that i can see problems in tcpdump/wireshark.
psql 'sslmode=disable dbname=gitea user=gitea host=10.0.0.1 password=PASSWORD'
This is the databasepart of my config:

[database]
DB_TYPE  = postgres
HOST     = 10.0.0.1:5432
NAME     = gitea
USER     = gitea
PASSWORD = PASSWORD
SSL_MODE = disable
DB_RETRIES = 1

[zeripath]

Are there any special characters in your password?

[stop5]

no, it has only caracters from A-Z, a-z and 0-9.

[stop5]

and it fails even when i change the password to AAAAAAAA.

[zeripath]

I literally have no idea what could be causing this except for some authentication/configuration issue on behalf of postgres which is not allowing the gitea process to connect.

We convert the information to a connection url using:

gitea/modules/setting/database.go

Lines 166 to 168 in ff9a8a2

	connStr = fmt.Sprintf("postgres://%s:%s@%s:%s/%s%ssslmode=%s",
	url.PathEscape(dbUser), url.PathEscape(dbPasswd), host, port, dbName, dbParam, dbsslMode)
	}

e.g. in your case this should be:

postgres://gitea:<PASSWORD>@10.0.0.1:5432/gitea?sslmode=disable 

Perhaps some additional parameters are needed? If you change the name from gitea to gitea?paramName=paramValue the addtional parameters will be set. IIRC there's another issue where people needed an additional parameter for some replication/HA configuration - but they were getting past the authentication issue.

I guess double-checking if the above configuration url works on psql would be helpful. Ensure that you're running psql from the same place that gitea is running too in case it's some firewall/IP issue.

[stop5]

i ran psql 'postgres://gitea:[email protected]:5432/gitea?sslmode=disable' and it connected without problems.

[zeripath]

Does postgres give us any more information as to why it is rejecting gitea's connection?

[zeripath]

Actually it does:
2021-10-10 11:59:53.132 UTC [1114730] FATAL: Passwort-Authentifizierung für Benutzer »gitea« fehlgeschlagen
2021-10-10 11:59:53.132 UTC [1114730] DETAIL: Verbindung stimmte mit pg_hba.conf-Zeile 4 überein: »hostnossl all gitea 10.0.0.0/8 scram-sha-256«

[zeripath]

scram-sha-256

I am willing to bet is not supported by the pq driver.

Switch that to md5

[stop5]

I set it up like the docs said: https://docs.gitea.io/en-us/database-prep/#postgresql
They say that it must be scram-sha-256.

[stop5]

i tested it and nothing has changed

[zeripath]

OK I've just docker pull postgres to get the postgres 14 and 13 and Gitea can authorize against that. (some fiddling with postgresql.conf was required to get SCRAM secrets to be generated.)

I think this has to be a configuration issue.

Try setting your host to all and work back from there.

[stop5]

@zeripath Thanks for the test using the official package.
I made a test run using the debian:stable container.
In my example i am using podman, but it also works with docker.
podman run --rm -ti -v ./gitea:/gitea -v ./local_app.ini:/local.ini -v ./remote_app.ini:/remote.ini -v ./run_test.sh:/test.sh docker.io/debian:stable-slim /test.sh
gitea is the binary that is downloaded from dl.gitea.io
local_app.ini, remote_app.ini and run_test.sh are files that i included in my updated gist.
when i run the command above, it install postgres, git and sudo. it modifies the postgres config to get the scram-sha256 auth method, creates the gitea user in the system and in the db, creates the custom dir and finally start gitea. After it started the same thing happens there as on my servers. i added a small error control that starts a bash in this case.
I tested this also with debian unstable (just adding "un" before stable and replacing the postgres version 13 with 14) and the same happens.

[stop5]

I made an small example program that uses the golang pq, pgx and xorm lib to connect to my database.
It connected without problems and could even run an query successfully. i think we can ignore the underlying libs. in case of problems. the low level lib and xorm are even able to use the authentication with ldap.
So somewhere there is something that makes the problem.

[theAkito]

Same problem with Gitea 1.15.10 and a legacy PostgreSQL 11 database.

I am in the process of migrating from a generic local installation to a Kubernetes installation of Gitea and I want to keep my old database.

I experience the same problems, as described here. I can connect perfectly fine on the CLI with psql, etc. I can do everything. However, when the Gitea configuration container tries to authenticate with PostgreSQL 11, it fails with an authentication failure regarding the password.

I excluded common errors and human mistakes by repeating the restoration process in different ways and I was really careful to make it as working as possible. Still, to no avail.

I might try a different Gitea version, to see if it continues to happen then. My legacy Gitea installation is based on a development release of 1.13.

[theAkito]

I solved my issue by making the Postgres part of the migration process an extremely hughmongous pain in the ass, i.e. doing everything step by step, by hand only. This was an extreme pain.

However, I got it to work that way, since I basically used the newly generated database to import the old data, then re-deploy this modified database onto the new Gitea instance, etc.........

This was such a pain. It would've helped, if it just worked out of the box.

[lunny]

Looks like lib/pq#833 has been merged and this is not a problem anymore?

[wxiaoguang]

Gitea now is using lib/pq v1.10.7