2

Using PostgreSQL I see that the pg_dump command (and probably other commands) requires access to 'sequences'. I understand these to just be incremental IDs that are necessary for row identification...

Why is it necessary for server roles to be granted access to sequence objects? What are the potential security problems if every role had access to all sequences of accessible schemas?

1 Answer 1

2

pg_dump needs to dump the current position of the sequence, so that it can be restored. Otherwise, the restored sequence would start at 1 (or whatever its start value is) again and would generate duplicate keys.

Granting SELECT on the sequence should be enough for that purpose. Then the user can see the current value, but cannot increase or reset the sequence.

7
  • Do other database servers hide this from users by default? I guess that this prevents users from knowing how many rows are in a table unless they are explicitly allowed to know this?
    – Zach Smith
    Feb 2, 2021 at 4:23
  • The sequence value doesn't say much about the table row count. Feb 2, 2021 at 7:30
  • Why not? I would have thought that an auto increment ID would use a sequence, and that the sequence current value would give a minimum row count of a table... What is the reason that Postgres prevents reading the sequence value by default? I can't imagine it being useful in any way from a security perspective (unless there is some reason that knowing a sequence value is a security compromise)
    – Zach Smith
    Feb 2, 2021 at 8:23
  • There are things like DELETE and ROLLBACK which make that sequence values used in an autoincrement column are not in the table. Also, sequences can cycle and be reset. It is a design principle (although some objects, like databases, functions and types, violate it) that after an object is created, the owner has all rights, and everybody else has no rights. Makes sense, doesn't it? Feb 2, 2021 at 8:53
  • ... No it doesn't make sense that an object that has no bearing on security is associated with security roles. Especially since other objects such as databases, function, types, as you mention already violate the design principle that all objects are associated with security roles. But that is just my opinion
    – Zach Smith
    Feb 2, 2021 at 9:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.