oracle cloud

방화벽

네트워크 -> 가상 클라우드 네트워크(VCN) -> 인스턴스가 속한 VCN의 보안(목록)탭 -> 인바운드 규칙 추가

postgresql db

설치

sudo systemctl stop postgresql
sudo apt remove --purge postgresql-12 postgresql-client-12 postgresql-common postgresql-contrib
sudo apt autoremove
sudo rm -rf /var/lib/postgresql/12
sudo rm -rf /etc/postgresql/12

echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo apt update
sudo apt install postgresql-17 postgresql-client-17

sudo systemctl status postgresql
psql --version

외부접속 (툴 사용)

sudo -u postgres psql # 사용자명 postgres
ALTER USER postgres WITH PASSWORD 'your_secure_password';

localhost:5432으로 접속 설정 (postgres // 계정비밀번호)
ssh 터널링 (도메인이름 // 포트22 // 서버계정이름 // private key 경로)

외부접속 (앱 사용)

# pip install sshtunnel
from fastapi import FastAPI, Depends
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sshtunnel import SSHTunnelForwarder

app = FastAPI()

# SSH 터널 설정
tunnel = SSHTunnelForwarder(
    ('SSH_HOST', 22),
    ssh_username='SSH_USERNAME',
    ssh_password='SSH_PASSWORD',
    remote_bind_address=('127.0.0.1', 5432)
)
tunnel.start()

# SQLAlchemy 엔진 생성
DATABASE_URL = f'postgresql://DB_USER:[email protected]:{tunnel.local_bind_port}/DB_NAME'
engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# 의존성 주입을 위한 세션 생성 함수
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

@app.on_event("shutdown")
def shutdown_event():
    tunnel.stop()