Search code examples
databasepostgresqlprivileges

Restore default privileges in PostgreSQL

Is there a way to restore default privileges on a table in PostgreSQL?

After creating a table, if i launch \z in psql i see this:

 Schema |    Name     |   Type   |     Access privileges     | Column access privileges
--------+-------------+----------+---------------------------+-------------------------
 public | example     | table    |                           |

phpPgAdmin, also, says that the privileges for the table example are the default ones. If I edit these privileges, I would like to be able to revert my changes. Is this possible? So far I tried with REVOKE ALL, which actually displays an empty Access privileges field when launching \z, but obviously doesn't work because it removes every privilege even from the owner.

Solution

  • There is not really a direct way to do this.

    The closest thing you can do is to do this and then grant ALL to the owner (you can look up the owner in pg_class).

    One problem with doing it in this way is that default permissions could be set differently to that and that doing it purely in the backend requires using DO with a plpgsql statement. Worse, since GRANT is not a planned statement, you cannot parameterize it, meaning you have to do string interpolation.