Non-Superuser postgres user

By default, Tines expects the DATABASE_USERNAME to be a superuser so it can create the database, enable extensions, and apply user-level session configuration during setup and upgrades.

If your environment requires a least-privilege approach, you can use a non-superuser — but you'll need to handle a few things manually before starting Tines.

Warning: All steps in this guide must be completed by a Postgres superuser before starting Tines for the first time. Skipping any step will cause Tines to fail on startup.

Prerequisites 

Before starting Tines with a non-superuser, a superuser must complete the following steps.

1. Create the database and user 

Replace tines_user, <password-here>, and tines_db with the values you intend to set for DATABASE_USERNAME, DATABASE_PASSWORD, and DATABASE_NAME respectively.

-- Create the Tines user. Values should match DATABASE_USERNAME and DATABASE_PASSWORD.
CREATE USER tines_user WITH PASSWORD '<password-here>';

-- Create the database. Value should match DATABASE_NAME.
CREATE DATABASE tines_db;

-- Make the user the owner so they have full privileges on the database.
ALTER DATABASE tines_db OWNER TO tines_user;

Note: The DATABASE_USER must have permission to create, use, and drop objects (tables, sequences, indexes) within the database. Making them the database owner is the simplest way to guarantee this.

2. Enable required extensions 

Tines requires the following Postgres extensions to be enabled before running for the first time.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS plpgsql;
CREATE EXTENSION IF NOT EXISTS btree_gin;

Warning: CREATE EXTENSION requires superuser privileges. A non-superuser cannot enable extensions — this step must be run as a superuser.

Warning: When upgrading Tines, new extensions may be introduced. Always check the release notes before running a migration and enable any new extensions manually before starting the upgraded version.

3. Apply required session configuration 

Tines configures the following parameters at the user level to optimise connection performance. If tines_user is not a superuser, these settings must be applied by a superuser in advance.

ALTER USER tines_user SET standard_conforming_strings = on;
ALTER USER tines_user SET intervalstyle = 'iso_8601';
ALTER USER tines_user SET client_min_messages = 'warning';
ALTER USER tines_user SET lock_timeout = '5000';
ALTER USER tines_user SET idle_in_transaction_session_timeout = '65000';

Note: These settings become the persistent defaults for tines_user across all connections. This avoids the overhead of applying them on every individual connection at runtime.

Warning: If these settings are not applied before starting Tines, the application will be unable to configure its database connection correctly and will fail to start.

Using a Schema Other Than public 

By default, Tines creates all objects in the public schema and assumes search_path remains at its default. To use a different schema:

  • Ensure tines_user owns the schema or has been granted all privileges on it

  • Set the DATABASE_SCHEMA environment variable

  • Configure the search_path on the user, or set DATABASE_SCHEMA_SEARCH_PATH

Note: If search_path is not configured correctly, Tines will be unable to locate its tables and will fail on startup.

Was this helpful?