Issue connecting to external Clair/Quay databases

Environment

• Red Hat Quay
  • 3.8

Issue

• I. When attempting to connect to external postgres databases for Quay and Clair, user is trying to connect to the "public" schema, but it says that is not allowed. What is missing in configuration?
• II. Is it is possible to use one service account/user to access both quay and clair's database?

Resolution

I. For separate users accessing separate databases:

1. The problem can be solved by specifying a [search path](https://postgresqlco.nf/doc/en/param/search_path/) to the quay/clair database for service account/user.

   • Command used on Quay database:

   ALTER ROLE "role_name" SET search_path TO quay_username;
   

   • Command used on Clair database:

   ALTER ROLE "role_name" SET search_path TO clair_username;
   

   Note: This will not allow one user to access quay and clair at the same time.

2. The problem can also be solved by adding a search_path in Quay and Clair config.yaml.

• quay-app pod expects database to be created in advance so quay-app-upgrade pod sets it up. Hence schemas and tables are already populated. This causes an issue with setting search_path for Quay. Solution is:

  • Add below lines in Quay config.yaml

    DB_URI: postgresql://<user>:<pass>@quayhostname.example.com:<port>/quay_username?options=-csearch_path%3Dquay_username
    

  • "Reset" the Quay DB by cleaning up and restart quay-upgrade-app pod.

• Configure the clair connstring to use a search_path with the below line:

  indexer:
    connstring: host=<dbname> port=5402 dbname=db_name user=<clair_username> password=<pass> sslmode=disable options=--search_path=clair_username
  

II. For single service account/user accessing both DBs

• Yes it is possible to use one single service account/user to access both DBs. A database contains one or more named schemas, which in turn contain tables. Unlike databases, schemas are not rigidly separated: a user can access objects in any of the schemas in the database they are connected to, if they have privileges(minimum CREATE rights). One would need to ensure that the tables(of quay + clair) are part of that same schema, otherwise unqualified queries wouldn't be able to find the tables. And the queries from Quay/Clair are all unqualified, as they expect the tables to be accessible from the DB connection by default.

Root Cause

I. By default if one does not set a schema, it connects to the public schema. And public schema is only accessible by the Postgres Admins. Setting search_path helps

Diagnostic Steps

• Check Quay debug logs for below error :

$ oc logs quay-app | grep "public schema"

{"level":"error","component":"main","error":"service initialization failed: failed to initialize indexer: failed to perform migrations: ERROR: permission denied for schema public (SQLSTATE 42501)","time":"2023-03-21T19:12:05Z","message":"fatal error"}
.
.
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InsufficientPrivilege) permission denied for schema public

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.