개발 조각

비밀번호 테이블

• "user" 부분을 따옴표 없이 했을 때 에러가 난다면 따옴표를 붙이고 해보는 것도 좋다.
• 외래키는 부모테이블의 기본키로만 연결할 수 있고 아닐 경우 외래키 설정이 안 된다. 그 기본키의 컬럼타입과도 같아야한다.
• ON DELETE CASCADE ON UPDATE CASCADE : 만약 이 조건을 지정하지 않으면 제약 검증을 실패할 경우 삭제 또는 수정이 되지 않는다. 부모 테이블에서 row 를 삭제/수정할 경우 연결된 자식 테이블의 row 가 함께 삭제/수정된다. 연결된 데이터를 한 번에 삭제/수정할 수 있어 데이터의 관리가 편리해지고 일관성을 유지할 수 있다.

CREATE TABLE password (
    password_id SERIAL PRIMARY KEY,
    user_no INTEGER UNIQUE NOT NULL,
    salt VARCHAR(128),
    CONSTRAINT password_fk FOREIGN KEY(user_no) REFERENCES "user"(user_no) ON DELETE CASCADE ON UPDATE CASCADE
);

테이블 수정하기 (ALTER)

• 컬럼 타입 수정하기

ALTER TABLE profile ALTER COLUMN user_no TYPE INTEGER USING user_no::integer;

• 컬럼 명 수정하기

ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

• 컬럼 추가하기

ALTER TABLE user ADD COLUMN user_name INTEGER NOT NULL UNIQUE;

• 제약조건 추가하기
• 외래키

ALTER TABLE child_table ADD CONSTRAINT constraint_name FOREIGN KEY (fk_columns) REFERENCES "parent_table" (parent_key_columns);

• 기본키

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 PRIMARY KEY(컬럼명);

• NULL ? NOT NULL ?

ALTER TABLE table_name ALTER COLUMN column_name [SET NOT NULL| DROP NOT NULL];

• 테이블명 수정하기

ALTER TABLE table_name RENAME TO new_table_name;

출처

https://velog.io/@eensungkim/ON-DELETE-CASCADE-feat.-row-%ED%95%9C-%EB%B2%88%EC%97%90-%EC%A7%80%EC%9A%B0%EB%8A%94-%EB%B0%A9%EB%B2%95-TIL-78%EC%9D%BC%EC%B0%A8

http://www.gisdeveloper.co.kr/?p=11993

https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-alter-table/

PostgreSQL을 사용하여 비밀번호를 안전하게 저장하는 방법

1. PostgreSQL에서 pgcrypto확장 기능에는 비밀번호 해싱에 필요한 내장 확장 기능이 있습니다.

확장 프로그램을 활성화하십시오:

CREATE EXTENSION pgcrypto;

2. 데이터 삽입

INSERT INTO user_account (user_name, password_hash) 
VALUES ('user1', crypt('user1_password', gen_salt('md5'))) ;

설명)

get_salt(type) : type에 따라서 해시를 한다.

• des- 데이터 암호화 표준(DES) 알고리즘용
• xdes- 확장 DES 알고리즘의 경우
• md5- MD5 메시지 다이제스트 알고리즘용
• bf- Blowfish 알고리즘의 경우

crypt('비밀번호',해시함수) : '비밀번호'를 해시함수로 해싱해서 바꾼다.

비밀번호 확인하기

SELECT (password_hash = crypt('user1의 비밀번호 입력', password_hash)) 
AS password_match 
FROM user_account 
WHERE user_name = 'user1' ;

설명)

crypt('user1의 비밀번호 입력', password_hash) 

• 비밀번호가 맞을 경우

• 비밀번호가 틀릴 경우

• 'user1의 비밀번호 입력'와 password_hash는 같은 해시값을 만든다.
  • 1번과 4번 같은 문자열에 대한 같은 타입의 해시인데도 값이 다르다.
  • 3번 이전에 2번을 했는데도 1번 해시값으로 검사했을 때 같다고 나온다.
  • 1번과 2번에서 다른 타입의 해시 알고리즘을 사용하여 다른 값이 나온다.

