Re: Eror while dropping a user
| От | Alessandro Aste |
|---|---|
| Тема | Re: Eror while dropping a user |
| Дата | |
| Msg-id | CAM9F+O3TbdWUzVcp69imx3NG-ypnSrDc8fHrTDG6E6WZx6xUgg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Eror while dropping a user (Ertan Küçükoğlu <ertan.kucukoglu@1nar.com.tr>) |
| Ответы |
RE: Eror while dropping a user
|
| Список | pgsql-general |
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
В списке pgsql-general по дате отправления: