Uploaded image for project: 'Jira Data Center'
  1. Jira Data Center
  2. JRASERVER-76429

Connecting Jira to Postgres document do not work for Postgres 15

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Fixed
    • Icon: High High
    • None
    • 9.4.0, 9.11.2
    • Documentation - All
    • None

      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

      1. Install Jira 9.11
      2. 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:

      1. Create the user that Jira will be using:
        postgres=# CREATE USER atlas WITH PASSWORD 'atlas';
      2. Create the database:
        postgres=# CREATE DATABASE atlas WITH ENCODING 'UNICODE' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;
      3. Grant the necessary privileges to the database
        postgres=# GRANT ALL PRIVILEGES ON DATABASE atlas TO atlas;
      4. Connect to the database:
        postgres=# \c atlas postgres
        You are now connected to database "atlas" as user "postgres".
      5. Grant the required schema privileges:
        atlas=# GRANT ALL ON SCHEMA public TO atlas;

              94e733e73fd4 Robert Klimkiewicz
              489dcb58d5d9 Edson Araujo
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Created:
                Updated:
                Resolved: