drop user
以下DDLにてユーザ（ロール）の削除はできるのですが、なにかと「しがらみ」があって簡単に削除できず苦労したので以下まとめました。
drop user udonman;
データベースのオーナになっているので削除できない
postgres=# drop role udonman;
ERROR: role "udonman" cannot be dropped because some objects depend on it
DETAIL: owner of database udondb
postgres=#
postgres=# select d.datname,u.usename as owner,pg_encoding_to_char(d.encoding),s.spcname
postgres-# ,pg_tablespace_location(d.dattablespace) as "Location"
postgres-# ,pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
postgres-# from pg_database d inner join pg_user u on d.datdba = u.usesysid
postgres-# left outer join pg_tablespace s on d.dattablespace = s.oid
postgres-# order by d.oid;
datname | owner | pg_encoding_to_char | spcname | Location | Access privileges
-----------+----------+---------------------+------------+----------+-----------------------
template1 | postgres | UTF8 | pg_default | | =c/postgres +
| | | | | postgres=CTc/postgres
template0 | postgres | UTF8 | pg_default | | =c/postgres +
| | | | | postgres=CTc/postgres
postgres | postgres | UTF8 | pg_default | |
db | postgres | UTF8 | pg_default | |
udondb | udonman | UTF8 | pg_default | |
(5 行)
postgres=# alter database udondb owner to postgres;
ALTER DATABASE
postgres=# drop user udonman;
DROP ROLE
postgres=#スキーマのオーナになっているので削除できない
postgres=# drop user udonman;
ERROR: role "udonman" cannot be dropped because some objects depend on it
DETAIL: owner of schema udonman
postgres=# select catalog_name,schema_name,schema_owner
postgres-# from information_schema.schemata order by schema_name;
catalog_name | schema_name | schema_owner
--------------+--------------------+-------------------
postgres | information_schema | postgres
postgres | pg_catalog | postgres
postgres | pg_toast | postgres
postgres | public | pg_database_owner
postgres | udonman | udonman
(5 行)
postgres=# alter schema udonman owner to postgres;
ALTER SCHEMA
postgres=# drop user udonman;
DROP ROLE
postgres=#オブジェクトのオーナになっているので削除できない
udondb=# drop user udonman;
ERROR: role "udonman" cannot be dropped because some objects depend on it
DETAIL: privileges for schema public
owner of table ex01
udondb=# select
udondb-# r.rolname as owner
udondb-# ,n.nspname as schema
udondb-# ,relname
udondb-# , case
udondb-# when c.relkind = 'i' then 'index'
udondb-# when c.relkind = 'm' then 'materialized view'
udondb-# when c.relkind = 'r' then 'table'
udondb-# when c.relkind = 'S' then 'sequence'
udondb-# when c.relkind = 't' then 'TOAST'
udondb-# when c.relkind = 'v' then 'view'
udondb-# when c.relkind = 'p' then 'partitioned table'
udondb-# when c.relkind = 'I' then 'partitioned index'
udondb-# when c.relkind = 'f' then 'foreign table'
udondb-# else concat(c.relkind,'')
udondb-# end relkind_dec
udondb-# from pg_class c inner join pg_namespace n
udondb-# on c.relnamespace = n.oid
udondb-# inner join pg_roles r
udondb-# on c.relowner = r.oid
udondb-# where 1=1
udondb-# and r.rolname ='udonman' -- オブジェクトオーナー
udondb-# order by owner,schema,relname
udondb-# ;
owner | schema | relname | relkind_dec
---------+--------+---------+-------------
udonman | public | ex01 | table
(1 行)
-- ex01テーブルのオーナになっているので削除できないようなので
-- テーブルのオーナを変更する
--
udondb=# alter table public.ex01 owner to postgres;
ALTER TABLE
--
-- あ、publicスキーマへの権限付与が残っている、、
--
udondb=# drop user udonman;
ERROR: role "udonman" cannot be dropped because some objects depend on it
DETAIL: privileges for schema public
--
-- スキーマへのアクセス権限を確認してみるとたしかにudonman
-- にも付与されています
--
udondb=#
udondb=# select n.nspname as "schema",pg_catalog.pg_get_userbyid(n.nspowner) as "owner"
udondb-# ,n.nspacl
udondb-# from pg_catalog.pg_namespace n
udondb-# where n.nspname !~ '^pg_' and n.nspname <> 'information_schema'
udondb-# order by 1;
schema | owner | nspacl
--------+-------------------+--------------------------------------------------------------------------------------------
public | pg_database_owner | {pg_database_owner=UC/pg_database_owner,=U/pg_database_owner,udonman=UC/pg_database_owner}(1 行)
-- revokeすると今度は削除できました
--
udondb=# revoke all on schema public from udonman;
REVOKE
udondb=# drop user udonman;
DROP ROLE
udondb=#
