Postgres Pro
Downloads
Home   >   mailing lists

Re: Permissions pg_dump / import - Mailing list pgsql-general

From Steve Crawford
Subject Re: Permissions pg_dump / import
Date
Msg-id CAEfWYyzqmvn+uGSrekQ4j_2XsH8DzJunsoadutcvOgSAxZ6TeQ@mail.gmail.com
Whole thread Raw
In response to Permissions pg_dump / import  (Patrick B <patrickbakerbr@gmail.com>)
List pgsql-general
Tree view
Check out the --no-owner and/or --no-acl flags when performing the dump. These eliminate the statements that set and/or alter ownership of database objects.

For use in a test server where the username of the test-server database is different than the username on the production server *and* where you don't have lots of roles with different ownership and permissions across your database you should be fine.

Or create role(s) on your test database that match those on the production database. This may require updating pg_hba.conf on the test database.

Cheers,
Steve


On Wed, Aug 17, 2016 at 3:16 PM, Patrick B <patrickbakerbr@gmail.com> wrote:
Hi guys,

I'm running a pg_dump and then importing the dump into a test server. I'm using PostgreSQL 9.5.

pg_dump:
pg_dump --dbname=prod1 --host= servername --port=5432 --username=user1 -v -f test1_NEW.sql

Steps into the new database (test1):

CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO user1;
psql -h servername -U master --port=5432 --dbname=test1 -f test1_NEW.sql


I get lots of errors like:

psql:test1_NEW.sql:212: ERROR:  must be owner of relation accounts_id_seq

prod1=> \d+ accounts_id_seq
            Sequence "public.accounts_id_seq"
    Column     |  Type   |        Value        | Storage 
---------------+---------+---------------------+---------
 sequence_name | name    | accounts_id_seq     | plain
 last_value    | bigint  | 33                  | plain
 start_value   | bigint  | 1                   | plain
 increment_by  | bigint  | 1                   | plain
 max_value     | bigint  | 9223372036854775807 | plain
 min_value     | bigint  | 1                   | plain
 cache_value   | bigint  | 1                   | plain
 log_cnt       | bigint  | 32                  | plain
 is_cycled     | boolean | f                   | plain
 is_called     | boolean | t                   | plain
Owned by: public.accounts.id 

What do I have to do? Should I revoke the permissions on the prod1 database before performing the dump?

Cheers;
Patrick 

pgsql-general by date:

Previous
From: Patrick B
Date:
Subject: Permissions pg_dump / import
Next
From: "Ilya Kazakevich"
Date:
Subject: Re: Permissions pg_dump / import