Hello!
I ran into an interesting situation last week.
Discovered on RDS Postgres version 13.
Replicated on community Postgres version 13.
A client wanted us to drop some invalid indexes, some of which turned out to be invalid indexes on TOAST tables.
We don't know for sure how their database got this way; we suspect a failed 'REINDEX DATABASE CONCURRENTLY', possibly more than one.
Attempts to drop the invalid index fail like so:
testy=> drop index CONCURRENTLY pg_toast.pg_toast_14199_index_ccnew;
ERROR: permission denied for schema pg_toast
This fails even when I'm logged in as the owner of the toast table's parent table; I have to assume superuser privileges in order to drop them.
On a hosted environment, this means a user could create an invalid index that they can't drop without provider intervention.
Is this expected behavior?
Thanks!
gabrielle