Обсуждение: Eror while dropping a user
Hello, Using PostgreSQL 9.6.9 on Linux amd64 platform. I have two users that I cannot drop from the system. I revoked all permissions from tables, sequences, functions. They do not own any objects. My log lines are as following: 2018-08-03 23:24:03.897 There are 2 users that needs to be removed from system. 2018-08-03 23:24:03.897 Removing user previliges on tables: pars.test 2018-08-03 23:24:03.899 Removing user previliges on sequences: pars.test 2018-08-03 23:24:03.900 Removing user previliges on functions: pars.test 2018-08-03 23:24:03.900 Dropping user itself: pars.test 2018-08-03 23:24:03.901 Drop user failed: SQL Error: ERROR: role "pars.test" cannot be dropped because some objects depend on it AYRINTI: 1 object in database postgres 2018-08-03 23:24:03.901 Removing user previliges on tables: pars.test2 2018-08-03 23:24:03.902 Removing user previliges on sequences: pars.test2 2018-08-03 23:24:03.903 Removing user previliges on functions: pars.test2 2018-08-03 23:24:03.903 Dropping user itself: pars.test2 2018-08-03 23:24:03.904 Drop user failed: SQL Error: ERROR: role "pars.test2" cannot be dropped because some objects depend on it AYRINTI: 1 object in database postgres I could not find any reference on postgres database for these users. I am not very good on database administration. Any help is appreciated. Thanks & regards, Ertan
You can run this query to itendify the relations owned by the users you're not allowed to drop, just replace ('<your_user_name_to_check>', 'username2' ..'userN' ) with the your role names . Then, once you have identified the tables/objecst change the owner like this:
ALTER TABLE <table_name> OWNER TO <valid_user> ;
and try to drop the user again.
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'foreign table' END as "Type",
pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",
pg_catalog.array_to_string(ARRAY(
SELECT attname || E':\n ' || pg_catalog.array_to_string(attacl, E'\n ')
FROM pg_catalog.pg_attribute a
WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
), E'\n') AS "Column privileges",
pg_catalog.array_to_string(ARRAY(
SELECT polname
|| CASE WHEN polcmd != '*' THEN
E' (' || polcmd || E'):'
ELSE E':'
END
|| CASE WHEN polqual IS NOT NULL THEN
E'\n (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)
ELSE E''
END
|| CASE WHEN polwithcheck IS NOT NULL THEN
E'\n (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)
ELSE E''
END || CASE WHEN polroles <> '{0}' THEN
E'\n to: ' || pg_catalog.array_to_string(
ARRAY(
SELECT rolname
FROM pg_catalog.pg_roles
WHERE oid = ANY (polroles)
ORDER BY 1
), E', ')
ELSE E''
END
FROM pg_catalog.pg_policy pol
WHERE polrelid = c.oid), E'\n')
AS "Policies", ower_user.usename as "Object Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_user ower_user on (c.relowner = ower_user.usesysid)
WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')
AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)
AND ower_user.usename in ('<your_user_name_to_check>', 'username2');
On Fri, Aug 3, 2018 at 10:41 PM, Ertan Küçükoğlu <ertan.kucukoglu@1nar.com.tr> wrote:
Hello,
Using PostgreSQL 9.6.9 on Linux amd64 platform.
I have two users that I cannot drop from the system. I revoked all
permissions from tables, sequences, functions. They do not own any objects.
My log lines are as following:
2018-08-03 23:24:03.897 There are 2 users that needs to be removed from
system.
2018-08-03 23:24:03.897 Removing user previliges on tables: pars.test
2018-08-03 23:24:03.899 Removing user previliges on sequences: pars.test
2018-08-03 23:24:03.900 Removing user previliges on functions: pars.test
2018-08-03 23:24:03.900 Dropping user itself: pars.test
2018-08-03 23:24:03.901 Drop user failed: SQL Error: ERROR: role
"pars.test" cannot be dropped because some objects depend on it
AYRINTI: 1 object in database postgres
2018-08-03 23:24:03.901 Removing user previliges on tables: pars.test2
2018-08-03 23:24:03.902 Removing user previliges on sequences: pars.test2
2018-08-03 23:24:03.903 Removing user previliges on functions: pars.test2
2018-08-03 23:24:03.903 Dropping user itself: pars.test2
2018-08-03 23:24:03.904 Drop user failed: SQL Error: ERROR: role
"pars.test2" cannot be dropped because some objects depend on it
AYRINTI: 1 object in database postgres
I could not find any reference on postgres database for these users. I am
not very good on database administration.
Any help is appreciated.
Thanks & regards,
Ertan
> From: Alessandro Aste <alessandro.aste@gmail.com>
> Sent: Saturday, August 4, 2018 12:02 AM
> To: Ertan Küçükoğlu <ertan.kucukoglu@1nar.com.tr>
> Cc: pgsql-general@postgresql.org
> Subject: Re: Eror while dropping a user
>
> You can run this query to itendify the relations owned by the users you're not allowed to drop,
> just replace ('<your_user_name_to_check>', 'username2' ..'userN' ) with the your role names .
> Then, once you have identified the tables/objecst change the owner like this:
I tried to run it on "postgres" database and "pars" database, schemas on pars database, etc. All return empty results.
Thanks anyway.
Regards,
Ertan
> -----Original Message----- > From: Ertan Küçükoğlu <ertan.kucukoglu@1nar.com.tr> > Sent: Friday, August 3, 2018 11:42 PM > To: pgsql-general@postgresql.org > Subject: Eror while dropping a user > > Hello, > > Using PostgreSQL 9.6.9 on Linux amd64 platform. > > I have two users that I cannot drop from the system. I revoked all permissions from tables, sequences, functions. They do not own any objects. I found a table named "data" in postres database's public schema. Owner of that table is postgres user. That table do not have any relation with these users that I failed to drop. It is probably a mistakenly created test table. After I drop that table, these users dropped without any error. That is something weird to me. I am almost 100% sure that I did not give any GRANT for these users out of "pars" database. Alessandro's provided scipt did not return that table when I run it on postgres database and postgres.public schema. Anyway, I could clean up my users in the end. Thanks & regards, Ertan
=?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= <ertan.kucukoglu@1nar.com.tr> writes:
> Using PostgreSQL 9.6.9 on Linux amd64 platform.
> 2018-08-03 23:24:03.901 Drop user failed: SQL Error: ERROR: role
> "pars.test" cannot be dropped because some objects depend on it
> AYRINTI: 1 object in database postgres
I see you've resolved your problem, but for future reference, note that
you could have gotten a more detailed error message if you'd issued the
DROP USER while connected to the postgres database. When the command
is issued in database A, we can't see the details of what the user owns
in database B, only that there is something over there :-(
FWIW, I'm guessing that the issue was not object ownership per se,
but permissions granted on some object owned by someone else. Those
have to be revoked as well before a DROP USER will succeed.
You might also care to read up on DROP OWNED BY.
regards, tom lane