Postgresql – How to solve privileges issues when restore PostgreSQL Database

database-backupspostgresqlrails-postgresql

I have dumped a clean, no owner backup for Postgres Database with the command

pg_dump sample_database -O -c -U

Later, when I restore the database with

psql -d sample_database -U app_name

However, I encountered several errors which prevents me from restoring the data:

ERROR:  must be owner of extension plpgsql
ERROR:  must be owner of schema public
ERROR:  schema "public" already exists
ERROR:  must be owner of schema public
CREATE EXTENSION
ERROR:  must be owner of extension plpgsql

I digged into the plain-text SQL pg_dump generates and I found it contains SQL 

CREATE SCHEMA public;
COMMENT ON SCHEMA public IS 'standard public schema';
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

I think the causes are that the user app_name doesn't have the privileges to alter the public schema and plpgsql.

How could I solve this issue?

Best Answer

To solve the issue you must assign the proper ownership permissions. Try the below which should resolve all permission related issues for specific users but as stated in the comments this should not be used in production:

root@server:/var/log/postgresql# sudo -u postgres psql
psql (8.4.4)
Type "help" for help.

postgres=# \du
               List of roles
    Role name    | Attributes  | Member of
-----------------+-------------+-----------
 <user-name>    | Superuser   | {}
                 : Create DB
 postgres       | Superuser   | {}
                 : Create role
                 : Create DB

postgres=# alter role <user-name> superuser;
ALTER ROLE
postgres=#

So connect to the database under a Superuser account sudo -u postgres psql and execute a ALTER ROLE <user-name> Superuser; statement.

Keep in mind this is not the best solution on multi-site hosting server so take a look at assigning individual roles instead: https://www.postgresql.org/docs/current/static/sql-set-role.html and https://www.postgresql.org/docs/current/static/sql-alterrole.html.

Related Topic