0

Does dropping and recreating a postgres database (or schema or table) cause a loss of any granular permissions that were formerly associated with that resource?

For comparison, is it like a Posix filesystem (or Linux FACL) where the metadata is attached to the entries and so deleting and recreating a director tree will undo any granular permission changes, or is the metadata kept elsewhere (and with no foreign key constraint) so that the granular policies can be retained and still apply if any future database resources match those same names?

Improve this question

2 Answers 2

Reset to default
1

In PostgreSQL, privileges are stored as an access control list (ACL) with the object's metadata. Each object “knows” who is allowed to do what with it. These privileges vanish with the object if you drop it. For example, for relations — tables or (materialized) views in this case — the information is stored in pg_class.relacl. For databases, it is pg_database.datacl, and so on.

There is one way to influence the privileges on not yet created objects in PostgreSQL: you can alter the default privileges for objects of a certain type with the ALTER DEFAULT PRIVILEGES command. But that does not preserve the privileges on objects after you dropped them.

Privileges on the database are usually not included in a dump of the database. If you want to dump the database privileges as well, use the --create option of pg_dump (or pg_restore, if you are not using the plain format).

Improve this answer
1
  • If you pg_dump, drop & pg_restore a database, do the privileges get restored too (or is the metadata excluded from the dump)? Commented Feb 5 at 20:59
0

If you have a role with a specific permission to an object, let's say a function, and you drop the function, that permission goes with it. It's not stored on the side someplace and will magically reattach if you later recreate the function.

Improve this answer
2
  • Did you mean to say will not magically reattach? Commented Feb 5 at 20:55
  • 1
    I think I said it correctly. Regardless, it won't reattach. Commented Feb 12 at 15:13

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.