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