"permission denied for schema public" error after upgrading Jira
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Database permission issues observed after upgrading the Jira environment.
Environment
Jira DC
Diagnosis
- The Jira Software startup shows database-related errors.
- In atlassian-jira.log, "PSQLException: ERROR: permission denied for schema public" error is observed multiple times.
2024-12-10 17:26:04,342+0530 JIRA-Bootstrap ERROR [o.o.c.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
CREATE TABLE public.cwd_application_attribute (application_id NUMERIC(18,0) NOT NULL, attribute_name VARCHAR(255) NOT NULL, attribute_value TEXT, CONSTRAINT PK_cwd_application_attribute PRIMARY KEY (application_id, attribute_name))
Error was: org.postgresql.util.PSQLException: ERROR: permission denied for schema public
Position: 142024-12-10 17:26:05,245+0530 JIRA-Bootstrap ERROR [o.o.c.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
CREATE INDEX issue_archived ON public.jiraissue (ARCHIVED)
Error was: org.postgresql.util.PSQLException: ERROR: permission denied for schema public
2024-12-10 17:26:05,245+0530 JIRA-Bootstrap ERROR [o.o.c.entity.jdbc.DatabaseUtil] Could not create missing indices for entity "CustomFieldValue"
2024-12-10 17:26:05,245+0530 JIRA-Bootstrap ERROR [o.o.c.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
CREATE INDEX customfield_explicit_idx ON public.customfieldvalue (CUSTOMFIELD)
Error was: org.postgresql.util.PSQLException: ERROR: permission denied for schema public
2024-12-10 17:26:05,246+0530 JIRA-Bootstrap ERROR [o.o.c.entity.jdbc.DatabaseUtil] Could not create missing indices for entity "ProjectRoleActor"
2024-12-10 17:26:05,246+0530 JIRA-Bootstrap ERROR [o.o.c.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
CREATE INDEX role_player_type_idx ON public.projectroleactor (ROLETYPEPARAMETER, ROLETYPE)
Error was: org.postgresql.util.PSQLException: ERROR: permission denied for schema public- Verify the Jira database information and compare it with the username mentioned in dbconfig.xml file. For Example,PostgreSQL
\l command will list all the databases (and their relevant information)MySQLSHOW DATABASES; will list all the databases (and their relevant information)Microsoft SQL Server (MSSQL)SELECT name FROM sys.databases; will list all the databases (and their relevant information) - Compare the Jira database owner with the one present on dbconfig.xml. Sample dbconfig.xml output:
<?xml version="1.0" encoding="UTF-8"?><schema-name>public</schema-name>
<jdbc-datasource>
<url>jdbc:postgresql://jirajira:xxxx/jira</url>
<driver-class>org.postgresql.Driver</driver-class>
<username>dbjira</username>
- Jira username will be different from the database owner
Cause
PostgreSQL 15 revokes the CREATE permission from all users except a database owner from the public (or default) schema. Therefore, Jira 10 is not able to create any further tables using the specified user in dbconfig.xml
Solution
Update the owner of the Jira database to the one specified on dbconfig.xml using:
ALTER DATABASE jira OWNER TO dbjira; Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.