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])

CREATE USER app_user WITH PASSWORD 'strong_random_password_here';

GRANT CONNECT ON DATABASE myapp_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

REVOKE CREATE ON SCHEMA public FROM app_user;

실행 완료 또는 조회 결과가 표시됩니다.

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;