SSL PosgreSQL

Hi,

I am using Supabase to work with posgreSQL. I am opting to set a certificate (SSL) for security. When trying to connect to the db I get the following error: certificate present, but not private key file "/Users/current_user/.postgresql/postgresql.key"

I only have an option to download the cert but Xojo is asking me for a key. Since i do not see that option in supabase for a key, where do i create the key or get it? I tried to look in the docs but did not see anything about a key creation.

I worked on a different project (Supabase) where the key was not required. Did something changed in the new release of Xojo?

Here is my code…

Var f As New FolderItem("my-cert.crt", FolderItem.PathModes.Native)
Var pgre As New PostgreSQLDatabase

pgre.SSLMode = PostgreSQLDatabase.SSLRequire
pgre.SSLCertificate = f
pgre.Host = host.Text
pgre.DatabaseName = DatabaseName.Text
pgre.Port = Port.Text.ToInteger
pgre.UserName = UserName.Text
pgre.Password = Password.Text

pgre.Connect

Thank you for your help

How did you create the key? I’d follow the instructions given here for example. I don’t think that supabase requires anything special in that area. Btw, interesting decision to go with supabase instead of plain Postgres, may I ask why you chose to go with supabase?

1 Like

Just for information, there was a limited free tier Cloud PostgreSQL at Heroku, and they announced the end of the free tier. Supabase has a limited free tier, US$0, lots of people migrated to it. Not saying that this is the reason, that’s one usual reason.

One possibility is writing an app that works in a demo mode using a preset demo DB, and having ways of your user to choose his own outsourced DB (payed) plan with that platform, after a trial using the free tier.

1 Like

Hi Maximilian,

Thank you for your response. See i was using Firebase for mobile apps. I am not too much of a fan for non-sql dbs and was looking for a rdbms since i was familiar with mysql. I found Supabase and provides almost everything that firebase offers. It has a FREE db, storage, authentication, realtime and out of the box it has a db rest api for postgre that you can use for insert, delete, update, select and even joins.

I am getting acquainted with postgresql, that is the reason of my questions. They just provide a button to download a certificate but not sure how to get the key, according to the article I read from xojo, yours and some others, it looks like a key should be provided with the cert. Supabase just provides the cert.

I also used SSLAllow and it works but as soon as I add the cert, it breaks asking for a .key file. Not sure if i have to have an instance of the postgresql server in my computer and create a cert and a key file. Not sure how it works to be honest. Trying to learn.

First a warning: I haven’t used Supabase, I haven’t used their SSL.

What happens if you comment out this line:

//pgre.SSLCertificate = f

My guess is that you are downloading a server certificate and trying to use it as a client certificate. As said before, I don’t know Supabase so I’m probably wrong.

I hope someone that uses Supabase and the SSL option can give you better information.

1 Like

You guys wanting to play with it should try a free account and play with this

Thank Rick,

I have read both the Xojo and Supabase docs. Connecting to your database | Supabase Docs

As you can see, the docs only show the SSL option and the download certificate button but no key. I have followed the docs and not sure they talk about a private key.

I know your difficulties, I’m giving other the direction so they can explore the options and the problem too.

Hopefully this is not distracting from the conversation too much, but is it safe for you app to just use an SSL connection over the internet for database reads/writes/etc.?

Yeah, i believe so. Specially on the internet where people can snoop and set a man-in-the-middle attacks. My opinion. This is the reason i am trying to learn how to set the SSL with postgre with my app.

If your database has a valid SSL certificate which has been created by a known Certificate Authority, then you don’t need the actual certificate, but a file which contains a list of valid certificate authorities

me.SSLMode = PostgreSQLDatabase.SSLVerifyFull
me.SSLAuthority=app.getresources(“cacert.pem”,“cacert.pem”) //downloaded from https://curl.se/ca/cacert.pem

Regards,

Lee

1 Like

Public clients (anything where a customer device may be involved) should NEVER have direct access to your database. SSL connections can be interrupted and inspected by the user.

1 Like

in my case Supabase only provides a button that says “Download Certificate” to connect to the postgre db over ssl. There is no key, pem or anything else.

So you set up an API layer to talk directly to the DB, correct?

Yes.

Last time I helped OP they were developing for their own use, so it’s not like it’s needed for every scenario. But if you’re building something where the app goes out to devices that are not considered confidential you will want an API layer.

Thanks. I will create another thread if I have any follow up questions. I don’t want to derail this topic any more than I have.

1 Like

hmmm,

SSL connections can be interrupted and inspected by the user.

if they inspect it what can they do without a key that allows them to actually inspect and see the raw data?

And yes you can use an API layer (prefer) but you also have other options like to connect to the db directly over ssl. My app is not public.

Actors who know what they are doing can inspect the communications and learn how to wreak their own havoc by forging their own requests. Everything I have said is standard security protocol described by people much smarter than me. If you want to argue, take it up with them.

I knew you were going to use this excuse which is why I wrote about our previous interaction.

Tim,

Not arguing just stating facts.

Everything I have said is standard security protocol described by people much smarter than me. If you want to argue, take it up with them.

SSL connections are secure otherwise all the million of sites using them including big corporations and this one can be attack and expose. But no, they use them to encrypt their data (customer data that is). The technology is not perfect but also not that easy to break. If that is the case, why do you use SSL in your site?

I knew you were going to use this excuse which is why I wasted time writing the sentence about our last interaction.

It is not an excuse, those are the facts unless you have some intel on my app that I am not aware of. As you mentioned, USE CASES drive the requirements. If my app is just for me running on my local machine for my own learning experience, would i need to fold my sleeves and start building a API layer because security says so?

Also, don’t waste your time Tim answering questions. Nobody is forcing you. You do it because you like to help ( i believe you do but with the “wasted time” comment it sounds like you may not?). Thank you for answering some of my questions in the past. I am totally grateful for your answers.

P.S. Also long back there was no SSL to encrypt the connection to connect to a db directly. That was one of the reasons this was not a good practice. I am not saying it is now but now we have a way to protect those connections unlike in the past. Also, it is up to the company building the app to understand the requirements and use cases to design the app accordingly. I agree with you security should be first.

It appears also that postgre allows you to do this PostgreSQL: Documentation: 16: 19.9. Secure TCP/IP Connections with SSL

You already ignored my advice previously so I have no more of my time to give you.