-
Bug
-
Resolution: Fixed
-
High
-
None
-
9.4.0, 9.11.2
-
None
-
9.04
-
1
-
Severity 2 - Major
-
Issue Summary
PostgreSQL 15 revokes the CREATE permission from all users except a database owner from the public (or default) schema. Due to that, if you follow the exact steps from Connecting Jira applications to PostgreSQL to connect Jira 9.11 to a Postgres 15 database, Jira won't be able to create the tables in the database.
This is reproducible on Data Center: Yes
Steps to Reproduce
- Install Jira 9.11
- Follow the exact steps from Connecting Jira applications to PostgreSQL to connect Jira 9.11 to Postgres 15
Expected Results
Jira is able to connect to the database and create the required tables.
Actual Results
Errors like the following can be seen in the atlassian-jira.log for every table Jira tries to create:
2023-10-24 15:27:07,385-0300 http-nio-8080-exec-7 ERROR anonymous 927x212x1 919nhf 0:0:0:0:0:0:0:1 /secure/SetupDatabase.jspa [o.o.c.entity.jdbc.DatabaseUtil] SQL Exception while executing the following: CREATE TABLE public.upgradetaskhistory (ID NUMERIC(18,0) NOT NULL, UPGRADE_TASK_FACTORY_KEY VARCHAR(255), BUILD_NUMBER NUMERIC(9,0), STATUS VARCHAR(60), UPGRADE_TYPE VARCHAR(10), CONSTRAINT PK_upgradetaskhistory PRIMARY KEY (ID)) Error was: org.postgresql.util.PSQLException: ERROR: permission denied for schema public Position: 14
In the Jira UI, a 500 error page is displayed.
Workaround
According to Postgres 15 documentation, the best would be to create a user-private schema for the Jira database user.
However, the following can be used to grant the required permissions to the user within the public schema:
- Create the user that Jira will be using:
postgres=# CREATE USER atlas WITH PASSWORD 'atlas';
- Create the database:
postgres=# CREATE DATABASE atlas WITH ENCODING 'UNICODE' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;
- Grant the necessary privileges to the database
postgres=# GRANT ALL PRIVILEGES ON DATABASE atlas TO atlas;
- Connect to the database:
postgres=# \c atlas postgres You are now connected to database "atlas" as user "postgres".
- Grant the required schema privileges:
atlas=# GRANT ALL ON SCHEMA public TO atlas;