How to solve privileges issues when restore PostgreSQL Database

Question

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?

If you don't need `plpgsql`, then `DROP EXTENSION plpgsql` before you `pg_dump`. This is safer than making your app a super user, and it is more convenient than ignoring errors (which bombs if you use `--single-transaction` or `-v ON_ERROR_STOP=1`). This is a known issue, [discussed at length by the Postgres developers|http://www.postgresql.org/message-id/1b8bae0e0822578a72f686d528337525.squirrel@webmail.harte-lyne.ca] but not fixed as of 9.3. — Mark E. Haase, Jun 05 '15 at 21:16

score 79 · Answer 1 · edited Sep 18 '16 at 23:37

79

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.

edited Sep 18 '16 at 23:37

MichielB

4,181
1
30
39

answered Mar 16 '13 at 18:04

Daniel Sokolowski

11,982
4
69
55

35

is there a way to do this without being a superuser? – Travis Webb May 12 '14 at 14:58
28

"must assign the proper ownership permissions" and "alter role superuser" are not congruent. Proper ownership would mean that `app_user` is *not* a super user. – Mark E. Haase Jun 05 '15 at 19:28
1

@mehaase please update the answer's wording as opposed to down voting. – Daniel Sokolowski Jun 05 '15 at 20:17
6

IMHO this is not solution but workaround that should be avoided in production. – Dmytriy Voloshyn Aug 22 '15 at 22:57
how can I achieve this by shell script? – user269867 Jul 17 '17 at 16:57
7

It is a bad suggestion to make a normal user `superuser` – Evren Yurtesen Dec 16 '17 at 10:20

score 69 · Answer 2 · answered Feb 24 '17 at 14:21

69

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.

answered Feb 24 '17 at 14:21

Jim Zucker

850
9
10

6

This error is preventing restore from completing for me (AWS RDS pg_restore). Any tips for ignoring these errors? – avjaarsveld Mar 01 '18 at 11:49
P.S. I did not use -e or --exit-on-error for pg_restore – avjaarsveld Mar 01 '18 at 11:57
9

I've found that, on RDS, the problem is `COMMENT ON EXTENSION`, not `CREATE EXTENSION`. Remove the comments and you should be fine. – pkoch Jun 17 '19 at 17:35
@pkoch same with Google Cloud Storage. COMMENT ON EXTENSION was the issue and not needed – Jaybeecave Apr 11 '20 at 00:13
Filter the comments with this `pg_restore --list $DUMP_DIR | sed '/COMMENT - EXTENSION/d' > $DUMP_ROOT/restore.list` then run the restore with this `pg_restore --dbname $CONN --verbose --exit-on-error --use-list $DUMP_ROOT/restore.list --no-owner --role=$DBUSER --clean --if-exists $DUMP_DIR` – Alf47 Sep 25 '20 at 18:24
AWS RDS doesn't allow you to create the superuser, BUT you can add a new user to the `rds_superuser` role: `CREATE USER new_user WITH NOSUPERUSER NOCREATEDB IN ROLE "rds_superuser" ...;` In this case, the user will be able to create extensions and comments. – Maxim Sep 14 '22 at 10:32

Standaa - Remember Monica · Answer 3 · 2017-10-11T14:48:38.663

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

GCP now has the exact `pg_dump` command to use in their [docs](https://cloud.google.com/sql/docs/postgres/import-export/exporting): `pg_dump -U [USERNAME] --format=plain --no-owner --no-acl [DATABASE_NAME] \ | sed -E 's/(DROP|CREATE|COMMENT ON) EXTENSION/-- \1 EXTENSION/g' > [SQL_FILE].sql` — Rush, Apr 17 '19 at 05:53
I had to use double quotes in the plgsql word like `"plpgsql"` on the grep command to make this work. — soltex, May 10 '21 at 10:36

score 18 · Answer 4 · answered Nov 06 '18 at 06:03

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.

https://www.postgresql.org/docs/9.5/app-pgrestore.html

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

I think the above is correct, excluding comments for plugins won't affect the functionality of your app — Andreas, Feb 02 '20 at 21:08
This is definitely the best answer, not sure why it's below two answers that say to simply ignore it... — Dylan, Aug 20 '20 at 07:31

score 14 · Answer 5 · answered Nov 16 '12 at 09:31

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.

score 12 · Answer 6 · answered Mar 28 '15 at 17:35

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.

Petar Nikov · Answer 7 · 2022-06-17T09:56:48.490

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:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html

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

EDIT 1: As suggested by Dmitrii I., you can also omit comments when dumping: pg_dump --no-comments

Or omit comments when dumping: `pg_dump --no-comments`. – Dmitrii I. Jun 13 '20 at 17:43 — Dmitrii I., Jun 13 '20 at 17:43

score 4 · Answer 8 · answered May 12 '20 at 08:52

4

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

COMMENT ON EXTENSION postgis IS NULL;
COMMENT ON EXTENSION plpgsql IS NULL;
COMMENT ON SCHEMA public IS NULL;

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

answered May 12 '20 at 08:52

Mark Schneider

360
2
10

1

Developing locally in Rails (which creates a new dump file automatically whenever a schema migration is run), this solutions enables me to simply run `rails db:reset` against a AWS RDS postgresql instance without having to remove COMMENT ON lines from the dump file every time I run a schema migration. – Mark Schneider May 12 '20 at 09:00

score 1 · Answer 9 · answered Oct 18 '15 at 14:31

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

score 1 · Answer 10 · answered May 10 '17 at 06:56

1

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').

answered May 10 '17 at 06:56

Peter L

2,921
1
29
31

score 1 · Answer 11 · answered Feb 20 '21 at 19:40

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.

How to solve privileges issues when restore PostgreSQL Database

11 Answers11

Linked