5

I have a table named test with id column. And id column has updated by sequence called "test_id_seq". This test table was created by the user "A" and another user "B" have the read permission. While creating the dump for table from the user "B". Using the following comment

pg_dump -U B -t test rafiu > test.sql

it showing the error like

pg_dump: [archiver (db)] query failed: ERROR: permission denied for relation test_id_seq

Is there any option to dump only the table?

Rafiu
  • 4,700
  • 6
  • 25
  • 27
  • What kind of dump do you really require? If you require data that is accessible also for user B, use --data-only option. – Martin Strejc Nov 22 '13 at 09:47
  • I need table structure and data. Is that possible. – Rafiu Nov 22 '13 at 09:51
  • The sequence counts as part of the structure. Any reason not to grant "B" permission to dump the sequence? – Richard Huxton Nov 22 '13 at 11:06
  • I tested it on the same case and it works. Just take care if you have both schema and sequence in the same schema or if you don't have another security restriction. Grant use to connect a database and select a table to make dump is enough. – Martin Strejc Nov 22 '13 at 11:13

2 Answers2

6

When the sequence is "owned" by a column, it's dumped with it, so the user producing the dump must have the rights to access it (GRANT SELECT ON sequence_name TO username)

This happens when using the SERIAL/BIGSERIAL pseudo-datatypes. However, the sequence can still be detached from the column afterwards by issuing:

ALTER SEQUENCE test_id_seq OWNED BY none;

After that, assigning a default value for test.ID with the sequence will continue to work as usual, but pg_dump will make no attempt to dump the sequence with the table.

If the table is created from the start with a pre-existing sequence (not using SERIAL), then the outcome is the same without the need for ALTER SEQUENCE.

Example:

create sequence seq1;
create table test1 (id int default nextval('seq1'));

In this case the table would be dumped with pg_dump -t test1 as:

CREATE TABLE test1 (
    id integer DEFAULT nextval('seq1'::regclass)
);

with no other reference to seq1 and no need for permission to read it.

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
  • So I guess the answer is no, there is no way to use pg_dump on a table that was created with the serial pseudo-datatype, if you don't have permissions on the sequence, without getting the admin to modify the sequence ownership. This is unfortunate. I suppose in my case I'll manually extract the data and import it again using the json functions. – ADJenks Aug 19 '21 at 17:54
4

Have you tried using the -T option to neglect the sequence?

pg_dump -U B -T test_id_seq rafiu > test.sql

This should take a pgdump without that sequence.

ZX12R
  • 4,760
  • 8
  • 38
  • 53