Postgresql

序列的 pg_dump 權限被拒絕

  • July 9, 2020

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 繼續。

引用自:https://dba.stackexchange.com/questions/190270