PostgreSQL 15 with Jira 9.10/9.11 "ERROR: permission denied for schema public at character ..."

Jira Development Jira Data Center
1

Hi,

I am testing my app against all supported PostgreSQL versions from 11-14 in a dockerized setup.

Now I wanted to add Postgres 15 with the exact same procedure but I am getting these DB-errors on startup of Jira.

ERROR:  permission denied for schema public at character ...

I have seen, there is no update on the docs for anything new on Postgres 15 here:

I am using this seed SQL since many years:

jira-seed.sql

CREATE DATABASE jira WITH ENCODING 'UNICODE' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;
CREATE USER jira WITH ENCRYPTED PASSWORD 'supersecure123';
GRANT ALL PRIVILEGES ON DATABASE jira TO jira;

And for the docker container I set these env vars:

# my local ip is: 192.168.178.73

# START DB
docker run --name jira911dbvanilla -d \
     -p 5432:5432 \
     -e POSTGRES_USER=root \
     -e POSTGRES_PASSWORD=supersecure123root \
     postgres:15.4-alpine

# SEED (pw: supersecure123root)
psql -h 192.168.178.73 -p 5432 -U root -f ./jira-seed.sql

# START JIRA
docker run --name jira911vanilla -d \
     -p 8080:8080 \
     -e JVM_MINIMUM_MEMORY=512M \
     -e JVM_MAXIMUM_MEMORY=2048M \
     -e ATL_JDBC_URL="jdbc:postgresql://192.168.178.73:5432/jira" \
     -e ATL_JDBC_USER=jira \
     -e ATL_JDBC_PASSWORD=supersecure123 \
     -e ATL_DB_DRIVER=org.postgresql.Driver \
     -e ATL_DB_TYPE=postgres72 \
     -e CLUSTERED=false \
     atlassian/jira-software:9.11-jdk11

What am I doing wrong? This has worked since postgres 11 up to 14.

Thanks

Bernhard

2

Cause:

Solution (use at own risk)

To solve this the suggested GRANT ALL ON SCHEMA public TO jira; did not work for me.
I also needed to change the database owner too. So here is the full seed.sql that is working with postrares 15+

CREATE USER jira WITH ENCRYPTED PASSWORD 'supersecure123';
-- since postgres 15 (before db creation)
GRANT ALL ON SCHEMA public TO jira;
-- now create objects
CREATE DATABASE jira WITH ENCODING 'UNICODE' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;
GRANT ALL PRIVILEGES ON DATABASE jira TO jira;
-- also needed for postgres 15
ALTER DATABASE jira OWNER TO jira;
3

You sir are awesome. Ran into this same problem with Confluence on AWS Aurora Postgres 15. Granting all on schema public and alter database owner did the trick for me.

1 Like
4

@clouless what is really strange is that this PostgreSQL 15-related table creation permission change was documented back in Jira 9.4, but PostgreSQL support for Jira only became official in Jira 9.10!!

FYI, we’ve submitted a support ticket with Atlassian (BSP-58295) asking them to kindly update their documentation to surface this particular detail.

1 Like