PostgreSQL

PostgreSQL ユーザへ権限付与

superuser 管理者権限の付与
alter user apluser with superuser
;
createdb データベース作成権限の付与
alter user apluser with createdb;
;
nocreatedb オプションでcreatedb権限を外す
alter user apluser with createdb;
;
createrole ロール(およびユーザ)作成権限の付与
alter user apluser with createrole;
;
nocreaterole オプションでcreaterole権限を外す
postgres=# alter user apluser with superuser;
ALTER ROLE
postgres=# alter user apluser with createdb;
ALTER ROLE

postgres=# alter user apluser with createrole;
ALTER ROLE
postgres=#
postgres=# select oid , r.rolname
postgres-# ,case when r.rolsuper = 'true' then '〇' else '-' end "Super"
postgres-# ,case when r.rolcreatedb ='true' then '〇' else '-' end "createDb"
postgres-# ,case when r.rolcreaterole ='true' then '〇' else '-' end "createRole"
postgres-# ,case when r.rolcanlogin ='true' then '〇' else '-' end "canlogin"
postgres-# ,case when s.search_path is null then current_setting('search_path') else s.search_path end "search_path"
postgres-# from pg_roles r
postgres-# left outer join (
postgres(#   select usename,search_path
postgres(#   from ( select usename,unnest(useconfig) search_path from pg_user ) t
postgres(#   where search_path like '%search%'
postgres(# ) s
postgres-# on r.rolname = s.usename
postgres-# where r.rolcanlogin ='true'
postgres-# order by r.oid
postgres-# ;
  oid  | rolname  | Super | createDb | createRole | canlogin |   search_path
-------+----------+-------+----------+------------+----------+-----------------
    10 | postgres | 〇    | 〇       | 〇         | 〇       | "$user", public
 16389 | esearch  | 〇    | 〇       | -         | 〇       | "$user", public
 16741 | apluser  | 〇    | 〇       | 〇         | 〇       | "$user", public
(3 行)


-- createdb権限を外す
--
postgres=# alter user apluser with nocreatedb;
ALTER ROLE
postgres=# select oid , r.rolname
postgres-# ,case when r.rolsuper = 'true' then '〇' else '-' end "Super"
postgres-# ,case when r.rolcreatedb ='true' then '〇' else '-' end "createDb"
postgres-# ,case when r.rolcreaterole ='true' then '〇' else '-' end "createRole"
postgres-# ,case when r.rolcanlogin ='true' then '〇' else '-' end "canlogin"
postgres-# ,case when s.search_path is null then current_setting('search_path') else s.search_path end "search_path"
postgres-# from pg_roles r
postgres-# left outer join (
postgres(#   select usename,search_path
postgres(#   from ( select usename,unnest(useconfig) search_path from pg_user ) t
postgres(#   where search_path like '%search%'
postgres(# ) s
postgres-# on r.rolname = s.usename
postgres-# where r.rolcanlogin ='true'
postgres-# order by r.oid
postgres-# ;
  oid  | rolname  | Super | createDb | createRole | canlogin |   search_path
-------+----------+-------+----------+------------+----------+-----------------
    10 | postgres | 〇    | 〇       | 〇         | 〇       | "$user", public
 16389 | esearch  | 〇    | 〇       | -         | 〇       | "$user", public
 16741 | apluser  | 〇    |        | 〇         | 〇       | "$user", public
(3 行)


postgres=#

権限付与の結果をユーザ一覧から確認する

スポンサーリンク
コピペで使う