Thread: How to determine what is preventing deleting a rule
I created some roles that I think are badly named, as my understanding of
them increases.
I have deleted, what i thought were all the objects referenced by these roles,
but I still get an error saying there is one object they reference.
How can I find out what this object is?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
stan <stanb@panix.com> writes:
> I have deleted, what i thought were all the objects referenced by these roles,
> but I still get an error saying there is one object they reference.
> How can I find out what this object is?
The error should tell you, if you're connected to the database where
that object is.
regression=# create database d1;
CREATE DATABASE
regression=# create database d2;
CREATE DATABASE
regression=# create user joe;
CREATE ROLE
regression=# \c d2 joe
You are now connected to database "d2" as user "joe".
d2=> create table tt(d1 int);
CREATE TABLE
d2=> \c d1 postgres
You are now connected to database "d1" as user "postgres".
d1=# drop user joe;
ERROR: role "joe" cannot be dropped because some objects depend on it
DETAIL: 1 object in database d2
d1=# \c d2
You are now connected to database "d2" as user "postgres".
d2=# drop user joe;
ERROR: role "joe" cannot be dropped because some objects depend on it
DETAIL: owner of table tt
Unfortunately, we can't see into the catalogs of a different database
to explain about dependencies there ...
regards, tom lane
> How can I find out what this object is?
This query might help if the object is a table:
SELECT c.relname as table,
a.rolname as owner,
c.relacl as permits
FROM pg_class c
JOIN pg_authid a ON ( a.OID = c.relowner )
WHERE relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'information%'
AND relname NOT LIKE 'sql_%'
AND relkind = 'r'
AND a.rolname = '<the_owner_with_problem>'
ORDER BY relname;
a.rolname as owner,
c.relacl as permits
FROM pg_class c
JOIN pg_authid a ON ( a.OID = c.relowner )
WHERE relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'information%'
AND relname NOT LIKE 'sql_%'
AND relkind = 'r'
AND a.rolname = '<the_owner_with_problem>'
ORDER BY relname;
Since you have not stated the PostgreSQL version, I can only
assure you this will work for 9.6 and below.
On Sat, Aug 17, 2019 at 7:49 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
stan <stanb@panix.com> writes:
> I have deleted, what i thought were all the objects referenced by these roles,
> but I still get an error saying there is one object they reference.
> How can I find out what this object is?
The error should tell you, if you're connected to the database where
that object is.
regression=# create database d1;
CREATE DATABASE
regression=# create database d2;
CREATE DATABASE
regression=# create user joe;
CREATE ROLE
regression=# \c d2 joe
You are now connected to database "d2" as user "joe".
d2=> create table tt(d1 int);
CREATE TABLE
d2=> \c d1 postgres
You are now connected to database "d1" as user "postgres".
d1=# drop user joe;
ERROR: role "joe" cannot be dropped because some objects depend on it
DETAIL: 1 object in database d2
d1=# \c d2
You are now connected to database "d2" as user "postgres".
d2=# drop user joe;
ERROR: role "joe" cannot be dropped because some objects depend on it
DETAIL: owner of table tt
Unfortunately, we can't see into the catalogs of a different database
to explain about dependencies there ...
regards, tom lane
--
Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!