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.
\dp verification_code_user_mapping_id_seq
?