Importing database on postgres - Permission denied on Bandana

Still need help?

The Atlassian Community is here for you.

Ask the community

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 exist

And 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 bandana

Cause

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;

 

 

Last modified on Oct 7, 2015

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.