COPY FROM Exploits: When PostgreSQL Reads Your Filesystem

Source: Dev.to

PostgreSQL’s COPY FROM is powerful. It can bulk‑load data from files, but it can also read arbitrary files such as /etc/passwd.

The Attack

// ❌ User controls file path
const filepath = req.body.filepath;
await client.query(`COPY users FROM '${filepath}'`);

Attacker input

filepath: /etc/passwd

PostgreSQL now reads your system files into the database.

Security References

⚠️ Note: While PostgreSQL treats CVE‑2019‑9193 as a “feature” for superusers, the pattern of user‑controlled file paths in application code remains a critical vulnerability.

What Can Be Read

The Correct Pattern

// ✅ Never use user input in file paths
const ALLOWED_IMPORTS = {
  users: '/var/imports/users.csv',
  products: '/var/imports/products.csv',
};

const filepath = ALLOWED_IMPORTS[req.body.type];
if (!filepath) throw new Error('Invalid import type');

await client.query(`COPY users FROM '${filepath}'`);

Or, use COPY FROM STDIN with validated data:

// ✅ Use COPY FROM STDIN
const stream = client.query(pgCopyStreams.from('COPY users FROM STDIN CSV'));
// Pipe validated CSV data to `stream`

COPY TO Is Also Dangerous

// ❌ Attacker can write to filesystem
await client.query(`COPY users TO '/var/www/html/shell.php'`);

Combined with control over data, this enables:

• Web‑shell deployment
• Configuration file overwrite
• Cron‑job injection

The Rule: pg/no-unsafe-copy-from

The pattern is detected by the pg/no-unsafe-copy-from rule from eslint‑plugin‑pg. The rule uses tiered detection:

Let ESLint Catch This

npm install --save-dev eslint-plugin-pg

Use Recommended Config

import pg from 'eslint-plugin-pg';
export default [pg.configs.recommended];

Enable Only This Rule

import pg from 'eslint-plugin-pg';

export default [
  {
    plugins: { pg },
    rules: {
      'pg/no-unsafe-copy-from': 'error',
    },
  },
];

Configure for Admin Scripts

If you have legitimate admin/migration scripts that use hard‑coded file paths:

export default [
  {
    files: ['**/migrations/**', '**/scripts/**'],
    rules: {
      'pg/no-unsafe-copy-from': ['error', { allowHardcodedPaths: true }],
    },
  },
];

Allow Specific Paths

export default [
  {
    rules: {
      'pg/no-unsafe-copy-from': [
        'error',
        { allowedPaths: ['^/var/imports/', '\\.csv$'] },
      ],
    },
  },
];

What You’ll See

Dynamic Path (CRITICAL – Injection Risk)

src/import.ts
  8:15  error  🔒 CWE-73 OWASP:A03-Injection | Dynamic file path in COPY FROM detected - potential arbitrary file read. | CRITICAL [SOC2,PCI-DSS]
        Fix: Never use user input in COPY FROM paths. Use COPY FROM STDIN for user data.

Hardcoded Path (MEDIUM – Operational Risk)

src/import.ts
  8:15  warning  ⚠️ CWE-73 | Hardcoded file path in COPY FROM - server-side file access. | MEDIUM
        Fix: Prefer COPY FROM STDIN for application code. Use allowHardcodedPaths option if this is an admin script.

Before / After: Fixing the Lint Error

❌ Before (Triggers Lint Error)

// This code triggers pg/no-unsafe-copy-from
const filepath = req.body.filepath;
await client.query(`COPY users FROM '${filepath}'`);

✅ After (Lint Error Resolved)

// Safe implementation – whitelist allowed imports
const ALLOWED_IMPORTS = {
  users: '/var/imports/users.csv',
};

const filepath = ALLOWED_IMPORTS[req.body.type];
if (!filepath) throw new Error('Invalid import type');

await client.query(`COPY users FROM '${filepath}'`);

Safe COPY FROM STDIN Example

// Use COPY FROM STDIN – the recommended safe pattern
import { from as copyFrom } from 'pg-copy-streams';
import { Readable } from 'stream';

async function importUsers(csvData) {
  const client = await pool.connect();
  try {
    // ✅ COPY FROM STDIN is safe – no file‑system access
    const stream = client.query(
      copyFrom('COPY users (name, email) FROM STDIN CSV')
    );

    // Validate and stream the data from your application
    const validatedCsv = csvData
      .map(row => `${sanitize(row.name)},${sanitize(row.email)}`)
      .join('\n');

    Readable.from(validatedCsv).pipe(stream);

    await new Promise((resolve, reject) => {
      stream.on('finish', resolve);
      stream.on('error', reject);
    });
  } finally {
    client.release();
  }
}

Key changes

• Replaced COPY FROM '/path/to/file' with COPY FROM STDIN.
• Data now flows through your application, not the filesystem.
• You control validation before it reaches the database.

Quick Install

npm install --save-dev eslint-plugin-pg

import pg from 'eslint-plugin-pg';
export default [pg.configs.recommended];

Keep PostgreSQL data in the database, not on your filesystem.

• 📦 npm: eslint-plugin-pg
• 📖 Rule docs: no-unsafe-copy-from
• ⭐ Star on GitHub: https://github.com/ofri-peretz/eslint

🚀 Follow me for more security articles & updates:
GitHub | X | LinkedIn | Dev.to