Importing database on postgres - Permission denied on Bandana
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
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.
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;