find and remove public schema object dependencies for a role in postgresql

Question

Weeks ago when I was learning PostgreSQL security, I was trying to do some strange things (although not recommended) to remove the public schema from the object browser in pgadmin. Luckily I have all the commands I ran and all of the undo commands I ran, but I still seem to have one role object that I'm unable to drop. I'm getting an error when trying to DROP ROLE test_role_11, but I still seem to have object dependencies in the public schema. Can you help me find the query to find out what it is? Can you also help me find the correct REVOKE command (or other) to undo the dependencies and/or privileges, given the first query? PUBLIC seems to be its own challenge and not an actual role, so I'm not sure how to write the query. I've tried using old questions, but didn't get very far.

Exception/Error:

role "test_role_11" cannot be dropped because some objects depend on it DETAIL: privileges for schema public

Here are the queries I ran.

CREATE ROLE test_role_11 
--NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOLOGIN NOREPLICATION NOBYPASSRLS
REVOKE ALL ON ALL TABLES IN SCHEMA pg_catalog FROM public, me;
--GRANT ALL ON ALL TABLES IN SCHEMA pg_catalog TO public, me;
GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO test_role_11;
--REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM test_role_11;
GRANT test_role_11 TO me
--REVOKE object_browser FROM me;

--GRANT SELECT ON TABLE aaa IN SCHEMA pg_catalog TO test_role_11;

GRANT SELECT ON TABLE pg_catalog."aaa" TO test_role_11;
GRANT SELECT ON TABLE staging."aaa" TO test_role_11;

GRANT SELECT ON TABLE pg_database TO test_role_11;

---

These did not find any object dependencies for my role.

Find objects linked to a PostgreSQL role

---

Answer 1

After looking in all of the catalog tables, I was able to find some records with the oid of that object. But I'm not sure what to do with this information quite yet.

SELECT rolname, oid FROM pg_roles WHERE rolname = 'test_role_11';

-- rolname, oid
-- test_role_11, 612646

SELECT DISTINCT dbid, * FROM   pg_shdepend WHERE refobjid = 612646

-- dbid, classid, objid, objsubid, refclassid, refobjid, deptype
-- 612032, 2615, 2200, 0, 1260, 612646, a
-- obj* must be the dependent object (aka 'test_role_11')
-- ref* must be the dependency object

SELECT * FROM pg_database WHERE oid = 612032

-- oid, datname
-- 612032, p01

SELECT * FROM  pg_catalog.pg_class WHERE oid = 2615

-- oid, relname, relnamespace, reltype, reloftype, relowner, relam, relfilenode, reltablespace, relpages, reltuples, relalvisible, reltoastrelid, 
-- 2615, pg_namespace, 11, 12023,  ...

SELECT * FROM pg_namespace WHERE oid = 11;

-- oid, nspname, nspowner, nspacl
-- 11, pg_catalog, 10, {postgres=UC/postgres,=U/postgres}

SELECT * FROM pg_namespace WHERE oid = 2200

-- oid, nspname, nspowner, nspaci
-- 2200, public, 10, {postgres=UC/postgres,=UC/postgres,test_role_11=U/postgres}

So it turns out that the dependent object that references the dependency (ref*) object is 2200, which is the public schema itself! So I need to change the ownership of this role (perhaps).

Here's what I learned. The dependent (parent), being oid=2200, of the dependency can be of any object type. So it's tricky to figure out which table its in. There are also two tables that have the dependencies. This dependency was not in the pg_depend!

In PostgreSQL the dependencies for the database objects are tracked in the system table pg_depend. There is also another system table pg_shdepend which tracks the dependencies for objects that are shared across all the databases present in the cluster.

This solved the problem! And was able to DROP ROLE test_role_11 after this.

REVOKE ALL ON SCHEMA public FROM test_role_11;