로아

Postgresql SSL 적용

[postgresql.conf] 

ssl = on

[pg_hba.conf] 

hostnossl : ssl 미적용 접근 설정

hostssl : ssl 적용 접근 설정

아래설정 : localhost는 ssl 없이 접속, 그외에 ssl 적용

hostnossl  all         all        127.0.0.1/32       trust
hostssl    all         all        127.0.0.1/32       reject
hostssl    all         all        192.168.2.0/24     scram-sha-256

[ssl 인증서파일] postgresql.conf 같은 폴더에 복사

server.crt
server.key

[postgresql 재시작]

sudo systemctl restart postgresql

[연결 확인] 클라이언트에서 접속

$ psql -d "dbname=postgres sslmode=require" -h postgresql.example.com -U postgres

Password for user mctdba:
psql (15.2, server 14.6)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
postgres=#

[연결 확인] postgresql 서버

$ psql
postgres=# SELECT ssl.pid, usename, datname, ssl, ssl.version, ssl.cipher, ssl.bits, client_addr FROM pg_catalog.pg_stat_ssl ssl, pg_catalog.pg_stat_activity activity WHERE ssl.pid = activity.pid;
  pid  | usename  |    datname     | ssl | version |           cipher            | bits | client_addr
-------+----------+----------------+-----+---------+-----------------------------+------+--------------
 10728 | mctdba   | RealOffice_MCT | t   | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 |  256 | 127.0.0.1
  8468 | mctdba   | RealOffice_MCT | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384      |  256 | 접속자IP

[적용 후 jdbc 설정]

jdbc:postgresql://localhost:5432/postgres?sslmode=require

Postgresql 15 user create 유저생성

postgresql 15버전에서 기본 유저 및 데이터 베이스를 생성했는데 public 스키마에 접근이 불가능해서 구글링해보니

15버전부터는 public 스키마에 권한을 따로 줘야 했다.

CREATE DATABASE testuser;

CREATE USER testuser WITH ENCRYPTED PASSWORD 'password';

GRANT ALL PRIVILEGES ON DATABASE testuser TO testuser;

GRANT ALL ON SCHEMA public TO testuser;

GRANT ALL ON DATABASE testuser TO testuser;

ALTER DATABASE testuser OWNER TO testuser;

GRANT USAGE, CREATE ON SCHEMA public TO testuser;

https://stackoverflow.com/questions/74110708/postgres-15-permission-denied-for-schema-public

Postgresql 콤마로 구분된 데이터 열을 행으로 변환

SQL

with temp01 as (
	select 1 as  idx
		, '가,나,다' as col01
	union
	select 2 as  idx
		, '라,마,바' as col01
)
select idx,
       unnest(string_to_array(col01, ',')) as col01
from temp01
order by idx asc;

출처 : https://medium.com/swlh/three-routes-convert-comma-separated-column-to-rows-c17c85079ecf