CODEWITHSUNDEEP
databasepostgresqlprivileges
0x5C91
0x5C91

Reputation: 3515

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.

Upvotes: 6

Views: 2973

Answers (2)

Matt Whitlock
Matt Whitlock

Reputation: 887

To restore default access privileges on a table in PostgreSQL, you can set the relacl field of the table to NULL in the system catalog:

UPDATE pg_class
    SET relacl = NULL
    WHERE oid = 'public.example'::regclass;

If you also want to restore default access privileges on the columns of the table, you can do that too:

UPDATE pg_attribute
    SET attacl = NULL
    WHERE attrelid = 'public.example'::regclass;

Upvotes: 0

Chris Travers
Chris Travers

Reputation: 26464

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.

Upvotes: 1

Related Questions