GitHub - kazhuravlev/database-gateway: Safe access to production databases (original) (raw)

Database Gateway

Go Reference License Test Status Release Status Go Report Card codecov Mentioned in Awesome Go

This service provides a unified web interface for secure, controlled access to company databases. It enables employees to run queries on production databases while enforcing access control (ACL) policies. For example, team leads may have permissions to execute both SELECT and INSERT queries on certain tables, while other team members are restricted to read-only (SELECT) access. This approach ensures that database interactions are managed safely and that each user's access is tailored to their role and responsibilities.

Architecture Overview

This application acts as a secure gateway to multiple PostgreSQL instances, allowing authenticated users to run approved queries through a unified web interface, with fine-grained ACLs controlling access.

                     ┌───────────────────────────┐
                     │ PROD      ┌─────────────┐ │
                     │       ┌───┤  Postgres1  │ │
  ┌────────┐    ┌────────┐   │   └─────────────┘ │
  │  USER  │────│  DBGW  │───┼                   │
  └────────┘    └────────┘   │   ┌─────────────┐ │
                     │       └───┤  Postgres2  │ │
                     │           └─────────────┘ │
                     └───────────────────────────┘

Components

  1. Local PostgreSQL Database:
    • Stores query results, user profiles, and ACLs.
    • Acts as a cache for query results, allowing unique links for debugging without re-execution.
  2. Remote PostgreSQL Instances:
    • Host production data and are accessed only through the app.
    • Queries are run only if authorized by ACLs, limiting access to specific users, tables, and query types.
  3. OIDC Authentication:
    • Users authenticate via an external OIDC provider.
    • User roles are mapped to ACLs, defining what queries each user can run.
  4. Access Control Lists (ACLs):
    • Define user permissions at the instance, table, and query type levels.
    • Stored in the local database, restricting queries based on user identity.
  5. Web Interface:
    • Provides login, query submission, and result viewing.
    • Shows error feedback for unauthorized or restricted queries.

Flow of Operations

  1. Authentication: Users log in via OIDC, and their identity maps to ACL permissions.
  2. Query Submission: Authorized queries are checked against ACLs, then run on remote instances.
  3. Result Caching: Results are stored locally with unique links for easy access and debugging.

This architecture ensures secure, controlled access to production data, balancing usability with data protection.

Quickstart with example setup

Run commands to get a local dbgw instance with 3 postgres.

git clone https://github.com/kazhuravlev/database-gateway.git cd database-gateway/example docker compose up --pull always --force-recreate -d open 'http://127.0.0.1:8080'

Admin: admin@example.com password

User1: user1@example.com password

You will see only 2 instances from 3 postgres instances (local-1, local-2,local-3) because ACL is applied to test user. ACLs stored in config.json.

pic1_instances.png

Choose local-1, put this query select id, name from clients and click Run pic2_run.png

Features

Advanced Configuration

Authentication Options

The service supports two authentication methods:

  1. Config File Authentication: For simple setups with hardcoded users
    "users": {
    "provider": "config",
    "configuration": [
    {
    "id": "admin@example.com",
    "username": "admin@example.com",
    "password": "password"
    }
    ]
    }
  2. OIDC Authentication: For integration with identity providers
    "users": {
    "provider": "oidc",
    "configuration": {
    "client_id": "example-app",
    "client_secret": "example-app-secret",
    "issuer_url": "http://localhost:5556",
    "redirect_url": "http://localhost:8080/auth/callback",
    "scopes": ["email", "profile"]
    }
    }

Access Control Configuration

Access control lists define user permissions with fine-grained control:

"acls": [ { "user": "admin@example.com", "op": "", "target": "", "tbl": "", "allow": true }, { "user": "user1@example.com", "op": "select", "target": "pg-5433", "tbl": "", "allow": true } ]

Wildcards (*) allow all operations, targets, or tables. Specific permissions override broader ones.

Database Connection Settings

Configure performance settings for each database connection:

"connection": { "host": "postgres1", "port": 5432, "user": "pg01", "password": "pg01", "db": "pg01", "use_ssl": false, "max_pool_size": 4 }

Performance Optimizations

Security Considerations

Edge Cases and Troubleshooting

Interesting projects