Home > mailing lists

Thread: Permissions pg_dump / import

Permissions pg_dump / import

From

Patrick B

Date:

17 August 2016, 22:17:23

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

Re: Permissions pg_dump / import

From

Steve Crawford

Date:

17 August 2016, 22:30:17

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

Re: Permissions pg_dump / import

From

"Ilya Kazakevich"

Date:

17 August 2016, 22:30:56

>> Owned by: public.accounts.id

This is not owner but table this sequence depends on. See http://stackoverflow.com/questions/6941043/get-table-and-column-owning-a-sequence

Use query provided on SO to get real owner

Ilya Kazakevich

JetBrains

http://www.jetbrains.com

The Drive to Develop

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Patrick B
Sent: Thursday, August 18, 2016 1:17 AM
To: pgsql-general
Subject: [GENERAL] Permissions pg_dump / import

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

Re: Permissions pg_dump / import

From

Patrick B

Date:

17 August 2016, 23:27:57

2016-08-18 10:30 GMT+12:00 Ilya Kazakevich <Ilya.Kazakevich@jetbrains.com>:

>> Owned by: public.accounts.id
This is not owner but table this sequence depends on. See http://stackoverflow.com/questions/6941043/get-table-and-column-owning-a-sequence

Use query provided on SO to get real owner

Thanks guys... the "--no-owner" option helped a lot.

Cheers

Patrick

Re: Permissions pg_dump / import

From

Patrick B

Date:

23 August 2016, 01:32:48

Hi guys,

I'm doing a pg_dump and a pg_restore on the same command, using different usernames and databases names.:

pg_dump --format=custom -v --no-password --no-owner --username=teste1 --dbname=test1 --host=11.11.11.11 | pg_restore -v --schema=public --no-password --no-owner --username=master --host=11.11.11.12 --dbname=new_test1

But I'm getting some permissions errors:

could not execute query: ERROR: role "devel" does not exist

To fix that, I ran on the server; also I'm using "--no-owner" and though this kind of problem wouldn't be happening?

REVOKE ALL ON SCHEMA public FROM devel;

But it seems not working, as I'm still getting the errors.

Do you guys have any tips to solve this one?

Cheers

Patrick

Re: Permissions pg_dump / import

From

Tom Lane

Date:

23 August 2016, 01:41:19

Patrick B <patrickbakerbr@gmail.com> writes:
> I'm doing a pg_dump and a pg_restore on the same command, using different
> usernames and databases names.:
> ...
> But I'm getting some permissions errors:
> could not execute query: ERROR:  role "devel" does not exist

If that's from

> REVOKE ALL ON SCHEMA public FROM devel;

it's not a permissions error, it's complaining there's no such role
to grant/revoke from in the destination DB.  You may want to use
--no-privileges along with --no-owner if the destination doesn't
have the same set of users as the source.  Or just ignore these errors.

            regards, tom lane