gitea does not start - can not find public schema

[volker-raschek]

• Gitea version: 1.5.2 (docker)
• Git version: 2.19.1
• Operating system: Debian
• Database (use [x]):
  • PostgreSQL
• Can you reproduce the bug at https://try.gitea.io:
  • Not relevant

Hello, I have multiple instances for gitea. Every installation should use one big postgres database with his own schema.

So I create for one instance his own schema and username and changed the search_path to disable querying statements to the public schema, because I don't want, that any application or instance create his schema into public.

Here my psql statements

CREATE SCHEMA gitea;
CREATE ROLE gitea WITH LOGIN;
ALTER USE gitea SET search_path=gitea;
GRANT ALL ON SCHEMA gitea to gitea;

After installation gitea does not start and I get in my logs every time the same error.

root@vgttp:/srv/docker/gitea_01/data/gitea/log# tailf xorm.log
2018/10/23 20:37:33 [I] PING DATABASE postgres
2018/10/23 20:37:33 [I] [SQL] SELECT tablename FROM pg_tables WHERE schemaname = $1 AND tablename = $2 []interface {}{"public", "version"}
2018/10/23 20:37:33 [I] [SQL] CREATE TABLE IF NOT EXISTS "version" ("id" BIGSERIAL PRIMARY KEY  NOT NULL, "version" BIGINT NULL)
2018/10/23 20:37:33 [I] [SQL] SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = $1 AND table_name = $2 AND column_name = $3 [public version id]
2018/10/23 20:37:33 [I] [SQL] ALTER TABLE "version" ADD "id" BIGSERIAL PRIMARY KEY  NOT NULL ;

Gitea look in the public schema? I have installed the instance into his own schema! How can I change in my docker-compose or app.ini the schema name?

Volker

[lunny]

#3348 tried to resolve the problem.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs during the next 2 weeks. Thank you for your contributions.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs during the next 2 weeks. Thank you for your contributions.

[Rizzen59]

There is the issue with gitea 1.8.3 and 1.9.0-rc2 when default schema for the user is different to public on postgreSQL 11

Need to change the check of table "version" to specify the db user schema

SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = $1 AND table_name = $2 AND column_name = $3 [public version id]

[volker-raschek]

Hello everybody, is there any news about the topic yet?

We would like to roll out gitea in our company and don't want to start a separate postgres database for each instance just because the schema can't be changed.

Volker

[guillep2k]

Can this help?
https://www.mkyong.com/database/postgresql-how-to-change-default-schema/
https://www.postgresql.org/message-id/200307222318.58234.barwick@gmx.net

[volker-raschek]

Yes it helps, but only for the installation process. After the installation is in some sql queries the schema hardcoded to public. See @Rizzen59 post. I issue is also present in version 1.9.4.

SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = $1 AND table_name = $2 AND column_name = $3 [public version id]

[guillep2k]

Well, that's good to know. The current PR that attempted to solve this was trying the hard way by modifying all queries, when in fact it could be easier.
@lunny, it looks like it's only a couple of places in xorm that this query is executed? In combination with:

SET search_path = new_schema

it looks like this would be solved, but I'm no PostgreSQL expert.