I can't drop a role from a postgres database. Running the DROP ROLE statement outputs the following error:
DROP ROLE read_only;
SQL Error [2BP01]: ERROR: role "read_only" cannot be dropped because some objects depend on it
Detail: privileges for table orgs.client
When I try to revoke those privileges from the tables, the REVOKE statement is successful (no errors at all):
REVOKE ALL PRIVILEGES ON TABLE orgs.client FROM read_only;
But then, if I try to drop the role again, I get the same error above.
I have no idea why the REVOKE statement is not working as expected. Or maybe it is working but the issue is something else. Any ideas on how to troubleshoot this?
Output of \z orgs.client:
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+--------+-------+---------------------------------------------------------------------------------+-------------------+----------
orgs | client | table | "prisma-beta-gsa@REDACTED"=arwdDxt/"prisma-beta-gsa@REDACTED-beta.iam" +| |
| | | REDACTED_dev=arwdDxt/"prisma-beta-gsa@REDACTED-beta.iam" +| |
| | | landlord=a*r*w*d*D*x*t*/"prisma-beta-gsa@REDACTED-beta.iam" +| |
| | | read_only=r/landlord +| |
| | | read_write=arwd/landlord +| |
| | | landlord=a*r*w*d*D*x*t*/costa +| |
| | | landlord=arwdDxt/landlord +| |
| | | REDACTED_read_only=r/"prisma-beta-gsa@REDACTED-beta.iam" +| |
| | | REDACTED_read_write=arwdD/"prisma-beta-gsa@REDACTED-beta.iam" +| |
| | | REDACTED_admin=arwdDxt/"prisma-beta-gsa@REDACTED-beta.iam" | |
NOTE: I'm working with a postgres 11 hosted in a Google Cloud SQL Instance. Because of this, I have no access to a superuser role. I do have access to the postgres user that is the closest you can get to a superuser (docs).
Since the privilege was granted by user landlord, you should also use that role to revoke the privilege:
REVOKE SELECT ON orgs.client FROM read_only;