nodedb=# SELECT crypt('supersecurepassword', gen_salt('bf')) ;
                            crypt
--------------------------------------------------------------
 $2a$06$.OSDtWHWtAtNAnnIH/vz3.RfysAOn2DF.YU/fBFBqdXPPxvE/O2M.
(1개 행)


nodedb=# SELECT crypt('supersecurepassword', gen_salt('xdes')) ;
        crypt
----------------------
 _J9..V0Fk2jtgJXri9Cs
(1개 행)


nodedb=# SELECT crypt('supersecurepassword', '$2a$06$.OSDtWHWtAtNAnnIH/vz3.RfysAOn2DF.YU/fBFBqdXPPxvE/O2M.');
                            crypt
--------------------------------------------------------------
 $2a$06$.OSDtWHWtAtNAnnIH/vz3.RfysAOn2DF.YU/fBFBqdXPPxvE/O2M.
(1개 행)

nodedb=# SELECT crypt('supersecurepassword', gen_salt('bf')) ;
                          crypt
-------------------------------------------------------------
$2a$06$n9ZD0NkhjKL0tEOXjsAtoOBcZIHs9yxlxdZEqa7slBluyCMDd27/q

비밀번호 수정하기

UPDATE user_account 
SET password_hash = crypt('수정한 비밀번호 입력', gen_salt('md5')) 
WHERE user_name = 'user1' ;

출처

https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-create-table/

새 테이블 만들기

CREATE TABLE [IF NOT EXISTS] table_name (
   column1 datatype(length) column_contraint,
   column2 datatype(length) column_contraint,
   table_constraints
);

[IF NOT EXISTS] 기존 테이블에 같은 이름의 테이블이 있으면 알림을 해주는 역할

제약조건

• NOT NULL  – 열의 값이 NULL이 될 수 없도록 합니다.
• UNIQUE – 동일한 테이블 내의 행 전체에서 고유한 열의 값을 보장합니다.
• PRIMARY KEY – 기본 키 열은 테이블의 행을 고유하게 식별합니다. 테이블에는 기본 키가 하나만 있을 수 있습니다. 기본 키 제약 조건을 사용하면 테이블의 기본 키를 정의할 수 있습니다.
• CHECK – CHECK제약 조건은 데이터가 부울 표현식을 충족해야 함을 보장합니다.
• FOREIGN KEY – 테이블의 열 또는 열 그룹의 값이 다른 테이블의 열 또는 열 그룹에 있는지 확인합니다. 기본 키와 달리 테이블에는 많은 외래 키가 있을 수 있습니다.

테이블 만들기

CREATE TABLE user (
	user_no serial PRIMARY KEY,
	user_name VARCHAR ( 50 ) UNIQUE NOT NULL,
);

CREATE TABLE profile (
	profile_id serial PRIMARY KEY,
	user_no INTEGER UNIQUE NOT NULL,
	join_date DATE NOT NULL,
);

데이터베이스 부분 정리

1. postgreSQL 설치하기

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

2. 회원가입과 로그인에 필요한 데이터베이스 테이블

https://rastalion.me/%ED%9A%8C%EC%9B%90-%EA%B0%80%EC%9E%85-%EB%B0%8F-%EB%A1%9C%EA%B7%B8%EC%9D%B8%EC%9D%84-%EC%9C%84%ED%95%9C-%ED%85%8C%EC%9D%B4%EB%B8%94-%EC%84%A4%EA%B3%84/

위 링크에 나온 자료들을 참고하여 정리

3. 데이터베이스 접속하기

같이 설치된 SQL Shell (psql) 들어가서

enter -> enter -> enter -> enter -> 설치할 때 설정한 암호, enter

4. 새로운 데이터 베이스 생성

postgres=# create database nodedb;
postgres=# \c nodedb

5. 테이블 생성하기

https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-create-table/