Importing database on postgres - Permission denied on Bandana
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
Problem
When try to import database dump on PostgreSQL you face the following error message:
CREATE TABLE
ERROR: role "confluence" does not exist
CREATE SEQUENCE
ERROR: role "confluence" does not exist
ALTER SEQUENCE
CREATE TABLE
ERROR: role "confluence" does not existAnd on atlassian-confluence.log you will get the following error message:
2014-12-11 11:12:40,919 ERROR [localhost-startStop-1] [ContainerBase.[Standalone].[localhost].[/]] log Exception starting filter jmx
org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: Could not execute query; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: permission denied for relation bandana
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97)
…..
Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for relation bandanaCause
This is caused because the database was owned by the correct PostgreSQL user (handled by rake db:create and the database.yml file), when is restored the database contents, the user who was logged in was the postgresql super user, so the tables within the database were owned by postgresql, not the user who owned the database.
Solution
The solution was to restore databases using the user who owns the database. The reason is that you need additional privileges to access a view or table. Privileges on the database do not cover access to all objects in it.
psql -U postgres
create user confluence-db-user with password 'your-password';
create database confluencedb;
grant all privileges on database confluencedb to confluence-db-user;