Configuring a PostgreSQL database for yatai-service

[jiyer2016]

My end goal was to set the following in the environment: (notice the userid and password are missing in the url)
export BENTOML__YATAI_SERVICE__DB_URL = "postgresql://host:port/database"

and then configure the user id and password in a ~.pgpass file as described in https://www.postgresql.org/docs/9.1/libpq-pgpass.html
This allows the environment variables to be free of sensitive user ids and passwords.

When I do this - the first hurdle I meet is an error from this code:

/bentoml/yatai/db.py
if not database_exists(engine.url)

The error originates from sqlalchemy_utils library v0.36.8.
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL: database "user" does not exist
It turns out that this is a bug in sqlalchemy_utils described in kvesteri/sqlalchemy-utils#462

The suggestion is to downgrade to 0.36.7 - which I did but now leads to a different error from database_exists:
OperationalError: (psycopg2.OperationalError) fe_sendauth: no password supplied

I was able to reproduce this issue in my own test program using v0.36.7 of sqlalchemy_utils.

engine = create_engine(db_url, **extra_db_args)
database_exists(engine.url)
`OperationalError: (psycopg2.OperationalError) fe_sendauth: no password supplied`

Strangely enough even though database_exists fails - the following works like a charm:

engine = create_engine(db_url, **extra_db_args)
sql = 'select table_name, table_schema from information_schema.tables'
df = pd.read_sql_query(sql, con=engine)

table_name | table_schema
-- | --
deployments | public
bentos | public
pg_type | pg_catalog
alembic_version | public
pg_subscription | pg_catalog

BTW, none of these issues occur with v0.36.7 of sqlalchemy_utils if I set the user id and password explicitly in the environment variable.

export BENTOML__YATAI_SERVICE__DB_URL = "postgresql://user:password@host:port/<database>"

While this is a workaround for the moment - My app cannot pass a security review.

I think this is more of an issue with the BentoML's depedencies - but I want to know if others also face similar issues - and what they are doing about this.

[yubozhao]

@jiyer2016 I think you raised a really good point on not expose the user and password as part of the environment variables for the Postgres database.

I think there are a couple of things we can do to make BentoML a little bit smarter and adopt the best practices.

1. Like your suggested, downgrade the sqlalchemy_utils version, and reinvestigate when the fix database existence check kvesteri/sqlalchemy-utils#463 is merged and new version is released.

2. If the db url is postgresql, BentoML should look for .pgpass in the user's home directory or the file referenced by PGPASSFILE. Maybe also could provide something like BENTOML__YATAI_SERVICE__PG_PASSFILE.

[jackyzha0]

Following this conversation because the current Helm chart implementation also uses env vars which is not ideal

[jiyer2016]

@yubozhao - On the second item - I have a follow-up question. Why would bentoML need to have logic to handle .pgpass or PGPASSFILE ? Wouldn't that be automatically handled by the psycopg2 driver for postgreSQL ?

[yubozhao]

@jiyer2016 Good question. I don't know will SQLAlchemy handles it as well, even with psycopg2 as its default DBAPI for the postgres connection. I am going to test it and see does BentoML needs to handle it or not.

[yubozhao]

@jiyer2016 I create a PR to lock the sqlalchemy-utils version at #1078

I also tested the yatai service with .pgpass file. This is what I have to make it work.

For my .pgpass file, I created it in the location suggested in the Postgres documentation, ~/.pgpass

• make sure you escape any values that contain \ or :.
• change the .pgpass file's permission to 0600

I used a local Postgres docker container with the same user and password values from the .pgpass file

$ docker run --rm -e POSTGRES_PASSWORD=my_password -e POSTGRES_USER=my_user -p 5432:5432 postgres

For my yatai service command, I am now able to pass in the Postgres URL without user/password.

$ bentoml yatai-service-start --db-url postgresql://localhost:5432/bentoml

If you are running Postgres on ubuntu, the error you encountered fe_sendauth: no password supplied, could be related to how Postgres use authentication for connection. You can find out more about it at https://help.ubuntu.com/community/PostgreSQL#fe_sendauth:_no_password_supplied

[jiyer2016]

Thanks @yubozhao - I am on RHEL. I shall check the suggested solution and get back.

[yubozhao]

@jiyer2016 Have you had chance to give this another try? Let me know if you still encounter this issue on RHEL.

[jiyer2016]

Not yet - Let me get back on this.