How to connect Retool with Zoho CRM (Self-hosted Retool)

[coda1024]

• Goal: I'm trying to set up OAuth 2.0 authentication for a Zoho CRM integration in my self-hosted Retool instance. The expected behavior is for the OAuth flow to complete successfully, allowing me to make authenticated requests to the Zoho CRM API.

I have followed the steps in the Solution by Ben (How to connect Retool with Zoho CRM)

and an error occurs after clicking on Connect With Oauth and the tokens are not returned to the resource

• Steps:
  

  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

• Details:

It appears that Zoho is attempting to return tokens to Retool as part of the OAuth flow, but there's an issue preventing the process from completing successfully

The callback includes expected parameters:
The URL contains parameters like state, code, location, and accounts-server, which are typical for OAuth callbacks from Zoho (Steps for generating OAuth Token | OAuth | Access | Zoho People)

The nginx server encounters a timeout while trying to forward the request to the Retool API service. This suggests that the Retool API is not responding within the expected time frame.

The server hosting these containers is not under any load

The Retool docker-compose logs show:

 
{
	"level": "info",
	"message": {
		"http": {
			"method": "GET",
			"url_base": "https://retool.backups.net.au",
			"url_path": "/oauth/user/oauthcallback?state=e74a4dc6-e7d3-4eb5-9f10-e77226c25b3d&code=1000.feb08f6e6c9631f94f78373cbee84720.47f6d28f8e0efad43a1744d28d006807&location=au&accounts-server=https%3A%2F%2Faccounts.zoho.com.au&"
		},
		"type": "REQUEST_BEGIN"
	},
	"pid": 84,
	"requestId": "4c440194-708a-49f6-82bd-ec635212742a",
	"timestamp": "2024-10-03T00:53:52.038Z"
}



nginx_1                 | 2024/10/03 00:54:51 [error] 29#29: *893 upstream timed out (110: Connection timed out) while reading response header from upstream, client: 10.4.1.2, server: 10.4.1.31, request: "GET /oauth/user/oauthcallback?state=e74a4dc6-e7d3-4eb5-9f10-e77226c25b3d&code=1000.feb08f6e6c9631f94f78373cbee84720.47f6d28f8e0efad43a1744d28d006807&location=au&accounts-server=https%3A%2F%2Faccounts.zoho.com.au& HTTP/1.1", upstream: "http://172.23.0.2:3000/oauth/user/oauthcallback?state=e74a4dc6-e7d3-4eb5-9f10-e77226c25b3d&code=1000.feb08f6e6c9631f94f78373cbee84720.47f6d28f8e0efad43a1744d28d006807&location=au&accounts-server=https%3A%2F%2Faccounts.zoho.com.au&", host: "retool.backups.net.au", referrer: "https://accounts.zoho.com.au/"

nginx_1                 | 10.4.1.2 - - [03/Oct/2024:00:54:51 +0000] "GET /oauth/user/oauthcallback?state=e74a4dc6-e7d3-4eb5-9f10-e77226c25b3d&code=1000.feb08f6e6c9631f94f78373cbee84720.47f6d28f8e0efad43a1744d28d006807&location=au&accounts-server=https%3A%2F%2Faccounts.zoho.com.au& HTTP/1.1" 504 569 "https://accounts.zoho.com.au/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36"

nginx_1                 | 10.4.1.2 - - [03/Oct/2024:00:54:51 +0000] "GET /favicon.ico HTTP/1.1" 304 0 "https://retool.backups.net.au/oauth/user/oauthcallback?state=e74a4dc6-e7d3-4eb5-9f10-e77226c25b3d&code=1000.feb08f6e6c9631f94f78373cbee84720.47f6d28f8e0efad43a1744d28d006807&location=au&accounts-server=https%3A%2F%2Faccounts.zoho.com.au&" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36"

Additional context:

