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?
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.