로아

전자정부 프로젝트에서 postgresql을 연결하는데 아래와 같은 에러가 발생하였다.

org.postgresql.util.PSQLException: The server requested SCRAM-based authentication, but no password was provided.

에러를 구글링해보니 스키마를 파라미터로 추가해넣어라고 하니 해겼되었다.

#이전코드
Globals.postgres.Url=jdbc:postgresql://192.168.0.100:5432/proj01

#변환코드
Globals.postgres.Url=jdbc:postgresql://192.168.0.100:5432/proj01?currentSchema=proj01

https://stackoverflow.com/questions/34783370/psqlexception-password-based-authentication

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

postgres 1분이상 실행중인 쿼리 검색

운영중에 DB가 느려지고 DB서버 CPU가 90%까지 치고 오르는 현상이 발생했다.

원인을 찾고자 느린 쿼리를 찾아서 튜닝을 하려고 현재 1분이상 실행중인 쿼리를 검색하는 SQL이다.

SELECT current_timestamp - query_start AS runtime, datname, usename, query, pid
FROM pg_stat_activity
WHERE state = 'active' AND current_timestamp - query_start > '30 sec'
ORDER BY 1 DESC;

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

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