• Retool version 3.75.3
• I have reinstalled retool on a new server with 8 cores and 16GB RRAM and tried the steps again with same result
• Hosting environment: Self-hosted Docker deployment (dockerised, nginx, externalised postgres, ssl enabled)
• OAuth provider: Zoho CRM

The issue persists even with a different computer and browser.

I've consulted the Retool documentation on OAuth configuration and tried various troubleshooting steps, including verifying the BASE_DOMAIN setting and double-checking all OAuth credentials.

Any assistance in resolving this callback URL issue would be greatly appreciated.

[Darren]

Hey @coda1024! Welcome back to the community.

My first instinct is that this is a networking issue - as many self-hosted issues are - but the redirect does seem to hit your api container, if I'm correctly reading the logs that you've shared. Is that the only relevant entry? Can you search the api logs for any other entries with the same requestId?

[coda1024]

I see what you mean, but I did get it working a different way so maybe there is not a networking issue

eg. this works

it doesnt work well though, after the access token expires the refresh auth doesnt work and a new code needs to be generated and entered into the resource

does this help debug whether its a network issue

it might, I dont know

[Darren]

Thanks for doing so much testing! It does seem that it's just the callback having an issue, given the fact that your token exchange works as expected. I think there's probably a way to get the refresh flow working, as well, but I'd prefer to figure out the authorization code grant. I spun up a quick Docker deployment, minus SSL, and everything seems fine.

Have you deployed with a separate nginx server as your upstream proxy? Or are you using the https-portal container that comes built-in with the Retool Docker templates? In general, it would be super helpful to see your docker-compose.yml file!

[coda1024]

Thanks Darren,
Heres a copy of the compose file - i think its the default one,
theres the nginx conf also

