SQLBin Operational Manual

Installation

See the SQLBin homepage for the latest version and installation instructions.

Setup

Database preparation

I’m assuming you already have a PostgreSQL database running that you’d like to query against. You’ll want to setup a separate read-only user for SQLBin, because SQLBin by itself won’t prevent writes.

CREATE ROLE <user> LOGIN PASSWORD '<password>';
GRANT CONNECT ON DATABASE <db_name> TO <user>;
GRANT USAGE ON SCHEMA public TO <user>;

-- To grant read access to all tables:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user>;

-- Or to grant on a per-table basis
GRANT SELECT ON <public_table_name> TO <user>;

Permissions can be granted on individual tables, views, functions, columns and even rows. Refer to the PostgreSQL documentation for more information.

You can also set various configuration parameters for the user. The following are useful:

-- Change the default display of timestamptz values.
ALTER ROLE <user> SET timezone = 'UTC';
-- Set a default maximum query execution time.
ALTER ROLE <user> SET statement_timeout = '10 s';
-- Defense-in-depth, don't rely on this because it can likely be bypassed.
ALTER ROLE <user> SET default_transaction_read_only = true;

Configure SQLBin

Create a config.ini:

[server]
db=postgresql://user:password@hostname/database
storage=/path/to/sqlbin-data

See the Configuration Directives below for the available settings. Then simply run SQLBin:

sqlbin -c /path/to/config.ini

Setup reverse proxy

For single-user mode you can point your browser directly to the bind socket of SQLBin, but to support multiple accounts you’ll need to put a proxy server in front that handles authentication and tells SQLBin which user is currently logged in.

Here’s a configuration example for nginx that queries another endpoint for authentication and then passes the user’s credentials to SQLBin. The endpoint response can set the X-USER-ID, X-USER-NAME and X-USER-ROLE headers. If these headers are not set, the default anonymous credentials are used instead.

# SQLBin passes state around in the URI, increasing the maximum request header size is recommended.
large_client_header_buffers 1 128k;

# Proxy this request to another endpoint to fetch user credentials.
# FastCGI or other handlers can also be used instead.
location = /auth-request-for-sqlbin {
  internal;
  proxy_pass_request_body off;
  proxy_method GET;
  proxy_set_header Content-Length "";
  proxy_pass http://my-auth-endpoint/;  # <- Change this
}
auth_request /auth-request-for-sqlbin;

# Now grab the response headers from the above endpoint and pass them to SQLBin.
auth_request_set $auth_user_id   $upstream_http_x_user_id;
auth_request_set $auth_user_name $upstream_http_x_user_name;
auth_request_set $auth_user_role $upstream_http_x_user_role;
proxy_set_header X-USER-ID   $auth_user_id;
proxy_set_header X-USER-NAME $auth_user_name;
proxy_set_header X-USER-ROLE $auth_user_role;

proxy_pass http://127.0.0.1:8000/;  # <- Point this to your SQLBin server

Don’t forget to add the following lines to SQLBin’s config.ini:

[user]
id_header=X-USER-ID
name_header=X-USER-NAME
role_header=X-USER-ROLE

Configuration Directives

`[server]`

db

URI of the database being queried, formatted as postgresql://user:password@hostname/database. When connecting to a UNIX socket, use the following format: postgresql://user:password@/database?host=/path/to/socket.

SQLBin lets users run arbitrary queries over this connection, so it is important that the database user does not have more permissions than strictly necessary for querying.

storage

Path where SQLBin can store data and cached queries. The directory will be created if it does not yet exist.

bind

URI for the HTTP server to bind to, supported formats are tcp://ip:port/, unix:///path/to/socket or tls://ip:port?key=private.key&cert=certificate.cert&ca=ca.crt. While HTTPS is technically supported, you’ll probably still want to run SQLBin behind a reverse proxy anyway.

Defaults to tcp://127.0.0.1:8000/.

gnuplot_path

Path to the gnuplot-sqlbin binary for graphing functionality. See the gnuplot/ directory in the SQLBin source repository for instructions on how to build this binary.

While it is technically possible to use an unmodified gnuplot binary, doing so is wildly insecure and strongly discouraged. Regular gnuplot allows arbitrary shell execution, filesystem access and, for the use case of SQLBin, HTML injection, all of which the custom gnuplot-sqlbin build attempts to protect against.

broken_check_interval

How often to check whether a query is broken, in number of seconds. Defaults to 86400 (1 day), can be set to 0 to disable automatic checks.

`[user]`

SQLBin does not implement its own user management system, but instead expects that information about the current user is sent through HTTP request headers from a reverse proxy. Running in single-user mode (with or without a proxy) is also supported.

A “user” to SQLBin has only two properties: an id and a name. The id is case-sensitive, used in URLs and stored in the database and should be a stable identifier to uniquely identify a user. The name is only used for display purposes and does not have to be unique or stable.

id_anon

User identifier to use when id_header not set or the header is not present in the request. Default “anonymous”.

name_anon

Display name to use when name_header is not set or the header is not present in the request.

role_anon

User role to assume when role_header is not set or the header is not present in the request. Only two roles are currently supported:

editor: Regular user, can run queries and manage their own saved queries.
admin: Administrator, can view private/unlisted queries and delete/edit queries of other users.

Any other value results in an internal server error.

id_header

Name of the HTTP request header to get the user id from, case-insensitive.

name_header

Name of the HTTP request header to get the display name from, case-insensitive.

role_header

Name of the HTTP request header to get the user’s role from, case-insensitive. See role_anon for supported values of this header.

`[cache]`

SQLBin caches query results in order to provide a snappy user experience. Even if you prefer to always have fresh results from the database, caching queries for at least a few minutes will ensure that the UI remains somewhat responsive when working with slower queries.

min_query_time: Minimum time, in seconds, a query needs to take before it is considered for caching. The time is measured until the first row is received, which means it does not count any processing that the database may do in order to send further rows. Defaults to 0.05 (50 ms).
check_interval: Number of seconds between scans through the cache to look for entries older than max_age. Defaults to 1800 (30 mins).
max_age: Number of seconds after which a cache entry is deleted. Cache entries are only checked once every check_interval, so it’s possible that older results are still used. Defaults to 3600 (1 hour).

`[ui]`

max_table_rows: Maximum number of query result rows to display in the HTML table. Defaults to 10k.
max_plot_rows: Maximum number of query result rows to pass to gnuplot. Defaults to 10k.
max_export_rows: Maximum number of query result rows to include in the exports. Defaults to 100k.
queries_per_page: Number of entries per page on the query browser. Defaults to 100.
about: Path to a HTML file to put on /about.