[PGS9.2/15.6] 사용자 및 그룹 관리 - ver.dbrang

/***************************************************************************************
-- Title : [PGS9.2/15.6] 사용자 및 그룹 관리 - ver.dbrang
-- Reference : dbrang.tistory.com
-- Key word : postgresql user group role 사용자 그룹 롤
***************************************************************************************/

■ SUPERUSER 사용자 생성

최초 설치 직후 Oracle의 sys나 SQL Server의 sa와 같은 superuser 계정 생성

postgres$ psql psql (15.6) 도움말을 보려면 "help"를 입력하십시오. postgres=# CREATE USER sys WITH LOGIN PASSWORD "<패스워드>" SUPERUSER; postgres=# exit postgres$ postgres$ psql -U sys -h localhost -d postgres psql (15.6) 도움말을 보려면 "help"를 입력하십시오. postgres=# select * from pg_tablespace;  oid  |  spcname   | spcowner | spcacl | spcoptions ------+------------+----------+--------+------------  1663 | pg_default |       10 |        |  1664 | pg_global  |       10 |        | (2개 행)

 

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

-- 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;