히파루스의 솔직한 여행 이야기

앞서 리눅스 centos에 postgreSQL을 설치해 보았다.
https://hippalus.tistory.com/649

이제 설치된 리눅스 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 이라고 나와야 한다.

3. testdb라는 테스트용 DB를 만들어본다.(이미 만든 상태라면 pass)
CREATE DATABASE testdb;

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

참고로 취소는 다음과 같다.
'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 연결 정보 설정

const client = new Client({

  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) => {

  if (err) {

    console.error('error :', err);

    return;

  }

  console.log('result :');

  for (let row of res.rows) {

    console.log(row);

  }

  // 데이터베이스 연결 종료

  client.end();

});

저장 후 실행
node app.js 엔터

잘~ 나온다.

만약 정보가 틀리거나 권한이 없거나 그러면 이런 오류를 발견하게 될 것이다.

https://www.postgresql.org/download/

일단 리눅스를 선택한다.
그럼 바로 아래에 OS선택이 뜨는데 난 centos7이니 Red Hat을 선택했다.

그럼 이 페이지로 이동하는데
https://www.postgresql.org/download/linux/redhat/
조금만 스크롤 하면 이런 화면이 나온다.

어떤 버전을 다운받을지 플랫폼은 뭔지 아키텍쳐는 뭔지 선택하면 설치 스크립트가 나오는데
난 최신 버전 16과 CentOS7 선택했고 아키텍쳐 확인은 리눅스(centos 7)에서 아래 명령어로 확인한다.
uname -m
난 x86_64가 나왔다.
그럼 아래와 같은 스크립트가 나온다. 복사한다.

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql16-server
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
sudo systemctl enable postgresql-16
sudo systemctl start postgresql-16

리눅스에서 su권한을 획득한 상태에서 첫번째 스크립트 yum을 실행한다.
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

일단 성공.

두번째 스크립트 실행
yum install -y postgresql16-server

Error가 뜬다. 썩을
yum list | grep postgresql16 명령어로 postgresql16을 설치할 수 있는패키지 항목을 조회해 보았으나 아무것도 없었다.

yum list | grep postgresql15명령어로 확인해보니 뜬다.

계획을 바꿔서 15를 설치해보겠다.
yum install -y postgresql15-server

성공

postgresql15-server 설치가 완료됬다면 /usr/pgsql-15 경로에서 확인이 가능하다.

데이터베이스 setup을 진행한다.
/usr/pgsql-15/bin/postgresql-15-setup initdb
cd /usr/pgsql-15/bin/명령어로 이동해서 실행을 해도 된다.

PostgreSQL 기동
systemctl start postgresql-15
(재기동은 restart를 쓰면된다.)

일단 기초 설치는 이걸로 끝이다.
이제 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

그럼 생성한 user를 이용해 외부 DB에 최종적으로 연결된 모습을 확인할 수 있다.

다음엔 생성된 계정 tester를 이용해 testdb에 접근하여 데이터를 조회해오는 샘플 프로젝트를 진행해 볼 예정이다.
https://hippalus.tistory.com/650

일단 내 상황은 이렇다.
사용해본 DB는 MSSQL을 15년 이상 주력사였고 최근 Oracle(과거 SI 잠깐 했을 당시에 2년 정도 토드사용해본게 전부였지만 최근 다시 사용) 그 외 잡다한 MySql, MSACCESS(ㅋㅋㅋ), DB같지도 않은 파라독스(S모 카드사에서 처음 접하고 개깜놀) 대충 이렇다.

이 상황에서 처음 PostgreSQL을 설치해보았다.
그리고 첫 실행을 하고자 설치된 폴더로 이동해보니 딱 저렇게 보인다.
느낌적으로 pgAdmin 4라는 툴이 MSSQL의 SQL Server Management Studio스러워 보인다.
SQL Shell은 아마 커맨드 라인으로 실행해주는 paAdmin 4일듯 싶다.

pgAdmin 4를 냅다 실행해본다.

음 역시나가 역시다.
탭 영역을 보니 대시보드가 있고 그 옆에 프로퍼티, 시퀄 등등등이 있다. 
눈깔을 좌측 트리로 이동해보자 Servers가 있다.

MSSQL처럼 UI가 마음에 드는구나.
트리를 펼쳐서 PostgreSQL 16을 눌러보자.
팝업윈도우가 뜬다.

아까 설치할 때 기록한 비번을 넣어준다.

이러니 Databases와 Roles, Tablespaces 등등 기본 시스템 DB쪽으로 추정된다.

자 그럼 이제 내 디비를 만들어봐야겠제?
PostgreSQL 16에서 마우스 오른쪽을 눌러본다. Create가 있다 그 옆에 Database가 있구나.
정말 직관적이다.(MSSQL 이용자라면 너무 쉽다.)
있어야 할 곳에 기능이 있다. 그게 MS의 최대 장점 아닌가?

Database를 선택하니 역시나 창이 하나 뜬다.
뻔하다. DB명 만들어주고 저장하면 그만이다.
그 옆 Definition, Security 등을 눌러봤지만 뭘 하는건진 아직 모르겠다. 
일단 DB나 만들자. Save 전에 SQL탭이 눈에 들어온다.
뻔하다. 내가 만드는 액션의 SQL문이겠지. 그리고 저 SQL문은 SQL Shell 프로그램에서 동작시키거나 SQL management처럼 쿼리분석기에서 실행시킬 수 있을것이다.
그냥 저런것도 보여주는구나 하고 넘기자. SAVE

