Database access from a GUI client¶

How to connect Sequel Ace (or TablePlus, DBeaver, mysql) from your laptop to a database on staging – and, with permission, on production.

The database servers are never exposed to the internet: port 3306 is only open to allowlisted IP ranges, hosts with zt_lockdown_enabled: true are not reachable at all outside the Cloudflare tunnel, and SSH port forwarding is switched off by default. So the connection is built in two hops, and the host has to be configured to allow the tunnel first.

How it works¶

Sequel Ace ──MySQL──▶ its own SSH tunnel ──▶ 127.0.0.1:13306
                                                   │
                                             cloudflared
                                                   │
                                        Cloudflare Access (login)
                                                   │
                                       dev-e01: sshd on :22
                                                   │
                                     dev-e01: MariaDB on localhost:3320

1. cloudflared access tcp turns the Access-protected hostname into a plain TCP port on your own machine. What answers on that port is the server's SSH port – the tunnel only publishes SSH, not MySQL.
2. The client's own SSH tunnel logs in through that local port and forwards MySQL from the server's loopback interface.

Two hops, because the GUI client cannot run a ProxyCommand itself. From a terminal you can skip hop 1 entirely – see CLI alternative.

Prerequisites¶

Allow the tunnel on the host¶

Without this, everything else works and the client still fails with administratively prohibited: open failed.

The exception is per host, per user, per destination – set both variables in the host's host_vars and reapply the host:

# inventories/stage/host_vars/web-01/main.yml
ssh_tcp_forwarding_users:
  - pt
ssh_tcp_forwarding_permitopen:
  - localhost:3320

ansible-playbook -i inventories/stage/hosts.yml baseline.yml -l web-01

Staging web-01 is already set up like this for pt. See SSH tunnels for the details, and note that PermitOpen is matched literally: sshd does no name lookup, so a client asking for 127.0.0.1:3320 is refused when the allowlist says localhost:3320.

Step 1 – start the access tunnel¶

cloudflared access tcp --hostname dev-e01.subscribed.build --url 127.0.0.1:13306

• Runs in the foreground – keep the terminal open for as long as you need the connection.
• 13306 is just a free local port. Pick a different one per host if you want several tunnels open at the same time.
• The first run (or after 24 hours) opens a browser for the Access login.

Hostnames¶

The hostnames come from ansible_host in inventories/<env>/hosts.yml.

Step 2 – configure Sequel Ace¶

Create a new connection and choose the SSH tab (not Standard – Standard has no tunnel).

Save it as a favourite – then it is only step 1 you have to repeat next time.

Everything under "MySQL" is seen from the server's point of view; everything under "SSH" is seen from your machine. Sequel Ace passes the MySQL host and port straight into the forwarding request, which is why they have to be written the same way as in PermitOpen – localhost:3320, not 127.0.0.1:3320.

Basic setup – elome on staging¶

All staging sites live on the same host, dev-e01. Example for elome-com:

The database credentials are not in this repo – they live in the site's .env on the server, which is the authoritative source:

ssh pt@dev-e01.subscribed.build
sudo grep '^DB_' /home/barberklingen/elome-com-bedrock/.env

The project paths for the other staging sites are listed in inventories/stage/host_vars/web-01/main.yml under sites.

If .env points somewhere other than localhost:3320 – another port, or a container on its own address – that destination has to be added to ssh_tcp_forwarding_permitopen before the tunnel will reach it.

Never copy database credentials into this repo, a ticket, or a chat message. Read them on the host when you need them.

Other clients¶

TablePlus / DBeaver: same values – enable "Over SSH" / the SSH tab and fill in the SSH host 127.0.0.1 with port 13306, and the database host localhost with port 3320.

CLI alternative¶

If ~/.ssh/config already has the cloudflared ProxyCommand (see Cloudflare Zero Trust), you do not need cloudflared access tcp at all – ssh sets up the tunnel on its own:

# Terminal 1 – forward the server's MySQL port to 3307 locally.
# The destination must be spelled localhost:3320 to pass PermitOpen.
ssh -L 3307:localhost:3320 pt@dev-e01.subscribed.build

# Terminal 2 – here 127.0.0.1 is required: with localhost the mysql
# client ignores the port and looks for a unix socket instead.
mysql -h 127.0.0.1 -P 3307 -u <user> -p <database>

Production¶

Ask before you connect to a production database. Permission is per task, and read-only is not an exception – see the rules in the repo root.

Three things are different on production:

• No host allows forwarding yet. ssh_tcp_forwarding_users is unset on every prod host, so a tunnel needs a host_vars change and a baseline.yml run first – i.e. a reviewed change, not something you enable on the spot.
• Grants are locked to source IPs. The MySQL users on the prod database hosts are granted for specific addresses (the web servers' internal IPs, the office range, Plecto), not for localhost. A login over an SSH tunnel arrives as localhost and is therefore rejected even though the tunnel works. See mariadb_users in inventories/prod/host_vars/<host>/main.yml for who is allowed from where.
• From an allowlisted network – e.g. the office – you can connect straight to port 3306 without a tunnel, with the user that is granted for that IP. The plecto user is SELECT-only, which is usually all you want for a look around.

If you need a new database user for GUI access, add it to mariadb_users in host_vars with the password in vault, with the narrowest privileges and host list that solves the task. See Vault and Roles reference.

Troubleshooting¶

See also¶

• SSH management – granting the tunnel on a host
• Cloudflare Zero Trust – tunnels, Access policies, lockdown
• Getting started – SSH configuration
• Vault – where passwords belong