# Use as is if using self-managed Temporal cluster deployed alongside Retool
# Compare other deployment options here: https://docs.retool/self-hosted/concepts/temporal#compare-options
#version: "2"
services:
  api:
    build:
      context: ./
      dockerfile: Dockerfile
    env_file: ./docker.env
    environment:
      - DEPLOYMENT_TEMPLATE_TYPE=docker-compose
      - SERVICE_TYPE=MAIN_BACKEND,DB_CONNECTOR,DB_SSH_CONNECTOR
      - DBCONNECTOR_POSTGRES_POOL_MAX_SIZE=100
      - DBCONNECTOR_QUERY_TIMEOUT_MS=120000
      - WORKFLOW_BACKEND_HOST=http://workflows-backend:3000
      - WORKFLOW_TEMPORAL_CLUSTER_FRONTEND_HOST=temporal
      - WORKFLOW_TEMPORAL_CLUSTER_FRONTEND_PORT=7233
      - CODE_EXECUTOR_INGRESS_DOMAIN=http://code-executor:3004
    networks:
      - frontend-network
      - backend-network
      - temporal-network
      - code-executor-network
    depends_on:
      - postgres
      - retooldb-postgres
      - jobs-runner
      - workflows-worker
      - code-executor
    command: bash -c "./docker_scripts/wait-for-it.sh postgres:5432; ./docker_scripts/start_api.sh"
    links:
      - postgres
    ports:
      - "3000:3000"
    restart: on-failure
    volumes:
      - ./keys:/root/.ssh
      - ./keys:/retool_backend/keys
      - ssh:/retool_backend/autogen_ssh_keys
      - ./retool:/usr/local/retool-git-repo
      - ${BOOTSTRAP_SOURCE:-./retool}:/usr/local/retool-repo

  jobs-runner:
    build:
      context: ./
      dockerfile: Dockerfile
    env_file: ./docker.env
    environment:
      - DEPLOYMENT_TEMPLATE_TYPE=docker-compose
      - SERVICE_TYPE=JOBS_RUNNER
    networks:
      - backend-network
    depends_on:
      - postgres
    command: bash -c "chmod -R +x ./docker_scripts; sync; ./docker_scripts/wait-for-it.sh postgres:5432; ./docker_scripts/start_api.sh"
    links:
      - postgres
    volumes:
      - ./keys:/root/.ssh

  workflows-worker:
    build:
      context: ./
      dockerfile: Dockerfile
    command: bash -c "./docker_scripts/wait-for-it.sh postgres:5432; ./docker_scripts/start_api.sh"
    env_file: ./docker.env
    depends_on:
      - temporal
    environment:
      - DEPLOYMENT_TEMPLATE_TYPE=docker-compose
      - SERVICE_TYPE=WORKFLOW_TEMPORAL_WORKER
      - DISABLE_DATABASE_MIGRATIONS=true
      - WORKFLOW_BACKEND_HOST=http://workflows-backend:3000
      - WORKFLOW_TEMPORAL_CLUSTER_FRONTEND_HOST=temporal
      - WORKFLOW_TEMPORAL_CLUSTER_FRONTEND_PORT=7233
      - CODE_EXECUTOR_INGRESS_DOMAIN=http://code-executor:3004
    networks:
      - backend-network
      - temporal-network
      - code-executor-network
    restart: on-failure

  workflows-backend:
    build:
      context: ./
      dockerfile: Dockerfile
    env_file: ./docker.env
    environment:
      - DEPLOYMENT_TEMPLATE_TYPE=docker-compose
      - SERVICE_TYPE=WORKFLOW_BACKEND,DB_CONNECTOR,DB_SSH_CONNECTOR
      - WORKFLOW_BACKEND_HOST=http://workflows-backend:3000
      - WORKFLOW_TEMPORAL_CLUSTER_FRONTEND_HOST=temporal
      - WORKFLOW_TEMPORAL_CLUSTER_FRONTEND_PORT=7233
      - CODE_EXECUTOR_INGRESS_DOMAIN=http://code-executor:3004
    networks:
      - backend-network
      - temporal-network
      - code-executor-network
    depends_on:
      - postgres
      - retooldb-postgres
      - code-executor
    command: bash -c "./docker_scripts/wait-for-it.sh postgres:5432; ./docker_scripts/start_api.sh"
    links:
      - postgres
    restart: on-failure
    volumes:
      - ./keys:/root/.ssh
      - ./keys:/retool_backend/keys
      - ssh:/retool_backend/autogen_ssh_keys
      - ./retool:/usr/local/retool-git-repo
      - ${BOOTSTRAP_SOURCE:-./retool}:/usr/local/retool-repo

  code-executor:
    build:
      context: ./
      dockerfile: CodeExecutor.Dockerfile
    command: bash -c "./start.sh"
    env_file: ./docker.env
    environment:
      - DEPLOYMENT_TEMPLATE_TYPE=docker-compose
      - NODE_OPTIONS=--max_old_space_size=1024
    networks:
      - code-executor-network
    # code-executor uses nsjail to sandbox code execution. nsjail requires
    # privileged container access.
    # If your deployment does not support privileged access, you can set this
    # to false to not use nsjail. Without nsjail, all code is run without
    # sandboxing within your deployment.
    privileged: true
    restart: on-failure

  # Retool's storage database. See these docs to migrate to an externally hosted database: https://docs.retool.com/docs/configuring-retools-storage-database
  postgres:
    image: "postgres:11.13"
    env_file: docker.env
    networks:
      - backend-network
      - intra-temporal-network
    volumes:
      - data:/var/lib/postgresql/data

  retooldb-postgres:
    image: "postgres:14.3"
    env_file: retooldb.env
    networks:
      - backend-network
    volumes:
      - retooldb-data:/var/lib/postgresql/data