Save를 하고 나니 내 첫 PostgreSQL DB가 생성된게 보인다.

그럼 테이블을 만들어 보자.
테이블이야 뭐 뻔하다 Schemas에 있겠지
역시나 Tables가 있다.
만들려면? 뻔하지 마우스 오른쪽 눌러보자.
Create가 나온다. 그리고 Table

그러면 Database 처럼 Table 생성창이 뜬다.
이름은 게시판이 제일 만만하니 board_tbl이라 정해준다.
그다음은? 당연히 컬럼이다. General 옆에 Columns가 보인다. 그거 누른다.

난 매우 간단하게 기준키 필드는 integer, 제목은 varchar, 내용은 text나 varchar로 만들고자 한다.
그런데 varchar가 안보인다.
이건 좀 당황스럽다.

chatGPT센세에게 물어보고 싶지만 내가 쓰는 버전은 3.5버전이므로 최신 정보는 알 수 없을 것 같다.
claude.ai 티쳐에게 물어보자.

16버전에선 varchar대신 text를 사용하는게 좋단다.
초기 버전엔 varchar를 제공했지만 요즘은 그냥 닥치고 text쓰란다.
그럼 varchar와 text가 아무런 차이점이 없다란 말인데... 흠..일단 믿어보자.

그런데 text[] 이건 뭐지? 배열인데...
또 claude.ai 티쳐에게 퀘스쳔

{'태그1', '태그2'...}이런식을 저장하는 자료형이란다.
대충 PostgreSQL이 일반적인 RDBMS가 아니라는말은 익히 들어왔었던지라 지리정보나 그따위 정보에 특화된 필드가 아닐까 추측해본다.
조금 구글링 해보니 JSON형도 있던데... 음... 그렇군 
옥히 도키

b_idx는 integer로 PK로 설정하고 다른 제목과 내용 역시 Not NULL로 설정한다. 기본값은 없다.

Save

저장하고 나니 역시나 좌측 트리 영역에 테이블과 필드가 잘 나타난다.

이제 데이터를 insert해보자
ANSI문법은 언제나 통하니 간단하게 insert문을 실행해보자.
드래그 후 F5(execute script)를 실행하니 error가 뜬다.

이런.. b_title인데 필드명 오타를 냈나보다.
보자.. 테이블 스키마 수정은... 뭐 뻔하지 마찬가지로 테이블의 board_tbl에서 마우스 우측 눌러 Properties를 누르면 되겠지

역시나다. b_titile 오타 i를 하나 제거하자
Save

저장하기 전 SQL문을 구경해보자
좀 특이하다. 이게 ANSI문법인가.
MSSQL과는 좀 다르다.
ALTER TABLE IF EXISTS.... RENAME 뭐 그런가보다.

다시 SQL문을 실행하자.
그 전에 나는 그냥 select 문도 추가하고 F5를 실행하였는데 오류가 난다.
아마 오라클처럼 ;를 넣어줘야 할 것 같다.

정상적으로 insert, select가 실행된다.
다시 한 번 실행해보면 b_idx값이 똑같이 1로 넣으니 당연히 duplicate 오류가 뜬다.

2로 바꾸고 실행하면 잘 넣어지고 잘 보여진다.

일단 첫인상.
매우 직관적이다.
MSSQL을 사용해봤다면 토드를 사용할 때와 다르게 친숙한 느낌을 받을 수 있다.
여기에 뭐랄까 script같은 느낌도 들고...
조만간 회사 오라클 DB를 PostgreSQL로 바꾸게 될 지 모르겠다.
대공사겠지만.

Download the installer 링크를 누르면 아래 페이지로 이동된다.

https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

다운로드 페이지에서 자신에게 맞는 버전을 다운받으면 된다.
난 일전에 windows용을 다운받았다가 개피를 보았으므로 맥용으로 다운받았다.

설치하기는 매우 쉽다.

계속 Next만 누르면 그만이다.

계속 Next

계속 Next

계속 Next

여기선 암호만 넣어주고 계속 Next

포트도 5432그대로 입력된 상태에서 계속 Next

난 UTF-8 KR을 선택했다.
계속 Next

계속 Next

그럼 끝이다.

다운로드 페이지로 이동한다.
https://www.postgresql.org/download/windows/

Download the installer 링크를 누르면 아래 페이지로 이동된다.

https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

여기서 원하는 인스톨러를 다운받음 된다.

난 16.2 Windows X86-64로 다운받았다.
그리고 실행
그리고 에러 ㅋㅋㅋㅋ

검색해보니 관리자권한으로 실행을 해야 한다느니 cmd창에서 net user administrator로 실행을 하고 runas /user:administrator postgresql-16.2-1-windows-x64.exe 어쩌고 저쩌고 -_-
뭔짓을 해도 내 PC에는 설치가 안됐다.

더 검색해보니 하위버전을 설치해야 한다라는것 같아서
하나 하나씩 다운 받아봤지만 다 동일했고
누구는 이 링크로 다운 받아 설치하면 동작한다고 하는데 실제로 인스톨되는걸 확인했다.
https://get.enterprisedb.com/postgresql/postgresql-11.2-1-windows-x64.exe

그런데 버전도 마음에 안 들고 영 못마땅해서 난 그냥 맥용 16.2 버전 다운받아 맥에다 설치해버렸다.