Postgresql
序列的 pg_dump 權限被拒絕
post-data
我對轉儲部分有一個奇怪的問題。轉儲命令是:
pg_dump -Fc --verbose --section=post-data --no-owner --no-acl -h localhost -p 9999 -U root db_name -W > constraints.dump
這是一條錯誤消息:
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
但是當我嘗試在其中執行此查詢
psql
時效果很好: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)
這是授予使用者的權限
root
db_name=> \dg root List of roles Role name | Attributes | Member of -----------+-------------------------------+----------------- root | Create role, Create DB +| {rds_superuser} | Password valid until infinity |
我授予了預設權限,select 和 all 到公共模式中的所有表和序列,但沒有運氣。
你能幫忙嗎?提前感謝!
更新
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)
我正在使用亞馬遜 RDS。
可能是特定於 AWS-RDS 的問題,但如果您明確指定要轉儲的架構,它就會起作用。
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
我猜這只是對試圖轉儲的對象的權限問題。排除該序列可以使 pg_dump 繼續。