[BUG] Permify on AWS EKS using an RDS PostgreSQL

[fagihms]

Describe the bug Hello! I'm trying to use Permify on AWS EKS using an RDS PostgreSQL instance as database. When the pod try to start, it crashed after a while with the error message:

msg="failed to initialize database"
error="pinging pools failed: write pool ping failed: failed to connect to ... server error: FATAL: Feature not supported: RDS Proxy currently doesn’t support the option plan_cache_mode. (SQLSTATE 0A000)"

To Reproduce I applied the deployment using kubectl with the following configuration

apiVersion: apps/v1
kind: Deployment
metadata:
  labels:
    app: permify
  name: permify
spec:
  selector:
    matchLabels:
      app: permify
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: permify
    spec:
      serviceAccountName: aws-access-service-account
      restartPolicy: Always
      containers:
        - env:
            - name: PERMIFY_DATABASE_ENGINE
              value: postgres
            - name: PERMIFY_DATABASE_URI
              value: postgresql://$(DATASOURCE_USERNAME):$(DATASOURCE_PASSWORD)@$(DATABASE_HOST):$(DATABASE_PORT)/$(ENV_NAME)_permify
            - name: PERMIFY_DATABASE_MAX_OPEN_CONNECTIONS
              value: "20"
            - name: PERMIFY_LOG_LEVEL
              value: "trace"
            - name: PERMIFY_DATABASE_AUTO_MIGRATE
              value: "false"
          image: ghcr.io/permify/permify
          name: permify
          args:
            - "serve"
          ports:
            - containerPort: 3476
              protocol: TCP
          resources:
            limits:
              memory: 512Mi
            requests:
              memory: 256Mi

Expected behavior I expected that Permify can connect to my PostgreSQL database and initialize its tables.

Environment (please complete the following information, because it helps us investigate better):

• PostgreSQL version: Aurora PostgreSQL (Compatible with PostgreSQL 15.4) - default for major version 15
• Permify version: v0.9.8

[featurebase-for-github[bot]]

This issue is linked to our feedback platform. For feedback and updates, please visit this link.

[tolgaozen]

Hi @fagihms, could you add plan_cache_mode as force_custom_plan? Like this:

postgresql://%s:%s@%s/%s?plan_cache_mode=force_custom_plan?

[fagihms]

Hi!

I just tested it and it doesn't work.

Actually, the issue is more clear now. It seems that the RDS proxy located between the database instance and my Permify pod doesn't support the plan_cache_mode feature while the database itself support it. Indeed the plan_cache_mode feature was introduce in PostgreSQL at version 12 and currently I use version 15.4 of PostgreSQL engine in AWS. As temporary fix, I tried to connect to the instance database by bypassing the proxy and it works. Is it possible to implement an optional flag to disable the plan_cache_mode feature used by Permify ?

Something like:

- name: PERMIFY_DATABASE_PLAN_CACHE_MODE
  value: "false"

Thank you!

[fagihms]

Hi!

I contact the support of AWS and after trying from their side here is their response

I have replicated the issue from my end and I can confirm that RDS proxy can establish connection even though plan_cache_mode is set to auto or null. I will suggest that you try the connection using other client connection application, I suspect this could an issue with Permify since I can establish connection from my EC2 Instance.

With proxy

ubuntu@ip-172-xx-24-xxx:~$ psql -h testingissue.proxy-cw2ltalae4gk.xx-xx-x.rds.amazonaws.com -U xxx -d testdb -p 5432
Password for user testdb: 
psql (14.12 (Ubuntu 14.12-0ubuntu0.22.04.1), server 15.4)
WARNING: psql major version 14, server major version 15.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_128_GCM_SHA256, bits: 128, compression: off)
Type "help" for help.

testdb=> SHOW plan_cache_mode;

 plan_cache_mode 
-----------------
 auto
(1 row)

Without proxy

ubuntu@ip-172-xx-24-xx:~$ psql -h testdb.cluster-cw2ltalae4gk.xx-xxx-x.rds.amazonaws.com -U xxx -d testdb -p 5432
Password for user testdb: 
psql (14.12 (Ubuntu 14.12-0ubuntu0.22.04.1), server 15.4)
WARNING: psql major version 14, server major version 15.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

testdb=> SHOW plan_cache_mode;
 plan_cache_mode 
-----------------
 auto
(1 row)

NB: first connection is proxy and second is cluster endpoint

Thx

[tolgaozen]

Hello @fagihms, thank you for explaining your issue in detail. We will set up and test with RDS Proxy to try to identify the root cause of the issue.

[omer-topal]

Hello @fagihms,

We have fixed the issue. You will be able to test the fix in the next release.

To disable plan_cache_mode, please add plan_cache_mode=disable to the PERMIFY_DATABASE_URI as shown below:

- name: PERMIFY_DATABASE_URI
  value: postgresql://$(DATASOURCE_USERNAME):$(DATASOURCE_PASSWORD)@$(DATABASE_HOST):$(DATABASE_PORT)/$(ENV_NAME)_permify?plan_cache_mode=disable

Thanks!