DEV Community: Maxiviper117

Securely Connect to a Remote PostgreSQL Database with SSH Tunneling

Maxiviper117 — Wed, 01 Oct 2025 18:11:18 +0000

Securely Connect to a Remote PostgreSQL Database with SSH Tunneling

Exposing your database port directly to the internet is like leaving your house unlocked with a “welcome” sign on the door. A safer approach is to use an SSH tunnel (a secure pipe between your local machine and the remote server). This way PostgreSQL can stay bound to localhost on the server, while you still connect from your laptop.

1. Create an SSH key pair

If you don’t already have an SSH key, generate one locally:

ssh-keygen -t ed25519 -C "your_email@example.com"

Private key: ~/.ssh/id_ed25519
Public key: ~/.ssh/id_ed25519.pub

Keep the private key safe and never share it. The public key is what you’ll add to the server.

2. Copy your public key to the server

Replace user@remote-server.com with your real username and server address:

ssh-copy-id -i ~/.ssh/id_ed25519.pub user@remote-server.com

If ssh-copy-id isn’t available, you can manually paste the contents of the .pub file into the server’s ~/.ssh/authorized_keys.

3. Add a convenient SSH config entry

Save yourself some typing by creating an entry in ~/.ssh/config:

Host my-server
    HostName remote-server.com
    User user
    IdentityFile ~/.ssh/id_ed25519
    IdentitiesOnly yes

Now you can connect with:

ssh my-server

4. Open the SSH tunnel

Assume PostgreSQL is running on the server at localhost:5432. We’ll forward that to port 5555 on your machine:

ssh -L 5555:localhost:5432 my-server

5555: local port on your computer
localhost:5432: PostgreSQL port on the server
my-server: alias from your SSH config

Keep this terminal open while the tunnel is active.

Run in the background:

ssh -fN -L 5555:localhost:5432 my-server

-f = fork to background
-N = don’t execute a remote command, just tunnel

5. Connect with pgAdmin (or any client)

In pgAdmin, DBeaver, or psql, use:

Host: localhost
Port: 5555
User: your database user (e.g. postgres)
Password: your database password
Database name: the actual DB name

The client talks to localhost:5555, and the SSH tunnel securely forwards everything to PostgreSQL on the server.

6. Closing the tunnel

Foreground tunnel: press Ctrl+C.
Background tunnel: find and kill it:

  ps aux | grep 'ssh -L'
  kill <pid>

To Sum Up

With this setup:

Your database port remains hidden from the outside world.
All traffic is encrypted via SSH.
You can connect safely using your favorite GUI tool or CLI.

This pattern works for PostgreSQL, MySQL, Redis, or any other service that you’d rather not expose directly.

🔐 Securing Your SvelteKit App Against CSRF Attacks Using Custom Hooks

Maxiviper117 — Sun, 09 Mar 2025 16:24:46 +0000

Cross-Site Request Forgery (CSRF) is a vulnerability that lets attackers trick your users into unintentionally submitting requests. This guide demonstrates how to implement robust CSRF protection in SvelteKit using custom middleware hooks.

📌 Step 1: Disable Built-in SvelteKit CSRF Check

SvelteKit provides default CSRF checks. To enable custom handling, disable the built-in check first.

📂 svelte.config.ts

// svelte.config.ts
import adapter from '@sveltejs/adapter-auto';

export default {
  kit: {
    adapter: adapter(),
    csrf: {
      checkOrigin: false, // Disable built-in origin checking to allow custom middleware
    },
  },
};

🛡️ Step 2: Create Custom CSRF Middleware

Create the custom CSRF middleware hook file to provide enhanced security and configurability.

📂 src/hooks/csrf.ts

// src/hooks/csrf.ts
import type { Handle } from '@sveltejs/kit';
import { json, text } from '@sveltejs/kit';

/**
 * Custom CSRF Protection Middleware
 *
 * @param allowedPaths - List of URL paths that bypass CSRF protection.
 * @param allowedOrigins - Trusted origins allowed to make cross-origin form submissions.
 */
