SCRAM authentication to Postgres server on AWS

@Christian_Schmitz: guessing you may have some input on this?

I am trying to connect to a PG server on AWS which is now Postgres 14.x, where in the past the server used PG 13.x and my connection is being refused with the error:

SCRAM authentication requires libpq version 10 or above

Environment: Xojo 2021R2.1
MBS Complete Plugins: 22.3 (build 20802)
MBS SQL Plugin: latest from July 2022, reports itself as v 5.2.1

I had previously been using the (now deprecated) InternalPostgresLibraryMBS and web searches suggest I should update the libpq version used on the client. So I switched to the libpq.5.14.dylib from the MBS library download ( here ) but still have the same problem. Stepping through debug shows the .setFileOption is being called:

dim pgLib as FolderItem = App.ExecutableFile.Parent.Child("lib").Child("libpq.5.14.dylib")
if pgLib = nil or not pgLib.Exists then
  dim exc as new RuntimeException
  exc.Message = "Can't find PostgreSQL macos library " + pgLib.NativePath
  Raise exc
end if

self.setFileOption(self.kOptionLibraryPostgreSQL, pgLib)

Is the error perhaps misleading and it is something else? I am seeing this both on a M1 and Intel based machine. It seems like libpq.5.14 shoud handle SCRAM just fine.

Just for information, as far as I know, SCRAM is natively supported in Xojo since 2021R2.

Rick is right.

https://documentation.xojo.com/resources/release_notes/2021r2.html

And I am using 2021R2.1 for this project, but also using MBS in part because I am also using via a SSH2 tunnel (as I did in the past) using SSH2SessionMBS. The tunnel is instantiated just fine. Where I am failing is the in the .Connect() method of the SQLDatabaseMBS class, and the exception thrown states the problem is the SCRAM authentication.

I am aware of this thread which talks about scram-sha-256 authentication being added in Xojo 2021R2.1

But I also need to tunnel via SSH2, and in the past was able to use MBS just fine for this.

1 Like

We do have newer PostgreSQL libraries here:

https://www.monkeybreadsoftware.de/xojo/download/plugin/Libs/

I am already trying that, using the libpq.5.14.dylib from the link given in the original post. It is dated Jan 22, 2022. The Readme in that dmg file says the files were extracted from Postgres.app v2.5.2

Okay, I got confused.
So you have a recent library and it should do Scram authorization.
No idea currently why this fails.

Maybe it needs to be enabled?

Thanks for the reply. See the original thread post. I am using the .setFileOption() and stepping through debug the line is executed and finds the file added via a build CopyFiles step.

The .connect fails with error class 3 (a DBMS error per your docs) and the text given at the top of the thread. It is almost like the error description is wrong, because SCRAM seems like it should be supported by libpq.5.14.dylib

If you mean it needs to be enabled on the AWS server instance, I am told it is enabled but md5 is not currently enabled.

I wonder if you need to add something to connection string.
e.g.
sslmode=require channel_binding=require

to enable SSL and channel binding.

@Christian_Schmitz: I already was using sslmode=prefer but changed to sslmode=require with the same effect (“SCRAM authentication requires libpq version 10 or above”). If I add the channel_binding=require, then I get this error message:

invalid connection option “channel_binding”

Yet that does seem like the right syntax, at least as used near the bottom of this page or here .

I’m using the environment in my first post: MBS 22.3 (Build 20802); MBS SQL version reports at 5.2.1, and setting the fie option pointing to libpq.5.14.dylib

Would that error message be coming from the AWS instance of the PG 14 server?

As an idea. Try to install libpq using “brew”

And to use this version

Well, if you have a test database, you could send me a test project with a test login to verify.

The dylib has the text channel_binding, so it should know the option.

I’ve now also tried the libpq.5.14.dylib from the homebrew install, and it has the same message. If I include the channel_binding=require it complains about the channel_binding. If I leave that plus the sslmode off, I get connection refused.

I’ll make a small sample project to a test user and test db to pass your way…

1 Like

Strangely, if I start with the MBS example project “PostgreSQL Connect”, I can make a connection to the server even over a SSH2 tunnel (also done via MBS). But it is using SQLConnectionMBS and SQLCommandMBS, where my real project uses SQLDatabaseMBS.

I’ve tried many permutations of making that work and have not found the magic bullet yet. I sample project has been sent to @Christian_Schmitz to see if he spots why.

It works here.

I just commented out the SQLConnectionMBS code above. Because the tunnel only forwards one connection, the second connection would be refused.

Very odd; that works but when I try the same thing in my large project the connection fails with "invalid connection option “channel_binding” message. And I have triple checked it is using the exact same files like the same libpq.5.14.dylib in the SetFileOption call.

The only difference so far seems to be that I have my own class whose super is SQLDatabaseMBS, so next I will modify the sample project to try it instead.

Can you search your hard disc for multiple libpq files?

Or run the project on another computer?

I would have multiple libpq files, but I am specifically using your SetFileOption() call and have verified the path to the FolderItem in the debugger. And like you said, in the small sample project where I create a SQLDatabaseMBS object directly, it is working for me too once I comment out the SQLConnectMBS stuff.

My large project is still failing, so I am in the process of merging more of the classes from that project into the sample to see if it still works there…

Well, I got it resolved. I actually have two different connections to PG servers going – a local PG and a remote PG connection over a SSH2 tunnel. It was the remote that was failing, and I failed to notice the local PG was using the InternalPostgresLibraryMBS library. And that must be an older version of libpq that does not handle the connection.

Then even though my second remote PG connection specified to not use it but set the file option to use the copy of libpq that I specified, it apparently still used the internal libpq (a form of DLL hell?). Once I changed my local PG connection to also use the libpq which I specified, it all worked.

But the design of the program is such that the local PG connection is always made first, and thus the wrong copy of libpq was already in memory by the time I tried to establish the second connection.

Moral of the story: if using the MBS SQL plugin and needing SCRAM support, do NOT use the InternalPostgresLibraryMBS and instead use the Postgres Mac libraries that MBS has available here

Thanks.
And that is the reason we may deprecate/remove InternalPostgresLibraryMBS.