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/[email protected]

[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.

[lunny]

@appleboy's PR #3348 will try to fix this.

[guillep2k]

@appleboy's PR #3348 will try to fix this.

@lunny The solution is much much simpler 😁. I've just tested it locally. The problem is that xorm is forcing the public schema in some queries.

What I did:

• Started from an existing Gitea database.
• Created a new schema (notpublic).
• Moved all tables to the new schema (alter table access set schema notpublic;, etc.)
• Ran ALTER USER gitea SET search_path = notpublic;.
• Edited vendor/xorm.io/xorm/dialect_postgres.go, changed DefaultPostgresSchema and postgresPublicSchema to notpublic.
• Compiled gitea.
• Run (works).

IMHO #3348 is trying to change all queries unnecessarily, and xorm almost handles this. There are only a few problems:

• DefaultPostgresSchema is initialized to "public" instead of postgresPublicSchema, so no compilation customization (-X) can be used.
• Engine.tbNameWithSchema() uses postgresPublicSchema instead of db.Schema, so the two values are not synchronized.
• Gitea has no setting to pass a customized schema name into xorm.

If you agree with my analysis, I could pass a PR to xorm for the first two.

[lunny]

@guillep2k Good catch! And if you can add a new database test with non-public schema of postgres, that's better. :)

[guillep2k]

@lunny It turns out xorm already handled the case correctly; only Gitea didn't use engine.SetSchema() to specify a different one. PR'd at #8819 (no need for weird ALTER user statements 😁)

[guillep2k]

To be rigurous, xorm could run and parse a SHOW search_path to see if the user has public as their schema or a different one, but that's very tricky because search_path accepts several schemas in a comma-separated list. It's much more reasonable to assume the default and let the user select one explicitly. 😊

[volker-raschek]

Hello,
today i tried to install a new environment with postgres as backend but it seems that gitea still uses the public schema again. It is probably hard coded somewhere.

I used the container image 1.11.4. The following configuration of the database section is used.

[database]
DB_TYPE  = postgres
HOST     = postgres:5432
NAME     = postgres
SCHEMA   = gitea
USER     = gitea
PASSWD   = <replaced>
SSL_MODE = disable
CHARSET  = utf8

I receive the following log messaged dunring the startup.

2020/04/16 18:49:28 routers/init.go:66:initDBEngine() [I] ORM engine initialization attempt #6/10...
2020/04/16 18:49:28 ...rm.io/xorm/engine.go:351:Ping() [I] PING DATABASE postgres
2020/04/16 18:49:28 .../xorm/session_raw.go:78:queryRows() [I] [SQL] SELECT tablename FROM pg_tables WHERE schemaname = $1 AND tablename = $2 []interface {}{"public", "version"} - took: 13.683319ms
2020/04/16 18:49:28 .../xorm/session_raw.go:196:exec() [I] [SQL] CREATE TABLE IF NOT EXISTS "version" ("id" BIGSERIAL PRIMARY KEY  NOT NULL, "version" BIGINT NULL) - took: 1.731081ms
2020/04/16 18:49:28 .../dialect_postgres.go:942:IsColumnExist() [I] [SQL] SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = $1 AND table_name = $2 AND column_name = $3 [public version id]
2020/04/16 18:49:28 .../xorm/session_raw.go:196:exec() [I] [SQL] ALTER TABLE "version" ADD "id" BIGSERIAL PRIMARY KEY  NOT NULL ; - took: 7.201686ms
2020/04/16 18:49:28 routers/init.go:72:initDBEngine() [E] ORM engine initialization attempt #6/10 failed. Error: migrate: sync: pq: column "id" of relation "version" already exists
2020/04/16 18:49:28 routers/init.go:73:initDBEngine() [I] Backing off for 3 seconds

I hope anyone can fix the issue and you cloud implement a test in the future so this won't happen again.

Many greetings and stay healthy
Volker

[jolheiser]

Database schema for postgres will be supported it 1.12. If you have further issues, please open a new ticket to track it. 🙂

[volker-raschek]

Ohhh, thanks a lot for this information.
So I will wait until 1.12 is released.

Thanks 😃