How to solve privileges issues when restore PostgreSQL Database

PostgresqlDatabase BackupsRails Postgresql

Postgresql Problem Overview

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
ERROR:  must be owner of extension plpgsql

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

COMMENT ON SCHEMA public IS 'standard public schema';
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?

Postgresql Solutions

Solution 1 - Postgresql

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;

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: and

Solution 2 - Postgresql

AWS RDS users if you are getting this it is because you are not a superuser and according to aws documentation you cannot be one. I have found I have to ignore these errors.

Solution 3 - Postgresql

For people using Google Cloud Platform, any error will stop the import process. Personally I encountered two different errors depending on the pg_dump command I issued :

1- The input is a PostgreSQL custom-format dump. Use the pg_restore command-line client to restore this dump to a database.

Occurs when you've tried to dump your DB in a non plain text format. I.e when the command lacks the -Fp or --format=plain parameter. However, if you add it to your command, you may then encounter the following error :

2- SET SET SET SET SET SET CREATE EXTENSION ERROR: must be owner of extension plpgsql

This is a permission issue I have been unable to fix using the command provided in the GCP docs, the tips from this current thread, or following advice from Google Postgres team here. Which recommended to issue the following command :

pg_dump -Fp --no-acl --no-owner -U myusername myDBName > mydump.sql

The only thing that did the trick in my case was manually editing the dump file and commenting out all commands relating to plpgsql.

I hope this helps GCP-reliant souls.

Update :

It's easier to dump the file commenting out extensions, especially since some dumps can be huge : pg_dump ... | grep -v -E '(CREATE\ EXTENSION|COMMENT\ ON)' > mydump.sql

Which can be narrowed down to plpgsql : pg_dump ... | grep -v -E '(CREATE\ EXTENSION\ IF\ NOT\ EXISTS\ plpgsql|COMMENT\ ON\ EXTENSION\ plpgsql)' > mydump.sql

Solution 4 - Postgresql

Try using the -L flag with pg_restore by specifying the file taken from pg_dump -Fc

> -L list-file --use-list=list-file

> Restore only those archive elements that are listed in list-file, and restore them in the order they appear in the file. Note that if filtering switches such as -n or -t are used with -L, they will further restrict the items restored.

>list-file is normally created by editing the output of a previous -l operation. Lines can be moved or removed, and can also be commented out by placing a semicolon (;) at the start of the line. See below for examples.

pg_dump -Fc -f pg.dump db_name
pg_restore -l pg.dump | grep -v 'COMMENT - EXTENSION' > pg_restore.list
pg_restore -L pg_restore.list pg.dump

Here you can see the Inverse is true by outputting only the comment:

pg_dump -Fc -f pg.dump db_name
pg_restore -l pg.dump | grep 'COMMENT - EXTENSION' > pg_restore_inverse.list
pg_restore -L pg_restore_inverse.list pg.dump
-- PostgreSQL database dump

-- Dumped from database version 9.4.15
-- Dumped by pg_dump version 9.5.14

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

-- PostgreSQL database dump complete

Solution 5 - Postgresql

You can probably safely ignore the error messages in this case. Failing to add a comment to the public schema and installing plpgsql (which should already be installed) aren't going to cause any real problems.

However, if you want to do a complete re-install you'll need a user with appropriate permissions. That shouldn't be the user your application routinely runs as of course.

Solution 6 - Postgresql

Shorter answer: ignore it.

This module is the part of Postgres that processes the SQL language. The error will often pop up as part of copying a remote database, such as with a 'heroku pg:pull'. It does not overwrite your SQL processor and warns you about that.

Solution 7 - Postgresql

For people using AWS, the COMMENT ON EXTENSION is possible only as superuser, and as we know by the docs, RDS instances are managed by Amazon. As such, to prevent you from breaking things like replication, your users - even the root user you set up when you create the instance - will not have full superuser privileges:

> When you create a DB instance, the master user system account that you > create is assigned to the rds_superuser role. The rds_superuser role > is a pre-defined Amazon RDS role similar to the PostgreSQL superuser > role (customarily named postgres in local instances), but with some > restrictions. As with the PostgreSQL superuser role, the rds_superuser > role has the most privileges on your DB instance and you should not > assign this role to users unless they need the most access to the DB > instance.

In order to fix this error, just use -- to comment out the lines of SQL that contains COMMENT ON EXTENSION

Solution 8 - Postgresql

For people who have narrowed down the issue to the COMMENT ON statements (as per various answers below) and who have superuser access to the source database from which the dump file is created, the simplest solution might be to prevent the comments from being included to the dump file in the first place, by removing them from the source database being dumped...


Future dumps then won't include the COMMENT ON statements.

Solution 9 - Postgresql

Use the postgres (admin) user to dump the schema, recreate it and grant priviledges for use before you do your restore. In one command:

sudo -u postgres psql -c "DROP SCHEMA public CASCADE;
create SCHEMA public;
grant usage on schema public to public;
grant create on schema public to public;" myDBName

Solution 10 - Postgresql

For me, I was setting up a database with pgAdmin and it seems setting the owner during database creation was not enough. I had to navigate down to the 'public' schema and set the owner there as well (was originally 'postgres').

Solution 11 - Postgresql

Some of the answers have already provided various approaches related to getting rid of the create extension and comment on extensions. For me, the following command line seemed to work and be the simplest approach to solve the problem:

cat /tmp/backup.sql.gz | gunzip - | \
  grep -v -E '(CREATE\ EXTENSION|COMMENT\ ON)' |  \
    psql --set ON_ERROR_STOP=on -U db_user -h localhost my_db

Some notes

  • The first line is just uncompressing my backup and you may need to adjust accordingly.
  • The second line is using grep to get rid of offending lines.
  • the third line is my psql command; you may need to adjust as you normally would use psql for restore.


All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionsteveyangView Question on Stackoverflow
Solution 1 - PostgresqlDaniel SokolowskiView Answer on Stackoverflow
Solution 2 - PostgresqlJim ZuckerView Answer on Stackoverflow
Solution 3 - PostgresqlStandaa - Remember MonicaView Answer on Stackoverflow
Solution 4 - PostgresqlLigemerView Answer on Stackoverflow
Solution 5 - PostgresqlRichard HuxtonView Answer on Stackoverflow
Solution 6 - PostgresqlCharles MerriamView Answer on Stackoverflow
Solution 7 - PostgresqlPetar NikovView Answer on Stackoverflow
Solution 8 - PostgresqlMark SchneiderView Answer on Stackoverflow
Solution 9 - PostgresqlPascal_dherView Answer on Stackoverflow
Solution 10 - PostgresqlPeter LView Answer on Stackoverflow
Solution 11 - PostgresqloxerView Answer on Stackoverflow