How to connect to postgresql with ssl

[etayruben]

I’m trying to connect my label studio to posgresql with client.crt and client.key certificates but i can’t manage to do it

Label studio version: V1.4
OS: linux
Trying to run it in a docker container

This is the error I’m getting:
The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/usr/local/bin/label-studio", line 8, in ‹module»
sys.exit(main())
File "/usr/local/lib/python3.6/site-packages/label studio/server.py", line 283, in main
apply _database migrations ()
File "/ur/local/lib/python3.6/site-packages/label_studio/server.py", line 57, in
apply database migration
if not is database synchronized (DEFAULT DB ALIAS) :
File
"/usr/local/lib/python3.6/site-packages/label_studio/server.py", line 51, in is database synchronized
executor = MigrationExecutor (connection)
File
"/usr/local/lib/python3.6/site-packages/django/db/migrations/executor.py",line18,in
init
self.loader = MigrationLoader(self.connection)
File
"/usr/local/lib/python3.6/site-packages/django/db/migrations/loader.py",line53,in
init
self.build graph()
File "/usr/local/lib/python3.6/site-packages/django/db/migrations/loader.py",line216,in build graph
self.applied migrations = recorder.applied migrations ()
File
"/usr/local/lib/python3.6/site-packages/django/db/migrations/recorder.py",line77,inappliedmigratic
if self.has table():
File "/usr/local/lib/python3.6/site-packages/django/db/migrations/recorder.py",line55,inhastable
with self. connection. cursor) as cursor:
File "/usr/local/lib/python3.6/site-packages/django/utils/asyncio.py",line26,ininner
return func(*args,
**kwargs)
File "/usr/local/lib/python3.6/site-packages/django/db/backends/base/base.py",line 259, in cursor
return self. cursor()
File
"/usr/local/lib/python3.6/site-packages/django/db/backends/base/base.py",line235,in_cursor
self.ensure connection()
File "/usr/local/lib/python3.6/site-packages/django/utils/asyncio.py",line26,ininner
return func (*args,
«*kwargs)
File "/usr/local/lib/python3.6/site-packages/django/db/backends/base/base.py",line 219, in ensure connectior
self.connect()
File "/usr/local/lib/python3.6/site-packages/django/db/utils.py",line90,in
exit
raise di exc value.with traceback(traceback) from exc value
File "/usr/local/lib/python3.6/site-packages/django/db/backends/base/base.py",line 219, in ensure connection
self.connect()
File "/usr/local/lib/python3.6/site-packages/sentry_sdk/integrations/django/init_.py",line583,inconnec
return real connect(self)
File "/usr/local/lib/python3.6/site-packages/django/utils/asyncio.py",line26,ininner
return func(*args,
**kwargs)
File "/usr/local/lib/python3.6/site-packages/django/db/backends/base/base.py",line200,in connect
self.connection = self.get _new connection(conn params)
File "/usr/local/lib/python3.6/site-packages/django/utils/asyncio.py",line26,ininner
return func (*args,
**kwargs)
File
"/usr/local/lib/python3.6/site-packages/django/db/backends/postgresql/base.py",line187,inget_newconr
connection = Database.connect (**conn params)
File "/usr/local/lib/python3.6/site-packages/psycopg2/_
init
_.py", line 122, in connect
conn =
connect (dsn, connection factory=connection factory,
**kwasync)
django.db.utils.OperationalError: FATAL: connection requires a valid client certificate
FATAL: pg_hba. conf rejects connection for host "121.190.0.218", user "label-studio", database "spectrum", SSL off

[farioas]

It's not clear what is your setup: pg version? how you created certs? how you deliver them into container, etc

[etayruben]

I created the certs through using openssl and i know they work because i use them in my PgAdmin4 and some code i wrote with sqlalchemy

Im using PostgresSQL 12.2

I deliver the certs to the container with COPY command from my docker file and i change the mode of the certs to 600 after

[Michael-shihao-li]

I have the same issue. I configured a GCP cloud run with label studio community edition to connect to a GCP PostgreSQL version 15.

1. When I enabled the SSL mode as "Allow unencrypted network traffic (not recommended)" or "Allow only SSL connections" on the GCP PostgreSQL, the label studio could start properly.
2. When I enabled the SSL mode as "Require trusted client certificates" on the GCP PostgreSQL, I got the following error:

psycopg2.OperationalError: connection to server at "10.96.48.50", port 5432 failed: FATAL:  connection requires a valid client certificate

By the way, I tested the same certificates with other container images, such as Pgadmin, directus, and retool, and it worked.
3. The main issue is that I could not find the environment variables I needed to specify to enable the SSL connection for the label studio. I could not find any documentation about the environment variables that enable an SSL connection between Label Studio and Postgresql. However, I found the documentation for other container images such as Pgadmin, directus and retool.
Here are the env variables and the Dockerfile sample I tested for the Label Studio SSL connection:

# Use the official Label Studio image as a parent image
FROM --platform=linux/amd64 heartexlabs/label-studio:latest

# Set environment variables for Label Studio
ENV DATABASE_URL=postgres://myuser:mypassword@myhost:5432/mydatabase
ENV DATABASE_SSLMODE=require
ENV DATABASE_SSLROOTCERT=/etc/ssl/certs/ca-certificates.crt
ENV DATABASE_SSLCERT=/etc/ssl/certs/client-cert.pem
ENV DATABASE_SSLKEY=/etc/ssl/private/client-key.pem

# Copy the SSL certificates into the container
COPY path/to/ca-certificates.crt /etc/ssl/certs/ca-certificates.crt
COPY path/to/client-cert.pem /etc/ssl/certs/client-cert.pem
COPY path/to/client-key.pem /etc/ssl/private/client-key.pem

# Expose the port Label Studio runs on
EXPOSE 8080

# Run Label Studio
CMD ["label-studio"]

I also tested it with the following env variable:

DATABASE_URL=postgres://postgres:[email protected]:5432/postgres?sslmode=verify-full&sslrootcert=/label-studio/root.crt&sslcert=/label-studio/postgresql.crt&sslkey=/label-studio/postgresql.key

The above env variables did not work for the SSL mode: verify-full

@makseq Could you please share the environment variable names I need to configure for the label studio SSL connection with PostgreSQL?

[Michael-shihao-li]

Hi @makseq, I also found the PR to enable TLS on pgsql: https://github.com/HumanSignal/label-studio/pull/1737/files. it for the PostgreSQL version 11.5.
I am using GCP postgresql 15 or 16 and GCP cloud run for label studio community edition now and it has the following three certificates:

• server-ca.pem
• client-cert.pem
• client-key.pem

Could you please share the instructions for the SSL connection?