PostgreSQL

PostgreSQL ユーザの削除(ロールの削除)

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=#

DBのオーナを調べる方法

スキーマのオーナを調べる方法

オブジェクトのオーナを調べる方法

スキーマへのアクセス権限を調べる方法

スポンサーリンク