export function csrf(allowedPaths: string[], allowedOrigins: string[] = []): Handle {
    return async ({ event, resolve }) => {
        const { request, url } = event;

        // Get the 'origin' header from the incoming request
        const requestOrigin = request.headers.get('origin');

        // Determine if the request comes from the same origin
        const isSameOrigin = requestOrigin === url.origin;

        // Check if the request origin is explicitly allowed (trusted external origins)
        const isAllowedOrigin = allowedOrigins.includes(requestOrigin ?? '');

        // Define conditions under which the request is forbidden (potential CSRF attack)
        const forbidden =
            isFormContentType(request) && // Checks if the request contains form data
            ['POST', 'PUT', 'PATCH', 'DELETE'].includes(request.method) && // State-changing methods
            !isSameOrigin && // Origin mismatch
            !isAllowedOrigin && // Not explicitly allowed
            !allowedPaths.includes(url.pathname); // Path not explicitly allowed

        // If forbidden, return a 403 Forbidden response immediately
        if (forbidden) {
            const message = `Cross-site ${request.method} form submissions are forbidden`;

            // Return JSON or plain text based on request headers
            if (request.headers.get('accept') === 'application/json') {
                return json({ message }, { status: 403 });
            }
            return text(message, { status: 403 });
        }

        // If the request passes CSRF checks, continue to the next middleware or endpoint
        return resolve(event);
    };

    /**
     * Helper function to check if request 'origin' is allowed.
     */
    function isAllowedOrigin(requestOrigin: string | null, allowedOrigins: string[]) {
        return allowedOrigins.includes(requestOrigin ?? '');
    }

    /**
     * Helper function to determine if request content-type indicates a form submission
     */
    function isFormContentType(request: Request) {
        const type = request.headers.get('content-type')?.split(';', 1)[0].trim().toLowerCase() ?? '';
        return ['application/x-www-form-urlencoded', 'multipart/form-data', 'text/plain'].includes(type);
    }
}

🚩 Step 3: Integrate the Middleware into SvelteKit's Hooks

Integrate this middleware using SvelteKit's sequence helper, allowing you to chain multiple middleware cleanly.

📂 src/hooks.server.ts

// src/hooks.server.ts
import { sequence } from '@sveltejs/kit/hooks';
import { csrf } from './hooks/csrf';

// Define paths exempt from CSRF checks (e.g., public forms or APIs)
const allowedPaths = ['/api/public-form'];

// Define trusted origins allowed to make cross-origin form submissions
const allowedOrigins = ['https://trusted-site.com', 'http://localhost:5173'];

// Export the combined hooks using 'sequence' for better flexibility
export const handle = sequence(
    csrf(allowedPaths, allowedOrigins) // CSRF hook added here
    // You can chain additional middleware hooks here if needed
);

✅ Step 4: Testing Your CSRF Middleware

Allowed Requests (should pass):

Form submission from same-origin (e.g., https://your-site.com → https://your-site.com/api).
Cross-origin submission from explicitly allowed origin (https://trusted-site.com) to an explicitly allowed path (/api/public-form).

Blocked Requests (should fail with `403`):

Cross-origin submission from a non-whitelisted domain.
Submission from an allowed origin (https://trusted-site.com) to a non-allowed path.
Submissions from unlisted origins/domains.

⚙️ Step 5: Integrate Using SvelteKit `sequence` Hook

Use SvelteKit’s built-in sequence function to combine your CSRF middleware with other hooks seamlessly.

📂 src/hooks.server.ts

// src/hooks.server.ts
import { sequence } from '@sveltejs/kit/hooks';
import { csrf } from './hooks/csrf';

// Initialize middleware with explicit allowed paths and origins
const csrfProtection = csrf(
    ['/api/public-form'], // paths exempt from CSRF protection
    ['https://trusted-site.com', 'http://localhost:5173'] // trusted cross-origin sites
);

// Export the combined hook using SvelteKit’s 'sequence'
export const handle = sequence(csrf(['/api/public-form'], ['https://trusted-site.com']));

⚠️ Important Formatting Notes for Allowed Origins

When defining allowed origins, remember:

✅ Correct Examples:

['https://trusted-site.com', 'http://localhost:5173']

❌ Incorrect Examples (these won't work!):

❌ No protocol: 'trusted-site.com'
❌ Wildcards not supported: '*.example.com'
❌ Paths not allowed: https://example.com/path

🎉 Summary & Final Thoughts

You've successfully implemented custom CSRF protection in your SvelteKit application:

✅ Custom middleware with explicit allowed origins and paths.
✅ Flexible configuration via hooks.
✅ Clear error messaging for blocked requests.

This approach provides powerful protection against CSRF attacks without restricting legitimate cross-origin usage.

Stay secure, and happy coding! 🚀✨

DEV Community: Maxiviper117

Securely Connect to a Remote PostgreSQL Database with SSH Tunneling

Securely Connect to a Remote PostgreSQL Database with SSH Tunneling

1. Create an SSH key pair

2. Copy your public key to the server

3. Add a convenient SSH config entry

4. Open the SSH tunnel

5. Connect with pgAdmin (or any client)

6. Closing the tunnel

To Sum Up

🔐 Securing Your SvelteKit App Against CSRF Attacks Using Custom Hooks

📌 Step 1: Disable Built-in SvelteKit CSRF Check

🛡️ Step 2: Create Custom CSRF Middleware

🚩 Step 3: Integrate the Middleware into SvelteKit's Hooks

✅ Step 4: Testing Your CSRF Middleware

Allowed Requests (should pass):

Blocked Requests (should fail with 403):

⚙️ Step 5: Integrate Using SvelteKit sequence Hook

⚠️ Important Formatting Notes for Allowed Origins

🎉 Summary & Final Thoughts

Blocked Requests (should fail with `403`):

⚙️ Step 5: Integrate Using SvelteKit `sequence` Hook