Data sources

What a data source is #

A PostgreSQL, MySQL, MariaDB or SQL Server database, added under Add a data source in a project's sidebar with its address. Every table in it shows up at once, in the grid and to scripts, with nothing copied: the rows stay where they are, under your backups. A project has up to 5 of them, and each has a short name — what a script says (db["shop"]) and what the REST API takes (?source=shop).

Removing a data source, or deleting the project, never touches the database itself.

The address #

postgres://tenlines:PASSWORD@db.example.com:5432/shop?sslmode=verify-full
mysql://tenlines:PASSWORD@db.example.com:3306/shop?ssl-mode=VERIFY_IDENTITY
mariadb://tenlines:PASSWORD@db.example.com:3306/shop?ssl-mode=VERIFY_IDENTITY
sqlserver://tenlines:PASSWORD@db.example.com:1433/shop?encrypt=true

"Encrypted only" keeps the traffic private but does not check who answers, so something sitting between this server and yours could still pose as your database. Use the first column whenever the database has a certificate from a public authority, which every hosted database does. With none of these, the connection is not encrypted at all.

The address is tried before anything is kept, stored encrypted, and shown afterwards without its password. On PostgreSQL the form also takes the schema to work in (public unless you say otherwise); on MySQL and MariaDB it is the database the address names, and on SQL Server the login's default schema.

Letting this server in #

The database has to accept connections from this server's address: add it to the database's firewall, security group or list of allowed addresses. Whoever runs this server can tell you what it is.

A database that does not answer when it is added is nearly always one that does not let this server in, or an address that only works from inside its own network — a host name like db.internal, or a port only open to the machines next to it. The address has to be the one it is reached by from the internet.

A login of its own #

Do not paste the administrator's password. Make a login for this project that can read and write the rows of the tables it needs and nothing else: what it cannot do, no script, API key or assistant acting through it can do either, whatever they are told. And it can be taken away on its own.

CREATE ROLE tenlines LOGIN PASSWORD 'a long random password';
GRANT CONNECT ON DATABASE shop TO tenlines;
GRANT USAGE ON SCHEMA public TO tenlines;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO tenlines;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO tenlines;

-- The same for tables made later, by whoever makes them (run it as that role):
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO tenlines;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO tenlines;

-- To make tables from the schema page as well. It can change the shape only of
-- tables it owns: the ones it made, or ones handed to it.
GRANT CREATE ON SCHEMA public TO tenlines;
ALTER TABLE orders OWNER TO tenlines;

-- 203.0.113.7: the address this server connects from.
CREATE USER 'tenlines'@'203.0.113.7' IDENTIFIED BY 'a long random password';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'tenlines'@'203.0.113.7';

-- To change tables' shape from the schema page as well:
GRANT CREATE, ALTER, INDEX, REFERENCES, DROP ON shop.* TO 'tenlines'@'203.0.113.7';

CREATE LOGIN tenlines WITH PASSWORD = 'a long random password';
USE shop;
CREATE USER tenlines FOR LOGIN tenlines WITH DEFAULT_SCHEMA = dbo;
ALTER ROLE db_datareader ADD MEMBER tenlines;
ALTER ROLE db_datawriter ADD MEMBER tenlines;

-- To change tables' shape from the schema page as well:
ALTER ROLE db_ddladmin ADD MEMBER tenlines;

Rows only is enough for the grid, the REST API and every script: scripts cannot change a table's shape anyway. Making and changing tables from the schema page needs more, as above; without it the database refuses those changes, the schema page says it could not make them, and everything else works.

What it does in your database #

Where the engines differ #

A table made somewhere else need not look like one made here. A table without an id column is listed, browsed and read in the grid but not edited there, and one without updated_at is written without it. Scripts read and write any table the login can.