SQL-error during import of data from production

[Rune Hellem]

About

During dump of data from prod to test in JIRA failes. JIRA won't start due to errors found in the log such as 

2022-06-14 08:31:51,601+0200 JIRA-Bootstrap ERROR      [c.a.jira.startup.LauncherContextListener] Unable to start JIRA.
com.atlassian.jira.exception.DataAccessException: org.postgresql.util.PSQLException: ERROR: permission denied for relation moved_issue_key
	at com.atlassian.jira.startup.DatabaseLauncher.cleanupDatabaseTableName(DatabaseLauncher.java:254)
	at com.atlassian.jira.startup.DatabaseLauncher.cleanupDatabaseTableNames(DatabaseLauncher.java:210)
	at com.atlassian.jira.startup.DatabaseLauncher.start(DatabaseLauncher.java:73)
	at com.atlassian.jira.startup.DefaultJiraLauncher.lambda$postDbLaunch$2(DefaultJiraLauncher.java:139)
	at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrEnqueue(DatabaseConfigurationManagerImpl.java:307)
	at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrWhenDatabaseActivated(DatabaseConfigurationManagerImpl.java:202)
	at com.atlassian.jira.startup.DefaultJiraLauncher.postDbLaunch(DefaultJiraLauncher.java:135)
	at com.atlassian.jira.startup.DefaultJiraLauncher.lambda$start$0(DefaultJiraLauncher.java:102)
	at com.atlassian.jira.util.devspeed.JiraDevSpeedTimer.run(JiraDevSpeedTimer.java:31)
	at com.atlassian.jira.startup.DefaultJiraLauncher.start(DefaultJiraLauncher.java:100)
	at com.atlassian.jira.startup.LauncherContextListener.initSlowStuff(LauncherContextListener.java:154)
	at java.base/java.lang.Thread.run(Unknown Source)
Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for relation moved_issue_key

Versions

Production is currently hosted on Win 2012, but we are about to migrate to Win 2019. Therefore we need the dump/import of data to be working without issues. 

JIRA

8.20.2#820002-sha1:829506d

Postgres

PS C:\Windows\system32> psql --version
psql (PostgreSQL) 10.19

Export

On the production server the export is done using command

d:\postgresql\bin\pg_dump.exe  -U postgres -d jiradb --format=c --file=e:\backups\dbs\jiradb_20220614_020008.pgdump

Import

Import script has done the following preparations before import

DROP DATABASE jiradb;
CREATE DATABASE jiradb;

grant all privileges on database jiradb to jira_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO jira_user;

On the test instance the data is being imported using command 

d:\postgresql\bin\pg_restore.exe -U postgres -d jiradb --no-privileges --no-owner -v -1 d:\temp\latest-backup.pgdump

Errors being logged

JIRA won't start, 5 errors are being logged, in brief

2022-06-14 08:51:18,645+0200 localhost-startStop-2 ERROR [o.a.c.c.C.[Catalina].[localhost].[/]] Exception sending context destroyed event to listener instance of class [com.atlassian.jira.startup.LauncherContextListener]
... 130 lines omitted ...
... 98 more
Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for relation propertyentry
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284)
Show all 143 lines
2022-06-14 08:53:44,979+0200 JIRA-Bootstrap ERROR [c.a.jira.startup.ComponentContainerLauncher] A fatal error occurred during initialisation. JIRA has been locked.
... 151 lines omitted ...
... 117 more
Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for relation propertyentry
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284)
Show all 164 lines
2022-06-14 08:53:45,042+0200 JIRA-Bootstrap ERROR [c.a.jira.health.HealthChecks] We couldn't start JIRA
2022-06-14 08:53:45,042+0200 JIRA-Bootstrap ERROR [c.a.jira.health.HealthChecks] An error occurred while trying to start JIRA. We can't give you any more detail right now, we suggest checking the logs for more detail and contacting our support team.
See our documentation for more information on contacting our support team and creating a support zip.
2022-06-14 08:53:45,261+0200 JIRA-Bootstrap ERROR [c.a.jira.startup.LauncherContextListener] Unable to start JIRA.
com.atlassian.jira.exception.DataAccessException: org.postgresql.util.PSQLException: ERROR: permission denied for relation moved_issue_key
at com.atlassian.jira.startup.DatabaseLauncher.cleanupDatabaseTableName(DatabaseLauncher.java:254)
... 11 lines omitted ...
Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for relation moved_issue_key
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
Show all 27 lines

[Rune Hellem · Accepted solution]

The below command resolved the issue for me, got help from a colleague of mine. 

d:\postgresql\bin\pg_restore.exe --username jirs_user -d jiradb -O -W --format=c d:\temp\latest-backup.pgdump

Beats me, but it works

[Nic Brough -Adaptavist-]

It's pretty clear that this is a permissions issue in the database.

Could you try re-running

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO jira_user;

after the import (and also check that Jira is definitely using jira_user for access - look at Jira-home/dbconfig.xml 

[Rune Hellem]

It is without doubt my DBA skills that is not up to date

C:\Windows\system32>psql -U postgres -c "grant all privileges on database jiradb to jira_user;"
GRANT

C:\Windows\system32>psql -U postgres -c "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO jira_user;"
GRANT

C:\Windows\system32>psql -U postgres -c "GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO jira_user;"
GRANT

C:\Windows\system32>psql -U postgres -c "GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO jira_user;"
GRANT

C:\Windows\system32>psql -U jira_user -d jiradb -c "select O_S_PROPERTY_ENTRY.property_key, O_S_PROPERTY_ENTRY.propertytype from public.propertyentry O_S_PROPERTY_ENTRY"
ERROR: permission denied for relation propertyentry