patternsqlMinor
pg_dump permission denied for sequence
Viewed 0 times
pg_dumppermissiondeniedsequencefor
Problem
I have a strange issue with dumping
Dump command is:
Here is an error message:
But when I'm trying to execute this query in
Here are the permissions granted to user
I granted default privileges, select and all to all tables and sequences in schema public but with no luck.
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.dumpHere 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 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_seqBut 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 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
rootdb_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.
Solution
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-nameContext
StackExchange Database Administrators Q#190270, answer score: 8
Revisions (0)
No revisions yet.