How to connect your database securely — a step-by-step guide

Connecting a production database to an external tool doesn’t have to be risky — but it does require a few deliberate steps. This guide walks you through what to do on your side before sharing any credentials.

These steps apply regardless of which tool you’re connecting to.

Step 1: Create a dedicated database user

Never use your superuser, your application user, or root. Create a new user specifically for the external tool.

For PostgreSQL:

CREATE USER byesql_reader WITH PASSWORD 'use-a-strong-random-password';

For MySQL:

CREATE USER 'byesql_reader'@'%' IDENTIFIED BY 'use-a-strong-random-password';

Use a randomly generated password — a password manager works well for this. The dedicated user makes it easy to revoke access later without affecting anything else.

See the full setup guides: PostgreSQL read-only user · MySQL read-only user

Step 2: Grant only SELECT permissions

The new user should only be able to read data, not write it.

PostgreSQL:

GRANT CONNECT ON DATABASE your_database TO byesql_reader;
GRANT USAGE ON SCHEMA public TO byesql_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO byesql_reader;

MySQL:

GRANT SELECT ON your_database.* TO 'byesql_reader'@'%';

This is your first line of defense. Even if something goes wrong at the application layer, a read-only database user cannot modify or delete your data.

Step 3: Limit access to only the tables you need

SELECT on all tables is more exposure than necessary. If the tool is used for sales reporting, it doesn’t need access to your users, payments, or audit_logs tables.

PostgreSQL — grant specific tables only:

-- Do NOT run the blanket grant from Step 2
-- Instead, grant table by table:
GRANT SELECT ON TABLE orders TO byesql_reader;
GRANT SELECT ON TABLE products TO byesql_reader;
GRANT SELECT ON TABLE customers TO byesql_reader;

Limiting the schema reduces the blast radius of a breach and limits what information is sent to AI providers when the tool inspects your schema. See what your schema exposes for more context on this.

Step 4: Restrict database access by IP

Your database shouldn’t be open to the world. Allowlist only the IPs that need access — nothing more.

ByeSQL provides its egress IP ranges so you can restrict inbound access at the firewall, security group, or VPC level. Contact support@byesql.com to get the current list before opening any inbound rules.

Avoid setting 0.0.0.0/0 — if your infrastructure requires it to connect, that’s worth investigating before going live.

Step 5: Enable query logging at the database level

Even with a read-only user, you want visibility into what data was accessed and when. Enable query logging at the database level — those logs are under your control and persist even if you stop using the tool.

See how to audit query history in PostgreSQL for a full setup guide.

Step 6: Plan how you’ll revoke access

Before you connect, know how to disconnect. The fastest way to revoke access is to change the database user’s password directly — this invalidates all active connections immediately, regardless of what credentials the tool has stored.

Keep the password in your own records (not just inside the tool’s settings).

PostgreSQL:

ALTER USER byesql_reader WITH PASSWORD 'new-password-here';

MySQL:

ALTER USER 'byesql_reader'@'%' IDENTIFIED BY 'new-password-here';

To fully remove the user:

-- PostgreSQL
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM byesql_reader;
DROP USER byesql_reader;

-- MySQL
DROP USER 'byesql_reader'@'%';

Step 7: Verify the setup before going live

Before handing over credentials, test that the permissions work as intended.

Connect as byesql_reader and verify:

-- Should succeed:
SELECT * FROM orders LIMIT 5;

-- Should fail with "permission denied":
INSERT INTO orders (id) VALUES (1);
UPDATE orders SET status = 'cancelled' WHERE id = 1;
DELETE FROM orders WHERE id = 1;

If a write query succeeds, the permissions aren’t correctly configured. Fix this before connecting the tool.

Quick checklist

Before sharing credentials with any external tool:

Created a dedicated database user (not root, not your app user)
User has SELECT only — no INSERT, UPDATE, DELETE, or DDL
Access scoped to only the tables the tool needs
Database access restricted by IP (allowlist only)
Query logging enabled at the database level
Password stored somewhere you control, ready to rotate
Write operations verified to fail when connecting as the new user

These steps take under 15 minutes. They significantly reduce the risk of using any external tool with your production data.