# Not required, but leave this container to use nginx for handling the frontend & SSL certification
  nginx:
    image: nginx:latest
    ports:
      - "8080:80"
      - "443:443"
    command: [nginx-debug, "-g", "daemon off;"] # Improve error logging in the container
    volumes:
      - /etc/nginx/nginx.conf:/etc/nginx/nginx.conf:ro
      - /etc/nginx/certs:/etc/nginx/certs:ro
    links:
      - api
    depends_on:
      - api
    restart: always
    env_file: ./docker.env
    environment:
      STAGE: "production" # <- Change 'local' to 'production' to use a LetsEncrypt signed SSL cert
      CLIENT_MAX_BODY_SIZE: 40M
      KEEPALIVE_TIMEOUT: 605
      PROXY_CONNECT_TIMEOUT: 600
      PROXY_SEND_TIMEOUT: 600
      PROXY_READ_TIMEOUT: 600
    networks:
      - frontend-network

  temporal:
    container_name: temporal
    env_file: ./docker.env
    environment:
      - DB=postgresql
      - DYNAMIC_CONFIG_FILE_PATH=config/dynamicconfig/development-sql.yaml
      # To enable TLS between temporal and external postgres, set both below variables to true
      - SQL_TLS_ENABLED=false
      - SQL_TLS=false
      # Defined twice because temporal-server and temporal-sql-tool use different envvars
      - SQL_TLS_DISABLE_HOST_VERIFICATION=true
      - SQL_HOST_VERIFICATION=false
    image: tryretool/one-offs:retool-temporal-1.1.2
    networks:
      - intra-temporal-network
      - temporal-network
    ports:
      - "127.0.0.1:7233:7233"
    volumes:
      - ./dynamicconfig:/etc/temporal/config/dynamicconfig
  temporal-admin-tools:
    container_name: temporal-admin-tools
    depends_on:
      - temporal
    environment:
      - TEMPORAL_CLI_ADDRESS=temporal:7233
    image: temporalio/admin-tools:1.18.5
    networks:
      - intra-temporal-network
    stdin_open: true
    tty: true


  temporal-ui:
    container_name: temporal-ui
    depends_on:
      - temporal
    environment:
      - TEMPORAL_ADDRESS=temporal:7233
      - TEMPORAL_CORS_ORIGINS=http://localhost:3000
    image: temporalio/ui:2.9.1
    networks:
      - intra-temporal-network
    ports:
      - "8081:8081"

networks:
  frontend-network:
  backend-network:
  code-executor-network:
  temporal-network:
  intra-temporal-network:

volumes:
  ssh:
  data:
  retooldb-data:

/etc/nginx/nginx.conf    

events {
    worker_connections 1024;
}

http {
    server {
        listen 80;
        server_name retool.backups.net.au;
        return 301 https://$server_name$request_uri;
    }

    server {
        listen 443 ssl;
        server_name retool.backups.net.au;

        ssl_certificate     /etc/nginx/certs/ca.crt;
        ssl_certificate_key /etc/nginx/certs/private.key;
        ssl_protocols TLSv1.2 TLSv1.3;
        ssl_prefer_server_ciphers on;
        ssl_session_cache shared:SSL:10m;
        ssl_session_timeout 10m;
        ssl_ciphers HIGH:!aNULL:!MD5;

        proxy_read_timeout 300s;
        proxy_connect_timeout 300s;
        proxy_send_timeout 300s;

        client_max_body_size 40M;

        location / {
            proxy_set_header Host $host;
            proxy_set_header X-Real-IP $remote_addr;
            proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
            proxy_set_header X-Forwarded-Proto $scheme;
            proxy_set_header X-Forwarded-Host $host;
            proxy_set_header X-Forwarded-Server $host;
            proxy_pass http://api:3000;
        }
    }
}

[Darren]

Thanks! Yep - your compose file looks pretty standard!

@coda1024

    image: nginx:latest
    ports:
      - "8080:80"
      - "443:443"

Probably not relevant, but is there a particular reason that you are exposing port 8080 here instead of just 80? Do you have a load balancer or proxy sitting in front of nginx?

