Postgresql SSL Connection

[digital32]

I'm using PureBasic 5.44 and talking to a Postgresql database. I'm getting an error "no pg_hba.conf entry for host "172.1xx.xx.x", user "myID", database "MyDB", SSL off”

If I modify the pg_HBA.conf file it will let me in. But this may be a violation of security in my company.

PGAdmin 4 works fine because it can do SLL encryption.

Has this been resolved in newer versions of PureBasic?

Note: Turning SSL off for my connection may not be an option.

[Thorium]

I'm using PureBasic 5.44 and talking to a Postgresql database. I'm getting an error "no pg_hba.conf entry for host "172.1xx.xx.x", user "reports", database "portal", SSL off”

If I modify the pg_HBA.conf file it will let me in. But this may be a violation of security in my company.

PGAdmin 4 works fine because it can do SLL encryption.

Has this been resolved in newer versions of PureBasic?

Note: Turning SSL off for my connection may not be an option.

I don't think is SSL was been added to any of PureBasics functions. Same for HTTPS.
I guess for databases it's not a big issue because most people run the database only on the local host and do not let it communicate over the network. So all communication to and from the database never leaves the host machine.

[spikey]

I guess for databases it's not a big issue because most people run the database only on the local host and do not let it communicate over the network. So all communication to and from the database never leaves the host machine.

I'd disagree with this. In my experience most databases are remote to the client machines in a server room somewhere and almost all communication is done over a network. If you can hack any of the devices on the LAN (up to and including the server) you could install a packet sniffer to capture any useful data which might allow a direct attack on the database to hack it.

In these modern days of mobile apps I can only see this issue getting more significant.

[skywalk]

I guess for databases it's not a big issue because most people run the database only on the local host and do not let it communicate over the network. So all communication to and from the database never leaves the host machine.

Methinks Thorium was being sarcastic?

[Thorium]

I guess for databases it's not a big issue because most people run the database only on the local host and do not let it communicate over the network. So all communication to and from the database never leaves the host machine.

Methinks Thorium was being sarcastic?

Not really. It's even mentioned in the config of postgre that it is recommended to keep it bound to the local host only. A remote connection, even via SSL, is a security problem on it's own.

I understand the need for secure remote access and i know that there are uses. However most application i know of don't connect from the client to the database, but from the client to the server software and the server software will connect to the database. So the server software can handle access rights, etc.
I don't say all software does it this way but it's very common and probably the reason why SSL wasn't requested a lot and is still not supported by PB.

[skywalk]

Yes, I see your point. I never polled the database accesses I encounter. Fossil is a cgi based SQLite database with SSL access in the exe in front of the database. Which is another Client/Server case. But, within a trusted domain, you could run queries or administration utilities against non-local databases and still come across SSL or some other Username/Password hurdle.

[digital32]

In PB 6+ you now have the ability to add a "dynamic library" using UsePostgreSQLDatabase([LibraryName$]). The issue is I cannot find any more documentation on this. Help file says if it returns true it worked and false it failed.

I tried UsePostgreSQLDatabase("libpq.dll") with the proper path and it just fails with no other information.

Anyone know how this works? Is this a PB Library or can I use a DDL?

Main thing I need is SSL communication to the host that is running PostGreSQL. Don't want my ID/Password going over the network un-encrypted.

My understanding is that the PB 6 UsePostgreSQLDatabase is built on libpq but it does not have SSL/TLS compiled into it.

[Fred]

You should be able to use the official postgresql dll, which one did you tried ?

[digital32]

You should be able to use the official postgresql dll, which one did you tried ?

I got it from this site: https://www.dllme.com/dll/files/libpq
I also tried the 17.0.4 version that ships with PGAdmin.

[digital32]

Figured it out... In order to use the external library "libpq.dll" you need the other DLLs listed below. Also note that they all must be in the same directory as your EXE or in the windows PATH.

;Files Needed:
; libpq.dll
; libcrypto-3-x64.dll
; libiconv-2.dll
; libintl-8.dll
; libssl-3-x64.dll
Note: I pulled all of the files above from my local install of pgAdmin.

With this external library you can now connect via SSL and many other cool function.
Use this site to help configure the DB Connection Strings:
https://www.postgresql.org/docs/current ... CONNSTRING

Example Code:

Code: Select all

OpenConsole("PG SQL Test")

PrintN("Hello World")

PrintN("Loading: UsePostgreSQLDatabase")

If UsePostgreSQLDatabase("libpq.dll")
  
  PrintN("Opening Connection")
  If OpenDatabase(11, "hostaddr=10.x.x.x" + " port=5432 dbname=myDB connect_timeout=10 sslmode=prefer application_name='My App Name' ", "MyID", "MyPassword",#PB_Database_PostgreSQL)
    PrintN("Worked")
    FinishDatabaseQuery(11)
  Else
  	PrintN("DB Open Error: " + DatabaseError())
  EndIf

  ;This is here to keep the console open when running in debug mode. 
  Repeat
    KeyPressed$ = Inkey()
    If KeyPressed$ <> ""
   
      PrintN("You pressed: " + KeyPressed$)
      PrintN("It has a raw code of: "+Str(RawKey()))
        
    ElseIf RawKey()
      
      PrintN("You pressed a non ASCII key.")
      PrintN("It has a raw code of: "+Str(RawKey()))
        
    Else
      Delay(20) ; Don't eat all the CPU time, we're on a multitask OS
    EndIf

  Until KeyPressed$ = Chr(27) ; Wait until escape is pressed

  CloseDatabase(11)
  
EndIf