이제 설치된 리눅스 postgreSQL에 어플리케이션으로 접속하여 DB를 조회해 볼 예정인데 일단 손쉬운 node.js로 구현해보려 한다.
일전에 로컬 DB에 user를 만들었을 당시엔 편의상 pgAdmin에서 생성( https://hippalus.tistory.com/648 )했는데 이번엔 리눅스 서버에 설치된 postgreSQL에 계정을 psql을 이용해 생성해보고 어플리케이션에서 접근할 테이블과 관련한 권한 설정도 진행해보려 한다. 지금은 tester 라는 계정을 만들며 작업을 진행하겠다. 이미 앞전 포스팅에서 tester와 testdb를 만든 상태라면 2번과 3번은 패싱~
1. 리눅스 터미널에 su 계정으로 접속되어 있단 가정하에 먼저 su계정에서 postgres계정으로 전환하자. su - postgres psql을 입력 후 엔터를 친다. 2.커맨드 상태에서 CREATE USER tester WITH PASSWORD '적당한 암호'; 엔터를 치면 CREATE ROLE 이라고 나와야 한다.
4. 일단 여기까지 기본 준비는 끝이고 이제 본격적으로 어플리케이션에서 tester의 설정을 만져보려한다. tester 계정은 단순히 어플리케이션단에서 SELECT, INSERT, UPDATE, DELETE 정도의 권한만 부여받은채로 실행될 계정이다. 이용자가 사용할 계정인데 SUPER USER 권한이 부여된채로 노출됐다가 무슨 일이 생길지 모르기 때문이다.
내가 사용할 DB는 testdb이므로 testdb로 실행해야 하므로 testdb로 사용 DB를 바꿔준다. \c testdb 를 입력하고 엔터 그럼 이렇게 뜰 것이다.
5. 이제 해야 할 일은 이 testdb에서 table이 생성될 때 마다 자동으로 programuser 계정으로 SELECT, INSERT, UPDATE, DELETE 권한이 부여되게 만들어야 한다.
'public' 스키마의 모든 테이블에 대한 기본 권한 부여 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO tester;
만약 이전에 생성한 테이블들이 있다면 아래처럼 한 번 더 실행해준다. 'public' 스키마의 모든 테이블에 대해 'appuser'에게 권한 부여 GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TOtester;
참고로 취소는 다음과 같다. 'public' 스키마의 모든 테이블에 대한 기본 권한 취소 ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLES FROM tester;
'public' 스키마의 모든 테이블에 대한 권한 취소 REVOKE SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM tester;
6. 테이블이 만들어질 때마다 tester계정에 해당 테이블을 SELECT, INSERT, UPDATE, DELETE권한이 자동으로 부여되게 만들었으니 board_tbl을 만들자. CREATE TABLE IF NOT EXISTS public.board_tbl ( b_idx integer NOT NULL, b_title text COLLATE pg_catalog."default" NOT NULL, b_content text COLLATE pg_catalog."default" NOT NULL, CONSTRAINT board_tbl_pkey PRIMARY KEY (b_idx) );
7. 이제 기본 DB의 user작업은 끝났다. node.js로 board_tbl을 select해보겠다. insert 문을 이용해 데이터를 넣어보자. insert into board_tbl (b_idx, b_title, b_content) values (1, 'title', 'content'); insert into board_tbl (b_idx, b_title, b_content) values (2, 'title2', 'content2'); insert into board_tbl (b_idx, b_title, b_content) values (3, 'title3', 'content3');
8. 드디어 node.js 프로그래밍 (node.js설치는 검색해서 알아서~)
node.js에서 postgreSQL을 접근하려면 pg 모듈을 설치해야 한다. npm i pg를 터미널에서 입력하면 잘 설치 된다.
그다음 실제 소스 구현이다. app.js라는 파일을 만들고 아래와 같이 타이핑하자.
const { Client } =require('pg');
// PostgreSQL 연결 정보 설정
constclient=newClient({
user:'tester', //아까 생성한 어플리케이션용 계정
host:'000.000.000.000', //postgreSQL이 설치된 서버의 ip
database:'testdb', //생성한 DB명
password:'적당한암호', //어플리케이션용 계정의 비밀번호
port:5432, // PostgreSQL 기본 포트 번호
});
// PostgreSQL 데이터베이스에 연결
client.connect();
// board_tbl 테이블에서 데이터 조회
client.query('SELECT * FROM board_tbl', (err, res) => {
어떤 버전을 다운받을지 플랫폼은 뭔지 아키텍쳐는 뭔지 선택하면 설치 스크립트가 나오는데 난 최신 버전 16과 CentOS7 선택했고 아키텍쳐 확인은 리눅스(centos 7)에서 아래 명령어로 확인한다. uname -m 난 x86_64가 나왔다. 그럼 아래와 같은 스크립트가 나온다. 복사한다.
일단 기초 설치는 이걸로 끝이다. 이제 su계정에서 postgres 계정으로 전환하고 나머지 설정을 손보려한다.
su - postgres pwd를 실행해보면 현재 위치가 /var/lib/pgsql로 나타난다.
원격접속 설정을 위해 /var/lib/pgsql/15/data/pg_hba.conf 를 vi에디터로 편집한다. vi /var/lib/pgsql/15/data/pg_hba.conf 아래로 스크롤 해보면 local connection 설정부분이 나온다.
# IPv4 local connections: host all all 127.0.0.1/32 scram-sha-256 요 아래에 0.0.0.0/0... 을 추가한다. host all all 0.0.0.0/0 scram-sha-256
다음은 postgresql.conf파일이다. 마찬가지로 vi에디터로 편집해주자. vi /var/lib/pgsql/15/data/postgresql.conf
listen_addresses를 보면 localhost가 있는데 이를 *로 바꿔준다. 저장하고 나와서 exit명령어를 입력하여 su계정으로 전환하자. 환경 설정이 바뀌었으니 su계정으로 postgreSQL을 재시작해주자. exit를 입력하면 다시 su 상태가 된다.
systemctl restart postgresql-15
참고로 처음 제공된 스크립트들 중 systemctl enable postgresql-15는 OS가 기동될때 자동으로 postgresql 서버가 실행되도록 하는 설정이다. 실행하면 이런 처리 결과를 확인할 수 있게 된다.
어찌 저찌 외부 접속까지는 다 됐는데 그럼 남은건 외부에서 어떤 계정을 사용해서 접근하게 만들지를 설정할 단계다. 다시 postgres계정으로 전환하자 su - postgres
그리고 psql을 실행 후 postgres=# 상태가 확인된다면 \du 명령어를 입력해 사용자명, 권한, 소유한 데이터베이스 등의 정보를 표시해 본다.
나님이 슈퍼유저란 뜻이다. 당연히 계정 생성이 가능하다. 외부 접속을 하려면 새로운 db user 를 생성하거나 postgres(관리자)의 password 를 설정해야 한다. 난 둘 다 하겠다.
먼저 관리자 password 설정 alter user postgres with password '원하는비밀번호입력'; ALTER ROLE 라고 뜨면 성공이다.
다음은 외부 접속용 아이디 생성 create user tester with password ' 원하는비밀번호입력 '; CREATE ROLE 라고 뜨면 성공이다.
DB도 만들어주자 createdb testdb; 간단하다.
이제 내 PC(맥)에서 접속을 해보자. (솔직히 이 부분에서 몇 시간을 소비해버렸다. 난 분명히 postgresql.conf 파일의 설정을 외부 접속 가능하도록 위에서 진행했는데... 한참 삽질 하다보니 바뀌어 있지 않았다. -_-)
pgAdmin4를 실행해서 Servers에서 마우스 오른쪽을 눌러보니 Register가 보인다. 그 옆엔 Server가 있으니 이를 선택하자
그럼 Register 새창이 뜨고 여기에 Name에 내가 원하는 식별 이름을 적어주자.
그 옆 탭은 Connection 당연히 postgreSQL이 설치된 ip를 host name/address에 적어주고 port는 기본 port인 5432 db는 내가 생성한 db인 testdb username도 내가 일전에 생성한 userid password도 아까 user생성할 때 함께 적었던 그 비번을 넣으면 된다. 그리고 Save
생각해보자. DB에 프로그램이 접속해서 CRUD 행위가 가능하려면 이용자가 있어야 하는 법 그런데 난 postgreSQL을 처음 설치할 때 이용자는 만들었는데 프로그램이 접속해서 사용하는 이용자는 만든 기억이 없다.
그러니 일단 만들자. Databases아래에 Login/Group Roles가 있다. 여기서 또 마우스 우측 버튼을 누르고 Create > Login/Group Roles를 선택
그러면 늘 그러하듯 새창이 뜬다. 이름을 지정해주자 난 programuser라고 하련다.
우측탭으로 이동하면 Password를 입력할 수 있다. 그 아래 expires는 그냥 놔두면 never expire라 하니 건드리지 말아야겠다. Connection limit는 -1인걸로 미루어 이게 현명한 처사 같다. 접속 제한이 없단 말 이겠지. 웹같은 환경에선 다수가 동시 접속이 일어날테니 -1로 놔두는게 맞지 싶다.
Save하면 끝. 그런데 이렇게 하고 프로그램 구현을 하면 제대로 안 된다. 이유는 각 테이블의 권한이 없기 때문이다. 따라서 이번 포스팅은 그냥 pgAdmin으로 계정 생성하는 목적 정도로만 공유하고 다음 포스팅에서 제대로 어플리케이션에서 사용 될 계정을 만들고 설정도 다뤄보려 한다.
검색해보니 관리자권한으로 실행을 해야 한다느니 cmd창에서 net user administrator로 실행을 하고 runas /user:administrator postgresql-16.2-1-windows-x64.exe 어쩌고 저쩌고 -_- 뭔짓을 해도 내 PC에는 설치가 안됐다.
