6

I have a strange issue with dumping post-data section.

Dump command is:

pg_dump -Fc --verbose --section=post-data --no-owner --no-acl -h localhost -p 9999 -U root db_name -W > constraints.dump

Here is an error message:

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation verification_code_user_mapping_id_seq
pg_dump: [archiver (db)] query was: SELECT sequence_name, start_value, increment_by, CASE WHEN increment_by > 0 AND max_value = 9223372036854775807 THEN NULL      WHEN increment_by < 0 AND max_value = -1 THEN NULL      ELSE max_value END AS max_value, CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL      WHEN increment_by < 0 AND min_value = -9223372036854775807 THEN NULL      ELSE min_value END AS min_value, cache_value, is_cycled FROM verification_code_user_mapping_id_seq

But when I'm trying to execute this query in psql it works well:

db_name=>  SELECT sequence_name, start_value, increment_by, CASE WHEN increment_by > 0 AND max_value = 9223372036854775807 THEN NULL      WHEN increment_by < 0 AND max_value = -1 THEN NULL      ELSE max_value END AS max_value, CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL      WHEN increment_by < 0 AND min_value = -9223372036854775807 THEN NULL      ELSE min_value END AS min_value, cache_value, is_cycled FROM verification_code_user_mapping_id_seq;
             sequence_name             | start_value | increment_by | max_value | min_value | cache_value | is_cycled 
---------------------------------------+-------------+--------------+-----------+-----------+-------------+-----------
 verification_code_user_mapping_id_seq |           1 |            1 |           |           |           1 | f
(1 row)

Here are the permissions granted to user root

db_name=> \dg root
                        List of roles
 Role name |          Attributes           |    Member of    
-----------+-------------------------------+-----------------
 root      | Create role, Create DB       +| {rds_superuser}
           | Password valid until infinity | 

I granted default privileges, select and all to all tables and sequences in schema public but with no luck.

Could you assist please? Thanks in advnce!

UPDATE

db_name=> \dp verification_code_user_mapping_id_seq
                                              Access privileges
 Schema |                 Name                  |   Type   | Access privileges | Column privileges | Policies 
--------+---------------------------------------+----------+-------------------+-------------------+----------
 public | verification_code_user_mapping_id_seq | sequence | root=rwU/root     |                   | 
(1 row)

I'm using an Amazon RDS.

2
  • How about \dp verification_code_user_mapping_id_seq ?
    – Luan Huynh
    Nov 7, 2017 at 7:33
  • Sorry, for delay. Please check updated section of the answer.
    – retgoat
    Nov 7, 2017 at 9:00

2 Answers 2

8

Might be an AWS-RDS specific issue but it works if you explicitly specify the schema to be dumped.

pg_dump -f lol.sql -Fc -v -n public -O -x --section=post-data -h 127.0.0.1 -p 9999 -U root db-name

3
  • 1
    I have PostGIS installed and this was the only solution that worked for me. One cannot even exclude all of the tables that RDS says I don't have access to, it ignores your excludes and then fails to get permission to dump those tables. I should also note that the tables I don't have permission to access are in the schema I'm specifying, but for some reason, now, without even explicitly excluding those tables it works. woof. Jun 14, 2019 at 20:48
  • Selecting only the schema to be backed up (rather than all schemas) also worked for Azure hosted Postgres Jul 24, 2022 at 12:36
  • I set the schema in my pg_dump command as mentioned and it still didn't work. I had configured my RDS databse through Terraform to create a read-only user for performing pg_dumps. I just needed to add another postgresql_grant with select privilege on the sequence object type in the public schema. Related to: registry.terraform.io/providers/cyrilgdn/postgresql/latest/docs/… Aug 29 at 13:38
-2

My guess that this is simply a permission issue towards the object that is attempted to be dumped. Excluding the sequence would work fine for pg_dump to continue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.