[9.2] Manage User and Group

/********************************************************************************************
-- Title : [9.2] Manage User and Group
-- Reference : dbrang.tistory.com
-- Key word : postgresql user group role 사용자 그룹 롤
********************************************************************************************/

---------------

-- create GROUP

---------------

create role grp_ttt_1;

create GROUP grp_ttt_2;

/*role (without login option)= group*/

--------------

-- create user

--------------

-- create user

create role usr_ttt_11 with login password 'ttt';

create user usr_ttt_12 with login password 'ttt';

create role usr_ttt_21 with login password 'ttt';

create user usr_ttt_22 with login password 'ttt';

/*role (with login) = user*/

-- Show user

SELECT * FROM pg_shadow;

SELECT * FROM pg_user;

----------------------

-- grant user TO GROUP

----------------------

-- create GROUP

grant usr_ttt_11 TO grp_ttt_1;

grant usr_ttt_12 TO grp_ttt_1;

grant usr_ttt_21 TO grp_ttt_2;

grant usr_ttt_22 TO grp_ttt_2;

-- SELECT GROUP

SELECT * FROM pg_group;

-------------------------------------

-- system catalog for GROUP members

-------------------------------------

SELECT g.groname "grp_nm", g.grosysid "grp_id"

, u.usename "usr_nm", u.usesysid "user_id"     

FROM pg_user u

INNER JOIN pg_auth_members a

ON u.usesysid = a.roleid

INNER JOIN pg_group g

ON a.member = g.grosysid;