معماری امن و سریع لایه پایگاه داده: راهنمای جامع طراحی، بهینه‌سازی و امنیت دیتابیس

import { Pool } from 'pg';

const pool = new Pool({
  host: process.env.DB_HOST,
  port: Number(process.env.DB_PORT),
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
  ssl: {
    rejectUnauthorized: true,
    ca: process.env.DB_CA_CERT
  }
});

export default pool;

export async function getUserByEmail(email) {
  const query = `
    SELECT id, full_name, role, password_hash 
    FROM users 
    WHERE email = $1 AND is_active = true 
    LIMIT 1
  `;
  const values = [email];
  const result = await pool.query(query, values);
  return result.rows[0] || null;
}

CREATE INDEX idx_users_email ON users(email);

CREATE INDEX idx_orders_user_status ON orders(user_id, status);

CREATE INDEX idx_logs_created_at_desc ON audit_logs(created_at DESC);

import Redis from 'ioredis';

const redis = new Redis(process.env.REDIS_URL);

export async function getCachedProduct(productId) {
  const cacheKey = `product:${productId}`;
  const cachedData = await redis.get(cacheKey);

  if (cachedData) {
    return JSON.parse(cachedData);
  }

  const query = 'SELECT * FROM products WHERE id = $1 LIMIT 1';
  const result = await pool.query(query, [productId]);
  const product = result.rows[0];

  if (product) {
    await redis.setex(cacheKey, 3600, JSON.stringify(product));
  }

  return product || null;
}

CREATE ROLE app_service WITH LOGIN PASSWORD 'strong_random_password_here';
GRANT CONNECT ON DATABASE production_db TO app_service;
GRANT USAGE ON SCHEMA public TO app_service;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_service;
REVOKE DROP, TRUNCATE, ALTER ON ALL TABLES IN SCHEMA public FROM app_service;

CREATE ROLE analytics_readonly WITH LOGIN PASSWORD 'another_secure_password';
GRANT CONNECT ON DATABASE production_db TO analytics_readonly;
GRANT USAGE ON SCHEMA public TO analytics_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_readonly;

const writePool = new Pool({
  connectionString: process.env.PRIMARY_DB_URL,
  max: 10
});

const readPool = new Pool({
  connectionString: process.env.REPLICA_DB_URL,
  max: 30
});

export async function executeQuery(sql, params, isWriteOperation = false) {
  const client = isWriteOperation ? writePool : readPool;
  return await client.query(sql, params);
}

export async function transferBalance(senderId, receiverId, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    const debitQuery = `
      UPDATE accounts 
      SET balance = balance - $1 
      WHERE id = $2 AND balance >= $1
    `;
    const debitResult = await client.query(debitQuery, [amount, senderId]);
    
    if (debitResult.rowCount === 0) {
      throw new Error('Insufficient funds or invalid sender');
    }

    const creditQuery = `
      UPDATE accounts 
      SET balance = balance + $1 
      WHERE id = $2
    `;
    await client.query(creditQuery, [amount, receiverId]);

    await client.query('COMMIT');
    return { success: true };
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

ALTER SYSTEM SET log_min_duration_statement = 200;
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;
ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();