Search code examples
postgresqldatabase-migration

Do I need to REVOKE permissions when a table is dropped

As part of a migration I create a table and grant permissions to a user. Do I need to REVOKE the permissions on my downgrade migration which already drops the table?

--- up.sql

CREATE TABLE IF NOT EXISTS table1 (id int primary key);

GRANT SELECT, INSERT, DELETE ON TABLE table1 TO user1;

--- down.sql

DROP TABLE IF EXISTS table1;

Do I need to add the following or is it implicit?

REVOKE SELECT, INSERT, DELETE ON table1 FROM user;
Solution

  • If you drop an object, all its privileges are also gone. That is because privileges are stored on the object itself: in the case of a table, the privileges are stored in the relacl column of the pg_class table (where all tables are stored).