At a high level, this is starting to feel like an SSL issue. I'll get some more eyes on this later today and hopefully come back with some more ideas.

[Darren]

In the meantime, could you also share a dump of your container logs? I'd be curious to see if there's any more information we can glean there. Feel free to DM me!

[coda1024]

there's no load balancer or proxy on the server and I can change it back to port 80

[coda1024]

I've implemented the zoho oauth with a python workflow for now as a workaround, maybe this will be a permanent fix...

auth block to get tokens from internal db

SELECT refresh_token, access_token, expiry  FROM zoho_auth

import requests
import time

base_url = "https://www.zohoapis.com.au/crm/v2"
token_url = "https://accounts.zoho.com.au/oauth/v2/token"
client_id = 'CLIENT_ID_HERE'
client_secret = 'CLIENT_SECRET_HERE'
redirect_uri = 'https://www.zoho.com/in/crm'


def get_access_token():
    access_token = auth.data[0].access_token
    expiry = float(auth.data[0].expiry)

    if time.time() >= expiry:
        return refresh_access_token()
    else:
        return access_token  # This is the return statement I forgot earlier

def refresh_access_token():
    print("Attempting to refresh access token...")
  
    refresh_token = auth.data[0].refresh_token

    payload = {
        'refresh_token': refresh_token,
        'client_id': client_id,
        'client_secret': client_secret,
        'grant_type': 'refresh_token'
    }
    response = requests.post(token_url, data=payload)
    if response.status_code == 200:
        data = response.json()
        access_token = data['access_token']
        expiry = int(time.time() + data.get('expires_in', 3600) - 300)  # 5 minutes buffer
        
        if 'refresh_token' in data:
            refresh_token = data['refresh_token']

        # Update the auth data in Retool
        auth.data[0].access_token = access_token
        auth.data[0].expiry = expiry
        auth.data[0].refresh_token = refresh_token

        print("Access token refreshed successfully")
        return access_token
    else:
        print(f"Failed to refresh token. Status: {response.status_code}")
        print(f"Response: {response.text}")
        raise Exception("Failed to refresh access token")


def make_request(method, endpoint, data=None, params=None):
    url = f"{base_url}/{endpoint}"
    headers = {
        "Authorization": f"Zoho-oauthtoken {get_access_token()}",
        "Content-Type": "application/json"
    }

    try:
        response = requests.request(method, url, headers=headers, json=data, params=params)

        if response.status_code == 401:
            print("Received 401 Unauthorized. Attempting to refresh token.")
            headers["Authorization"] = f"Zoho-oauthtoken {get_access_token()}"
            response = requests.request(method, url, headers=headers, json=data, params=params)

        response.raise_for_status()
        return response.json()
    except requests.exceptions.RequestException as e:
        print(f"Error making request: {e}")
        print(f"Response status code: {e.response.status_code if e.response else 'N/A'}")
        print(f"Response content: {e.response.text if e.response else 'N/A'}")
        raise

return refresh_access_token()

def get_contact_by_id(contact_id):
    endpoint = f"Contacts/{contact_id}"
    try:
        response = make_request("GET", endpoint)
        if response and 'data' in response and len(response['data']) > 0:
            contact = response['data'][0]
            print(f"Contact Details for ID {contact_id}:")
            for key, value in contact.items():
                if isinstance(value, dict):
                    print(f"  {key}:")
                    for sub_key, sub_value in value.items():
                        print(f"    {sub_key}: {sub_value}")
                else:
                    print(f"  {key}: {value}")
            return contact
        else:
            print(f"No contact found with ID: {contact_id}")
            return None
    except Exception as e:
        print(f"Error fetching contact details for ID {contact_id}: {str(e)}")
        return None



contact = get_contact_by_id("82060000001022615")

return json.dumps(contact)

[Darren]

Glad to hear that this workaround will suffice for the foreseeable future! I have a few different ideas for further debugging and won't leave this issue unresolved, as it seems like we're close.