def get_user_vulnerable(username: str):
    query = f"SELECT * FROM users WHERE username = '{username}'"
    cursor.execute(query)
    return cursor.fetchone()

query = f"SELECT * FROM users WHERE username = '{username}'"
cursor.execute(query)

cursor.execute(
    "SELECT id, username, email FROM users WHERE username = %s",
    (username,)
)

const result = await pool.query(
    'SELECT id, username, email FROM users WHERE id = $1',
    [userId]
);

const result = await pool.query(
    `INSERT INTO orders (user_id, product_id, quantity, created_at)
     VALUES ($1, $2, $3, NOW())
     RETURNING id`,
    [userId, productId, quantity]
);

String sql = "SELECT id, username, email FROM users WHERE id = ?";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
    stmt.setLong(1, userId);
    ResultSet rs = stmt.executeQuery();
}

@Query("SELECT u FROM User u WHERE u.username = :username")
Optional<User> findByUsername(@Param("username") String username);

User.objects.filter(name=name)

User.objects.raw("SELECT * FROM users WHERE name = %s", [name])

[클라이언트]  psql "postgresql://app_user:***@db:5432/appdb?sslmode=require"  +  SELECT ...
   │
   ① 연결·전송보안   TCP 접속 후 TLS 협상 (sslmode=require면 암호화 안 되면 거부)
   │
   ② 인증(누구인가)   pg_hba.conf 규칙 대조 — 호스트·DB·사용자·방식(scram-sha-256)
   │                  비번 불일치·해당 규칙 없음 → 인증 실패로 여기서 끊김
   │
   ③ 인가(무엇을 하나) 롤에 GRANT된 객체·동작 확인 (CONNECT·USAGE·SELECT/INSERT…)
   │                  권한 없으면 permission denied
   │
   ④ 실행 시 제어     행 수준(RLS 정책)·열 수준(컬럼 GRANT)으로 접근 범위를 좁힘
   │
   ⑤ 감사 기록        pgaudit가 누가·언제·무슨 SQL을 실행했는지 로그로 남김
   ▼
[결과]  허용된 행·열만 반환 + 감사 로그에 흔적

CREATE USER readonly_user WITH PASSWORD 'another_strong_password';
GRANT CONNECT ON DATABASE myapp_db TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

CREATE ROLE app_readwrite;
CREATE ROLE app_readonly;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

GRANT app_readwrite TO app_user;
GRANT app_readonly TO readonly_user;

REVOKE app_readwrite FROM app_user;

import bcrypt

def hash_password(plain_password: str) -> str:
    salt = bcrypt.gensalt(rounds=12)
    hashed = bcrypt.hashpw(plain_password.encode('utf-8'), salt)
    return hashed.decode('utf-8')

def verify_password(plain_password: str, hashed_password: str) -> bool:
    return bcrypt.checkpw(
        plain_password.encode('utf-8'),
        hashed_password.encode('utf-8')
    )

private final BCryptPasswordEncoder encoder = new BCryptPasswordEncoder(12);

public User register(String username, String rawPassword) {
    String hashedPassword = encoder.encode(rawPassword);
    return userRepository.save(new User(username, hashedPassword));
}

public boolean authenticate(String rawPassword, String storedHash) {
    return encoder.matches(rawPassword, storedHash);
}

postgresql://user:pass@db-host:5432/mydb?sslmode=require

conn = psycopg2.connect(
    host="db-host",
    database="mydb",
    user="app_user",
    password="...",
    sslmode="verify-full",
    sslrootcert="/path/to/ca.crt"
)

hostssl  all  all  0.0.0.0/0  scram-sha-256

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE payment_info (
    id          SERIAL PRIMARY KEY,
    user_id     INTEGER NOT NULL,
    card_number BYTEA,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO payment_info (user_id, card_number)
VALUES (
    42,
    pgp_sym_encrypt('4111-1111-1111-1111', current_setting('app.encryption_key'))
);

SELECT
    user_id,
    pgp_sym_decrypt(card_number, current_setting('app.encryption_key')) AS card_number
FROM payment_info
WHERE user_id = 42;

CREATE EXTENSION IF NOT EXISTS pgaudit;

ALTER SYSTEM SET pgaudit.log = 'write, ddl, role';

SELECT pg_reload_conf();

2024-03-15 10:23:45 UTC [1234] app_user LOG: AUDIT: SESSION,1,1,DDL,DROP TABLE,TABLE,public.users,DROP TABLE users
2024-03-15 10:24:01 UTC [1234] app_user LOG: AUDIT: SESSION,2,1,WRITE,INSERT,TABLE,public.orders,INSERT INTO orders VALUES (...)

SELECT usename, usesuper, usecreatedb, usecreaterole
FROM pg_user
WHERE usesuper = true;

SELECT grantee, privilege_type, table_name
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
ORDER BY grantee, table_name;

SELECT ssl, count(*)
FROM pg_stat_ssl
JOIN pg_stat_activity ON pg_stat_ssl.pid = pg_stat_activity.pid
GROUP BY ssl;