ログイン中のユーザ
select current_user;
ロール
ロールとは、簡単に言えばユーザーのようなもので「データベース全体に対する権限」と「オブジェクトに対する権限」の 2 通りの権限を持ちます。
通常、データベースのログインに使用しているのもこのロールです。
なぜ、ユーザーといわず、あえてロールかというと、ロールにはユーザーとグループという概念が内包されているからです。
ユーザーもグループもどちらもロールとして作られるものですが、ユーザーはグループに属し、グループの権限を継承することができます。
なのでロールとユーザーはイコールではなく、ロールというくくりの中にユーザーとグループがいることになります。
SELECT * FROM pg_roles;
検索
ユーザ一覧
Query
select usesysid as user_id,
usename as username,
usecreatedb as db_create,
usesuper as is_superuser,
valuntil as password_expiration
from pg_user
order by user_id
Columns
user_id - id of the user
username - user name
db_create - flag indicating if user can create new databases
is_superuser - flag if user have superuser privileges
password_expiration - date of user password expiration
Rows
One row represents one user in the database
Scope of rows: all users in the database
Ordered by user id
ユーザが保持するテーブル権限
その１
SELECT
use.usename as subject,
nsp.nspname as namespace,
c.relname as item,
c.relkind as type,
use2.usename as owner,
c.relacl
FROM
pg_user use
cross join pg_class c
left join pg_namespace nsp on (c.relnamespace = nsp.oid)
left join pg_user use2 on (c.relowner = use2.usesysid)
WHERE
c.relowner = use.usesysid
and nsp.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
ORDER BY subject, namespace, item
その２
WITH
usrs as (SELECT * FROM pg_user),
objs as (
SELECT
schemaname, 't' AS obj_type,
tablename AS objectname,
schemaname + '.' + tablename AS fullobj
FROM pg_tables
WHERE schemaname not in ('pg_internal')
UNION
SELECT
schemaname, 'v' AS obj_type,
viewname AS objectname,
schemaname + '.' + viewname AS fullobj
FROM pg_views
WHERE schemaname NOT IN ('pg_internal')
),
query as (
SELECT
schemaname,
objectname,
usename,
HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'select') AS sel,
HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'insert') AS ins,
HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'update') AS upd,
HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'delete') AS del,
HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'references') AS ref
FROM objs, usrs
ORDER BY fullobj
)
SELECT * FROM query
WHERE (
sel = TRUE
OR ins = TRUE
OR upd = TRUE
OR del = TRUE
OR ref = TRUE
) AND schemaname != 'information_schema'
AND schemaname != 'pg_catalog'
;
その３ ユーザごとのスキーマ権限
SELECT
u.usename,
s.schemaname,
has_schema_privilege(u.usename,s.schemaname,'create') AS user_has_select_permission,
has_schema_privilege(u.usename,s.schemaname,'usage') AS user_has_usage_permission
FROM
pg_user u
CROSS JOIN
(SELECT DISTINCT schemaname FROM pg_tables) s
WHERE
s.schemaname NOT IN ('pg_catalog', 'pg_internal', 'information_schema')
-- u.usename = 'cm_user1'
order by
u.usename, s.schemaname
;
その４
SELECT
use.usename as subject,
nsp.nspname as namespace,
c.relname as item,
c.relkind as type,
use2.usename as owner,
c.relacl
FROM
pg_user use
cross join pg_class c
left join pg_namespace nsp on (c.relnamespace = nsp.oid)
left join pg_user use2 on (c.relowner = use2.usesysid)
WHERE
c.relowner = use.usesysid
and nsp.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
-- and nsp.nspname in('public','staging','xxxxxxxx') /** 任意のスキーマを指定 */
and type in('r','v')
ORDER BY
subject,namespace,item;
グループ
グループの作成
CREATE GROUP - Amazon Redshift
CREATE GROUP cmawsgroup;
ユーザーのグループへの追加
所定のグループへユーザーを追加する際はALTER GROUPコマンドを使います。
ALTER GROUP cmawsgroup ADD USER testuser1;
グループからのユーザーの削除
ユーザーの追加同様、削除もALTER GROUPコマンドを使います。
ALTER GROUP cmawsgroup DROP USER testuser3;
グループに所属するユーザーの一覧を確認する
SELECT * FROM pg_group;
グループに付与した権限
Since grants are per object you need to query permissions of all objects. Here you can see the relacl that lists permissions and the grant statement generated to grant those permissions:
select relacl ,
'grant ' || substring(
case when charindex('r',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',select ' else '' end
||case when charindex('w',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',update ' else '' end
||case when charindex('a',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',insert ' else '' end
||case when charindex('d',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',delete ' else '' end
||case when charindex('R',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',rule ' else '' end
||case when charindex('x',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',references ' else '' end
||case when charindex('t',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',trigger ' else '' end
||case when charindex('X',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',execute ' else '' end
||case when charindex('U',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',usage ' else '' end
||case when charindex('C',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',create ' else '' end
||case when charindex('T',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',temporary ' else '' end
, 2,10000)
|| ' on '||namespace||'.'||item ||' to "'||pu.groname||'";' as grantsql
from
(SELECT
use.usename as subject,
nsp.nspname as namespace,
c.relname as item,
c.relkind as type,
use2.usename as owner,
c.relacl
FROM
pg_user use
cross join pg_class c
left join pg_namespace nsp on (c.relnamespace = nsp.oid)
left join pg_user use2 on (c.relowner = use2.usesysid)
WHERE
c.relowner = use.usesysid
and nsp.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
ORDER BY
subject, namespace, item
) join pg_group pu on array_to_string(relacl, '|') like '%'||pu.groname||'%'
where relacl is not null
-- and pu.groname='group1'
order by 2
;