로아

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

일자 로우로 출력

SELECT TO_CHAR(TO_DATE('{시작일자}', 'YYYYMMDD')+LEVEL-1, 'YYYY-MM-DD')
  FROM DUAL
CONNECT BY LEVEL <= (TO_DATE('{종료일자}', 'YYYYMMDD')-TO_DATE('{시작일자}', 'YYYYMMDD')+1)

postgresql 주말을 제외한 한 달의 모든 요일 얻기

select dt 
from generate_series(date'2019-11-01', date'2019-11-30', interval '1' day) as t(dt)
where extract(dow from dt) between 1 and 5;

https://stackoverflow.com/questions/59489628/get-all-days-in-a-month-excluding-weekends-postgresql

오라클 덤프 스케줄

Windows

expdp system/oracle directory=DUMP dumpfile=full_expdp_%DAY%.dmp logfile=full_expdp_%DAY%.log full=y

forfiles -p "D:\oraclebackup\dump" -s -m ** /D -30 /C "cmd /c del @path"

Linux

find /data/backup/ -name "*.dmp" -mtime +7 -exec rm {} \;

find /data/backup/ -name "*.log" -mtime +7 -exec rm {} \;

export NLS_LANG=KOREAN_KOREA.KO16MSWIN949
exp DWS/DWS0330@DWS FILE=/data/backup/DWS_exp`date +%y%m%d`.dmp LOG=/data/backup/DWS_exp`date +%y%m%d`.log GRANTS=Y INDEXES=Y ROWS=Y CONSTRAINTS=Y TRIGGERS=Y COMPRESS=N DIRECT=N CONSISTENT=Y OWNER=DWS

Maria DB 유저 및 데이터 베이스 생성

-- 데이터 베이스 확인
SHOW DATABASES;

-- 데이터 베이스 생성
CREATE DATABASE 데이터베이스명;

-- 사용자 확인
SELECT HOST, USER, PASSWORD FROM USER;

-- 사용자 계정 생성 %는 어디서나 접속 가능
CREATE USER '아이디'@'%' IDENTIFIED BY '비밀번호';

-- 사용자 권한 주기
GRANT ALL PRIVILEGES ON 데이터베이스.* TO '아이디'@'%';


-- 새로고침
FLUSH PRIVILEGES;

-- 사용자 계정 삭제 '사용자'@'host'
DROP USER [사용자]@[호스트];

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