How to solve privileges issues when restore PostgreSQL Database

If the request is a GET, then you should also add -G option, which tells curl to send the data with GET request.

curl -X GET -G http://www.example.com \
-d arg1=this \
-d arg2=that

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

" already exists

pg_dump sample_database -O -c -U

ERROR: must be owner of schema public

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?

153326 次浏览
小开
CREATE EXTENSION

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.

ERROR: must be owner of extension plpgsql

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.

小开

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

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

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

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

How could I solve this issue?

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.

小开
a 'heroku pg:pull'. It does not overwrite your SQL processor and warns you about that.

小开

Use the parameter --url with double quotes worked for me

Use the postgres (admin) user to dump the schema, recreate it and grant priviledges for use before you do your restore. iviledges 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
小开

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.

小开

easiest way :

curl -X GET -G http://example.com -d var1=$1 -d var2=$2

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

小开
pg_dump ... | grep -v -E '(CREATE\ EXTENSION\ IF\ NOT\ EXISTS\ plpgsql|COMMENT\ ON\ EXTENSION\ plpgsql)' > mydump.sql

小开

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

Hi I'm Trying to merge two arrays and also want to remove duplicate values from final Array.

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

小开

Here is my Array 1:

Array
(
[0] => stdClass Object
(
[ID] => 749
[post_author] => 1
[post_date] => 2012-11-20 06:26:07
[post_date_gmt] => 2012-11-20 06:26:07
)

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;

And this is my array 2:

Array
(
[0] => stdClass Object
(
[ID] => 749
[post_author] => 1
[post_date] => 2012-11-20 06:26:07
[post_date_gmt] => 2012-11-20 06:26:07


)

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

小开

try to use the array_unique()

  • the third line is my psql command; you may need to adjust as you normally would use psql for restore.

    • 提交答案