pg_dump Permission Denied for Sequence – PostgreSQL Solutions

amazon-rdspermissionspg-dumppostgresql

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.

Best Answer

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