Using a PostgreSQL schema different from "public" causes error 500

[negora]

• Gitea version (or commit ref): 1.13.0
• Git version: 2.20.1
• Operating system: Debian GNU/Linux 10.7 (Buster)
• Database (use [x]):
  • [×] PostgreSQL
  • MySQL
  • MSSQL
  • SQLite
• Can you reproduce the bug at https://try.gitea.io:
  • Yes (provide example URL)
  • [×] No
• Log gist:

Description

I'm trying to install Gitea using a PostgreSQL database. I've chosen a schema different from public, one called main. Then, after the installation finishes seemingly fine, the application takes me to a screen that shows a big error 500.

If I click on Dashboard, I still get the same error. In general, the same occurs with the following buttons:

• Dashboard
• Issues
• Pull Requests
• Milestones

The button Explore seems to work fine, though.

If I navigate through the options of Profile and Settings... the results are similar: some sections work, others don't.

By checking the logs, I've came to the conclusion that some tables are not found when using a custom schema:

Dec 21 09:10:08 my-server gitea[9701]: [Macaron] 2020-12-21 09:10:08: Completed GET /user/events 200 OK in 5.065552253s
Dec 21 09:10:08 my-server gitea[9701]: [Macaron] 2020-12-21 09:10:08: Started GET /issues for 192.168.6.196
Dec 21 09:10:08 my-server gitea[9701]: 2020/12/21 09:10:08 routers/user/home.go:60:getDashboardContextUser() [E] GetOrganizations: pq: relation "repository" does not exist
Dec 21 09:10:08 my-server gitea[9701]: [Macaron] 2020-12-21 09:10:08: Completed GET /issues 500 Internal Server Error in 5.477482ms
Dec 21 09:10:08 my-server gitea[9701]: [Macaron] 2020-12-21 09:10:08: Started GET /user/avatar/administrator/-1 for 192.168.6.196
Dec 21 09:10:08 my-server gitea[9701]: [Macaron] 2020-12-21 09:10:08: Completed GET /user/avatar/administrator/-1 302 Found in 2.823642ms
Dec 21 09:10:08 my-server gitea[9701]: [Macaron] 2020-12-21 09:10:08: Started GET /user/events for 192.168.6.196

If I use the public schema, everything seems to work fine.

[lunny]

Have you tried to restart the Gitea after you finished the installation?

[negora]

@lunny Thank you for your prompt answer.

I've just made a fresh installation, I've restarted the service (a Systemd service) right after the install, and the result is that the dashboard loads fine, but the other sections still throw the same error 500: Issues, Pull Requests, Milestones... Checking the log, the source of the error is the same: the application isn't able to find certain tables:

Dec 22 08:19:28 my-server gitea[7397]: 2020/12/22 08:19:28 routers/user/home.go:60:getDashboardContextUser() [E] GetOrganizations: pq: relation "repository" does not exist

[lunny]

@negora Then could you check your custom/conf/app.ini in [database], if the schema is correct?

[negora]

@lunny Yes, it's set correctly:

[database]
DB_TYPE  = postgres
HOST     = 127.0.0.1:5432
NAME     = gitea
USER     = gitea
PASSWD   = `<undisclosed>`
SCHEMA   = main
SSL_MODE = disable
CHARSET  = utf8
PATH     = /var/lib/gitea/data/gitea.db
LOG_SQL  = false

[negora]

By the way, my configuration file is located at /etc/gitea/app.ini, instead of /var/lib/gitea/custom/conf/app.ini . Just in case that it's of any help.

[zeripath]

You need to go into Postgres directly and tell us what schema each of the tables is in.

[zeripath]

As you will see from: 1e75afc

the schema search path should be being set in such a way that this cannot happen - so we need to figure out why this happened.

[negora]

@zeripath Thank you for joining to the conversation.

You need to go into Postgres directly and tell us what schema each of the tables is in.

The tables are successfully created in the main schema. So that part works OK.

As you will see from: #1e75afc , the schema search path should be being set in such a way that this cannot happen - so we need to figure out why this happened.

I've logged, from the database-side, all queries emitted by Gitea. The query mentioned in 1e75afc is run only once, just during the boot of the application. Indeed, it's the first query being executed. So that should work, I agree.

But I wonder: Does it mean that you use only 1 single connection to the database, for the whole application? Because, if you're using more than one session/connection, Shouldn't that query be run at least once per session/connection?

[zeripath]

But I wonder: Does it mean that you use only 1 single connection to the database, for the whole application? Because, if you're using more than one session/connection, Shouldn't that query be run at least once per session/connection?

Interesting! I hoped that the set_config would have been carried over connections - but I hadn't considered that it might not.

Right time for a workaround you can change the default search path for the user.

Unfortunately I suspect that the only solution is going to involve hijacking the sql.DB Connector through registering our own postgres driver and calling that query on connection. sigh

[negora]

Interesting! I hoped that the set_config would have been carried over connections - but I hadn't considered that it might not.

Yes, both SET and SELECT set_config() only work at the level of the current session/connection. Unfortunately, if you use more than one connection, you've to set that option in each one.

Right time for a workaround you can change the default search path for the user.

I will use that. Another option is to set the search_path at the database level. I used that for my projects in the past. That way, all users allowed to connect to the database took advantage of that setting.

Unfortunately I suspect that the only solution is going to involve hijacking the sql.DB Connector through registering our own postgres driver and calling that query on connection. sigh

Just out of curiosity, Is there any reason why you don't use the schema name in the own queries? Example:

SELECT * FROM main.notifications;

Is it because the queries are created automatically by an ORM framework?

Thank you.

[zeripath]

Most queries that come out of Xorm will have schema prefixed automatically but there are quite a few cases where we have (sometime partially) handwritten SQL which won't have the schema prefixed - simply because it's going to be bloody awkward and make the code terribly unreadable to add it. (Basically we'd have to always get Xorm to tell us the table name instead of us just being aware of it.)

It should be possible to set the search_path appropriately - we shouldn't have to write schema aware SQL in general.

[negora]

Most queries that come out of Xorm will have schema prefixed automatically...

I see. In that case, not adding the schema in every query is reasonable.

I'll keep an eye on that pull request. Thanks ;-) .