SSL Connection to remote database

[jdearing-neudesic]

I am trying to run Pyrseas against an azure postgres database that requires an azure connection. I get ``FATAL: SSL connection is required. Please specify SSL options and retry.`

Full stack:

~/.local/bin/yamltodb -H pgsql-wah.postgres.database.azure.com -U owner@pgsql-foo -n someSchema -W someDatabase someDatabase_someSchema.yaml
Password:
Traceback (most recent call last):
  File "/home/jdearing/.local/bin/yamltodb", line 8, in <module>
    sys.exit(main())
  File "/home/jdearing/.local/lib/python3.8/site-packages/pyrseas/yamltodb.py", line 50, in main
    stmts = db.diff_map(inmap)
  File "/home/jdearing/.local/lib/python3.8/site-packages/pyrseas/database.py", line 511, in diff_map
    self.from_catalog()
  File "/home/jdearing/.local/lib/python3.8/site-packages/pyrseas/database.py", line 349, in from_catalog
    self.db = self.Dicts(self.dbconn, single_db)
  File "/home/jdearing/.local/lib/python3.8/site-packages/pyrseas/database.py", line 92, in __init__
    self.schemas = SchemaDict(dbconn)
  File "/home/jdearing/.local/lib/python3.8/site-packages/pyrseas/dbobject/__init__.py", line 633, in __init__
    self._from_catalog()
  File "/home/jdearing/.local/lib/python3.8/site-packages/pyrseas/dbobject/__init__.py", line 640, in _from_catalog
    for obj in self.fetch():
  File "/home/jdearing/.local/lib/python3.8/site-packages/pyrseas/dbobject/__init__.py", line 682, in fetch
    self.query = self.cls.query(self.dbconn.version)
  File "/home/jdearing/.local/lib/python3.8/site-packages/pyrseas/database.py", line 77, in version
    self.connect()
  File "/home/jdearing/.local/lib/python3.8/site-packages/pyrseas/database.py", line 63, in connect
    super(CatDbConnection, self).connect()
  File "/home/jdearing/.local/lib/python3.8/site-packages/pgdbconn/dbconn.py", line 50, in connect
    raise exc
  File "/home/jdearing/.local/lib/python3.8/site-packages/pgdbconn/dbconn.py", line 43, in connect
    self.conn = connect("%s%sdbname=%s%s%s" % (
  File "/home/jdearing/.local/lib/python3.8/site-packages/psycopg2/__init__.py", line 127, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
FATAL:  SSL connection is required. Please specify SSL options and retry.

this github repo doesn't mention anything about SSL.

[jmafc]

As you can see from the trace, Pyrseas uses pgdbconn (see https://github.com/perseas/pgdbconn) which in turn relies on Psycopg2. Pgdbconn used to be part of Pyrseas but we split it because I thought perhaps we could use it in other related projects, but that never panned out. It's a very slim wrapper around psycopg2 and perhaps at this stage it could be put back into Pyrseas.
In any case, Psycopg2 doesn't have much to say about SSL either and I'm afraid I've never used SSL, programmatically with psycopg. As I understand, what would be needed is to add the libpq sslmode parameter to the pgdbconn DbConnection.__init__ method, so that it could be used when it invokes psycopg2's connect. In a pinch, you could edit the dbconn.py file in pgdbconn to add the sslmode parameter to the connection string on line 43. It seems that by default, sslmode is prefer (implying SSL should normally work, see https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS), but perhaps Azure has special needs.
@dvarrazzo knows much more about this than I do, like if there some way to specify the sslmode in envvars or something like that, so hopefully he can comment here too.

[dvarrazzo]

You can use env vars such as PGSSLMODE to work around pgdbconn getting in the way.

[jdearing-neudesic]

It seems like the issue was with the particular user and it worked fine with the admin user.