How can I query the object created by a specific user in postgresql

when I DROP USER regress_view_user1 ; then error:

ERROR:  2BP01: role "regress_view_user1" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema public
LOCATION:  DropRole, user.c:1003

I forgot what kind of object I've created. i already drop a table (base_tbl) cascade.
https://www.postgresql.org/docs/current/view-pg-roles.html
https://www.postgresql.org/docs/current/view-pg-user.html

pg_user only one oid type column, pg_roles also one oid type column.
https://www.postgresql.org/docs/current/catalog-pg-depend.html
pg_depend, there are 4 oid type columns.
So I executed 8 query (pg_depend with pg_user, pg_depend with pg_roles). but all 8 queries return 0 rows.

begin;
 SELECT * FROM pg_depend WHERE objid
        = (SELECT usesysid  FROM pg_user WHERE usename ='regress_view_user1');
 SELECT * FROM pg_depend WHERE classid
            = (SELECT usesysid  FROM pg_user WHERE usename ='regress_view_user1');
 SELECT * FROM pg_depend WHERE refclassid
                = (SELECT usesysid  FROM pg_user WHERE usename ='regress_view_user1');
 SELECT * FROM pg_depend WHERE refobjid
            = (SELECT usesysid  FROM pg_user WHERE usename ='regress_view_user1');
-----------------------------------------------
 SELECT * FROM pg_depend WHERE refobjid
            = (SELECT oid  FROM pg_roles WHERE rolname ='regress_view_user1');
 SELECT * FROM pg_depend WHERE classid
            = (SELECT oid  FROM pg_roles WHERE rolname ='regress_view_user1');
 SELECT * FROM pg_depend WHERE refclassid
        = (SELECT oid  FROM pg_roles WHERE rolname ='regress_view_user1');
 SELECT * FROM pg_depend WHERE objid
        = (SELECT oid  FROM pg_roles WHERE rolname ='regress_view_user1');
commit ;

1 answer

  • answered 2022-05-04 13:54 Mark

    solved via:

    REVOKE ALL PRIVILEGES ON schema public FROM regress_view_user1 cascade ;
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum