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?
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
how many words do you know
Powered by Examplum