On-Prem Deployment issue: "column sid does not exist"

Self Hosted Retool
1

I've run into this twice now, so thought I'd share here for future reference.

Context

  • I'm deploying the whole retool stack in a Docker Swarm, using a docker-compose file.
  • All Postgres details are passed in, as environment variables.
  • The database and the internal admin user are created separately, before deployment.

Setup

Simple database and user creation:
CREATE DATABASE retool; CREATE USER retool_admin WITH ENCRYPTED PASSWORD '[REDACTED]'; GRANT ALL PRIVILEGES ON DATABASE retool TO retool_admin;
Deployed the stack using this compose file:
`version: "3"
services:
api:
image: tryretool/backend:2.44.5
environment:
SERVICE_TYPE: API_SERVICE
DB_SSH_CONNECTOR_HOST: http://db-ssh-connector
DB_SSH_CONNECTOR_PORT: 3002
DB_CONNECTOR_HOST: http://db-connector
DB_CONNECTOR_PORT: 3002
NODE_ENV: production
POSTGRES_DB: ${POSTGRES_DB:-retool}
POSTGRES_HOST: ${POSTGRES_HOST}
POSTGRES_PORT: ${POSTGRES_PORT}

  POSTGRES_USER: ${POSTGRES_USER:-retool_admin}
  POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
  ENCRYPTION_KEY: ${ENCRYPTION_KEY}
  LICENSE_KEY: ${LICENSE_KEY}
  JWT_SECRET: ${JWT_SECRET}

depends_on:
  - db-ssh-connector
command: bash -c "chmod -R +x ./docker_scripts; sync; ./docker_scripts/start_api.sh"
ports:
  - "4000:3000"
volumes:
  - ssh:/retool_backend/autogen_ssh_keys
deploy:
  resources:
    limits:
      memory: 1G
    reservations:
      memory: 512M
  update_config:
    delay: 5s
  replicas: 1
  restart_policy:
    condition: on-failure

db-connector:
image: tryretool/backend:2.44.5
command: bash -c "./retool_backend"
environment:
SERVICE_TYPE: DB_CONNECTOR_SERVICE
deploy:
resources:
limits:
memory: 1G
reservations:
memory: 256M
update_config:
delay: 5s
replicas: 1
restart_policy:
condition: on-failure

db-ssh-connector:
image: tryretool/backend:2.44.5
command: bash -c "./docker_scripts/generate_key_pair.sh; ./retool_backend"
environment:
SERVICE_TYPE: DB_SSH_CONNECTOR_SERVICE
volumes:
- ssh:/retool_backend/autogen_ssh_keys
deploy:
resources:
limits:
memory: 1G
reservations:
memory: 256M
update_config:
delay: 5s
replicas: 1
restart_policy:
condition: on-failure

https-portal:
image: tryretool/https-portal:latest
links:
- api
environment:
STAGE: "local"
DOMAINS: "[REDACTED] -> http://api:3000"
ENCRYPTION_KEY: ${ENCRYPTION_KEY}
COOKIE_INSECURE: ${COOKIE_INSECURE:-'true'}
deploy:
resources:
limits:
memory: 1G
reservations:
memory: 256M
update_config:
delay: 5s
replicas: 1
restart_policy:
condition: on-failure

volumes:
ssh:`

Deployed

  • Everything starts up fine and runs perfectly.
  • The API connects to the database successfully and creates all the schemas and tables under the new database.

Issue

  1. I navigate to the web UI and try to sign up as the first user.
  2. Upon submitting, I get the following error:
    Column "sid" does not exist

    screenshot_12.png
    screenshot_12.png878×511 57.4 KB


    Apparently, the API service throws this error when checking if the user already exists:

Logs

Starting static http server on port 3000 {"requestId":"4a2896bb-fc34-468f-b5d6-0ca956f3d4e8","message":{"type":"REQUEST","method":"POST","url":"/api/signup","hostname":"[REDACTED]"},"level":"info","timestamp":"2019-09-04T08:34:41.686Z"} 2019-09-04T08:34:41:687 begin signup 2019-09-04T08:34:41:688 Looking for duplicate users SequelizeDatabaseError: column "sid" does not exist at Query.formatError (/snapshot/retool/node_modules/sequelize/lib/dialects/postgres/query.js:363:16) at run.query.catch.err (/snapshot/retool/node_modules/sequelize/lib/dialects/postgres/query.js:86:18) at tryCatcher (/snapshot/retool/node_modules/sequelize/node_modules/bluebird/js/release/util.js:16:23) at Promise.module.exports.Promise._settlePromiseFromHandler (/snapshot/retool/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:512:31) at Promise.module.exports.Promise._settlePromise (/snapshot/retool/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:569:18) at Promise.module.exports.Promise._settlePromise0 (/snapshot/retool/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:614:10) at Promise.module.exports.Promise._settlePromises (/snapshot/retool/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:690:18) at _drainQueueStep (/snapshot/retool/node_modules/sequelize/node_modules/bluebird/js/release/async.js:138:12) at _drainQueue (/snapshot/retool/node_modules/sequelize/node_modules/bluebird/js/release/async.js:131:9) at Async._drainQueues (/snapshot/retool/node_modules/sequelize/node_modules/bluebird/js/release/async.js:147:5) at Immediate.Async.drainQueues [as _onImmediate] (/snapshot/retool/node_modules/sequelize/node_modules/bluebird/js/release/async.js:17:14) at runCallback (timers.js:696:18) at tryOnImmediate (timers.js:667:5) at processImmediate (timers.js:649:5) at process.topLevelDomainCallback (domain.js:121:23)

Notes

  • The above used to work, the first time I deployed the stack. Unfortunately the database was dropped by accident, so I had to recreate everything.
  • I also tried making the admin user the owner of the DB, but it doesn't seem to be a permission thing?
  • There doesn't seem to be any documentation around what each of the services do, or what the database user requires.
    Any ideas as to why this user.sid column could be missing (and it is), would be very much welcome.
    If this gets resolved separately, I will update this thread with the solution.
    Cheers.
2

@will — do you have any thoughts here? Thanks!

3

Hi @pmetzdorf – thanks for the detailed stack traces + repro.

4

Off the top of my head, the most likely culprit is that the migration that adds the sid column also tries to run create extension if not exists "uuid-ossp";

5

the uuid-ossp extension allows us to create UUIDs in the database, and that’s what being used under the hood to generate sid values.

6

unfortunately, to enable the uuid-ossp extension, the database user needs admin privileges to the database. the easiest way to resolve this without changing the postgres user to is to manually run create extension if not exists "uuid-ossp"; yourself in the db you’ve created for Retool and then restart the Retool container.

7

The Retool container will attempt to re-run the migrations on startup which should end up solving your issue!

8

Hi @will, thank you for your response. Indeed, that did the trick. What is confusing is that the user we gave retool (retool_admin) is even a DBA admin on the retool database (although not on the whole RDS instance), but that apparently also wasn’t enough.