How to use keto with postgresql?

[Lispython]

Hi.

I tried to run keto with postgresql. Here my docker-compose config:

services:
  keto:
    image: oryd/keto:v0.6.0-alpha.3

    command: serve -c /home/ory/keto.yml

    ports:
      - '4466:4466'
      - '4467:4467'

    volumes:
      - ./ory/:/home/ory/

    depends_on:
      - postgresql

    environment:
      DSN: postgresql://user:[email protected]:5432/db

    networks:
      demo_app_network:
        aliases:
          - demo-app.dev
          
  keto-init:
    image: oryd/keto:v0.6.0-alpha.3

    environment:
      KETO_WRITE_REMOTE: demo-app.dev:4466
      KETO_READ_REMOTE: demo-app.dev:4467
      DSN: postgresql://user:[email protected]:5432/db

    volumes:
      - ./ory/:/home/ory/

    command: -c /home/ory/keto.yml relation-tuple create /home/ory/relation-tuples

    restart: on-failure

    depends_on:
      - postgresql

  keto-migrate:
    image: oryd/keto:v0.6.0-alpha.3

    env_file:
      - app.env

    environment:
      KETO_WRITE_REMOTE: demo-app.dev:4466
      KETO_READ_REMOTE: demo-app.dev:4467
      # DSN: memory
      DSN: postgresql://user:[email protected]:5432/db?sslmode=disable

    volumes:
      - ./ory/:/home/ory/

    command: -c /home/ory/keto.yml migrate up

    restart: on-failure

    depends_on:
      - postgresql

  postgresql:
    image: postgres:latest

    environment:
      POSTGRES_USER: user
      POSTGRES_DB: db
      POSTGRES_PASSWORD: pass

    networks:
      demo_app_network:
        aliases:
          - app-postgresql.dev


networks:
  demo_app_network:

and keto config

version: v0.6.0-alpha.3

log:
  level: trace
  leak_sensitive_values: true


namespaces:
  - id: 0
    name: videos

# dsn: memory
dsn: postgresql://user:[email protected]:5432/db?sslmode=disable

serve:
  read:
    host: 0.0.0.0
    port: 4466
  write:
    host: 0.0.0.0
    port: 4467

Migration command migrate up failed:

INFO[2021-05-05T12:22:19Z] No tracer configured - skipping tracing setup  audience=application service_name=ORY Keto service_version=master
WARN[2021-05-05T12:22:19Z] One or more of connection details are specified in database.yml. Override them with values in URL.  audience=application service_name=ORY Keto service_version=master source=pop
WARN[2021-05-05T12:22:20Z] One or more of connection details are specified in database.yml. Override them with values in URL.  audience=application service_name=ORY Keto service_version=master source=pop
WARN[2021-05-05T12:22:22Z] One or more of connection details are specified in database.yml. Override them with values in URL.  audience=application service_name=ORY Keto service_version=master source=pop```

What I am doing wrong?

[zepatrik]

What I am doing wrong?

A very good question, could reproduce but not find the cause yet 😅

Ok found it, you did not add the network to the migrate and init services. Also, you have to add the --yes flag to the keto-migrate command, see keto help migrate up 😉

Nevertheless, the connection error is not logged but really should be.

[Lispython]

Thanks for help!

[Lispython]

Why keto doesn't work without sslmode=disable parameter?

[zepatrik]

What do you mean by does not work? I assume you have to make sure that the certificate you use is trusted, so if you have a self-signed one you have to find out how to add it to the container. Never tried that so no idea...

[costap]

Hi I have a similar issue with mysql. I'm running in k8s

Config

version: v0.6.0-alpha.3

log:
  level: debug

namespaces:
  - id: 0
    name: videos

dsn: mysql://keto:keto@tcp(mysql:3306)/keto&max_conns=20&max_idle_conns=4

serve:
  read:
    host: 0.0.0.0
    port: 4466
  write:
    host: 0.0.0.0
    port: 4467

migrate job

apiVersion: batch/v1
kind: Job
metadata:
  name: migrate
spec:
  template:
    spec:
      containers:
        - args:
            - migrate
            - up
            - --yes
            - -c
            - /home/ory/keto.yml
          image: oryd/keto
          name: migrate
          volumeMounts:
            - mountPath: /home/ory
              name: keto-yml
      volumes:
        - name: keto-yml
          configMap:
            name: keto-yml
      restartPolicy: Never

Mysql is running, host is mysql, default port, there is a keto db and user

I'm missing something?
Thanks!

[zepatrik]

I have no idea what logs you get and where it fails. The original issue was not related to Keto itself but instead a docker compose miss-configuration. If you think you found a bug, please open an issue with all the details specified in the issue template.

[costap]

Sorry logs below didn't post as similar to above

❯ k logs migrate-4ktvf                                                                                                                                                                                                                [44/1893]
time=2021-05-17T12:14:20Z level=info msg=No tracer configured - skipping tracing setup audience=application service_name=ORY Keto service_version=master
time=2021-05-17T12:14:20Z level=debug msg=No SQL connection options have been defined, falling back to default connection options. audience=application service_name=ORY Keto service_version=master sql_max_connection_lifetime=0s sql_max_con
nections=6 sql_max_idle_connections=3                                                                                  
time=2021-05-17T12:14:21Z level=warning msg=One or more of connection details are specified in database.yml. Override them with values in URL. audience=application service_name=ORY Keto service_version=master source=pop
time=2021-05-17T12:14:21Z level=warning msg=One or more of connection details are specified in database.yml. Override them with values in URL. audience=application service_name=ORY Keto service_version=master source=pop
time=2021-05-17T12:14:23Z level=warning msg=One or more of connection details are specified in database.yml. Override them with values in URL. audience=application service_name=ORY Keto service_version=master source=pop
time=2021-05-17T12:14:25Z level=warning msg=One or more of connection details are specified in database.yml. Override them with values in URL. audience=application service_name=ORY Keto service_version=master source=pop
time=2021-05-17T12:14:27Z level=warning msg=One or more of connection details are specified in database.yml. Override them with values in URL. audience=application service_name=ORY Keto service_version=master source=pop

I don't know if its a bug TBH kind of feels I'm missing something

[costap]

OK found it your docker compose mysql mislead me, there is a typo in the DSN string

you have

mysql://root:secret@tcp(mysqld:3306)/mysql&max_conns=20&max_idle_conns=4

should be

mysql://root:secret@tcp(mysqld:3306)/mysql?max_conns=20&max_idle_conns=4

[frobones]

@costap Did you ever find an answer to your k8s problem? I have the same issue.