Gitea & postgres with docker compose - sync: pq: permission denied for schema public

Install/Maintain/Configure
1

I’m trying to set up gitea with a postgres DB on my local Synology server using docker. I get the following error message during the initial installation of gitea:

The database settings are invalid: migrate: sync: pq: permission denied for schema public

Here is what I did:

  • Set up Postgres in a seperate docker-compose stack since I want to have multiple DBs in the instance
  • created a database for gitea
  • created a docker-compose.yml for gitea (see below)
  • connected to the web interface and tried to initialize gitea, running into the above problem

I tried to see if the database connection is the problem, but that seems ok. To verify I ran a shell in the gitea container, installed the postgres client and connected to the database:

sudo docker exec -it gitea /bin/sh
/ # apk --update add postgresql-client
/ # psql -h postgres -d gitea -U gitea
Password for user gitea:
psql (14.5, server 15.1)
WARNING: psql major version 14, server major version 15.
         Some psql features might not work.
Type "help" for help.

gitea=> \dn
      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner
(1 row)

So it looks like I can connect to the database from the gitea container, credentials are correct and the public schema is present as well.

Any hints how to fix this problem?

Here is the docker-compose.yml that I use for gitea:

version: "3"

networks:
  postgresql_internal:
    external: true

services:
  server:
    image: gitea/gitea:1.17.4
    container_name: gitea
    environment:
      - USER_UID=1000
      - USER_GID=1000
      - GITEA__database__DB_TYPE=postgres
      - GITEA__database__HOST=postgres:5432
      - GITEA__database__NAME=gitea
      - GITEA__database__USER=gitea
      - GITEA__database__PASSWD=secret
    restart: always
    networks:
      - postgresql_internal
    volumes:
      - /volume1/docker/gitea/data:/data
#      - /etc/timezone:/etc/timezone:ro
      - /etc/localtime:/etc/localtime:ro
    ports:
      - "11080:3000"
      - "11022:22"
2

According to your shell output from Postgres, Gitea does not have ownership of the database so does not have permission to create tables/insert data/etc. Could you try running ALTER DATABASE gitea OWNER TO gitea using an admin account? See this guide for more info about using Gitea with Postgres.

2 Likes
3

Jake, that did the trick, tyvm.
I thought ‘grant all privileges on database gitea to gitea;’ was sufficient, apparently not.

1 Like