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.kucuko...@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
>
>
>
>
>
>
