Embedded posgresql in Developer. Remote access? RESOLVED

Questions and answers regarding general SQL and backend databases

Embedded posgresql in Developer. Remote access? RESOLVED

Postby joe26 » Sat Apr 28, 2018 8:55 pm

HI,

Again.

I'm installing 8.2.3 on another machine and want to use Navicat to transfer my data to the new machine from 7.4.8.

I've changed pg_hba.conf to 'host all all 0.0.0.0/0 trust',
postgresql.conf to 'listen = '*',
disabled firewalls.

I can ping the machine.
The username and password are identical.
Reinstalled 8.2.3.
7.4.8 is still fully functional.
psping shows no active listening on my chosen port: 5823.
I _can_ connect from 7.4.8 to the server where the database is a separate installation.
repository_server is enabled, but still seeking created database. Tried 'repository_server' as well. No joy.
Short of the firewall, there doesn't seem to be access restrictions. Even permitted the port under the firewall. No joy there, either.

I could try to backup my files, but defeats remote access to the database for admin.

It doesn't appear that the embedded database is actually using those config files.

Is there a limitation to the embedded database where I cannot remotely access the database for updates or backups?

Thanks,
--Joe.
Last edited by joe26 on Tue Jun 19, 2018 11:33 pm, edited 1 time in total.
joe26
 
Posts: 172
Joined: Wed Jun 19, 2013 10:30 pm

Re: Embedded posgresql in Deverloper. Remote access?

Postby ROCLASI » Sun Apr 29, 2018 1:59 pm

Hi Joe,

There is a lot to unpack here...

First, is this a remote development machine or are you using this as a server?
If the latter you should install the official PostgreSQL distribution. The bundled one is only there for your convenience when developing, it is not supported for deployments.
The official distro will set things up in a secure fashion and makes sure it all runs under the proper user/permissions.

I've changed pg_hba.conf to 'host all all 0.0.0.0/0 trust',


Never EVER use the trust option, forget it is even there.
Using a non-standard port doesn't protect you here from intrusion, they are one portscan away from having access.
Using this option one just have to connect to your database server and is in. No password is requested. It might be convenient but it's VERY bad practice.
The only use-case you might want to use it is if you lost your password then make it only work on localhost, connect and change the password and set the setting back to a secure method of authentication.

disabled firewalls.

If this is a server connected directly to the internet then this is also a bad idea. Enable it again and add a firewall rule for your IP to have access to that port.

psping shows no active listening on my chosen port: 5823.

If that was the port you gave PostgreSQL then there is your problem for not being able to connect.

Check the PostgreSQL log for any errors.

Also did you restarted PostgreSQL after you changed these settings?

Hope this helps.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Embedded posgresql in Deverloper. Remote access?

Postby joe26 » Sun Apr 29, 2018 8:04 pm

Hi Robert,

It turns out that the config wasn't being read. I enabled pg_hba.config in the wrong directory.
The server has it's separate install directory since I downloaded postgresql from their website.

I saw the sample config and edited a new config in place.

It wasn't listening or permitting access because the correct file wasn't being edited.

SHOW hba_file; from within SQL Explorer exposed my insanity, since I was certain the correct file was being edited.

yeah...

Thanks!
--Joe.
joe26
 
Posts: 172
Joined: Wed Jun 19, 2013 10:30 pm


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 6 guests