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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  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    gt  mydump sql  Which can be narrowed down to plpgsql    pg dump       grep -v -E   CREATE  EXTENSION  IF  NOT  EXISTS  plpgsql COMMENT  ON  EXTENSION  plpgsql    gt  mydump sql

User · 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 ----------------- ------------- -----------   lt user-name gt       Superuser                           Create DB  postgres         Superuser                           Create role                    Create DB  postgres   alter role  lt user-name gt  superuser  ALTER ROLE postgres     So connect to the database under a Superuser account sudo -u postgres psql and execute a ALTER ROLE  lt user-name gt  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

User · Answer

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

[postgresql] How to solve privileges issues when restore PostgreSQL Database

Examples related to postgresql

Examples related to database-backups

Examples related to rails-postgresql