How to connect to embedded POSTGRESQL on Mac OSX Lion Server

Question

Level 1

4 points

How to connect to embedded POSTGRESQL on Mac OSX Lion Server

Hi all,

I've upgraded my Mac OSX 10.6 server to 10.7 server 2 days ago.

My web sites databases are still under MySQL server cause it's still running after the upgrade.

I manage to migrate the databases to PostgreSQL, so i've enabled the wiki from the server.app to launch the PostgreSQL server.

I can see that it's now running through a ps -ef | grep sql and that it's listening on the unix domain socket 5432 with netstat -an | grep SQL in a terminal shell.

The problem is that I'am unable to connect to it.

I tried psql -U postgres, and installed the Navcat for postgreSQL Lite, but they failed to connec to the database server twith the same error :

psql: could not connect to server : Permission Denied

Is the server running locally and accepting connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

It seems that there is a permission problem on the Unix Domain Socket /var/pgsql_socket/.s.PGSQL.5432.

It has a 0770 permission on it, but i don't know if it's relly the problem.

I've compared with MySQL server, which I am able to manage through Navcat for MySQL Lite without any problem, and the socket permission are 0777 on /var/mysql/mysql.sock.

Does somebody able to connect to the embedded PostgreSQL server ?

Or am I doing something wrong ?

Thanks.

Posted on Jul 22, 2011 5:59 AM

Reply

Answer 1

Nov 17, 2017 7:50 AM in response to Yansolo

Hi there,

maybe someone with a similar question but about El Capitan or Sierra may have a look at my post:

https://discussions.apple.com/thread/8164982

Regards,

Olaf

Reply

Answer 2

Jul 22, 2011 11:02 AM in response to Yansolo

I have the same problem ...

Reply

Answer 3

nikoneko

Level 1

15 points

Jul 22, 2011 8:20 PM in response to Yansolo

I was able to connect but not from navcat

sudo -u _postgres psql template1 (or your database name)

Reply

Answer 4

nikoneko

Level 1

15 points

Jul 22, 2011 10:11 PM in response to nikoneko

I find out how is it

1. Need to add yourself or the account accessing the database to Postgres Users group (use admin tools)

2. Create a role on database login in as _posgres

3. To login in navcat you need to check socket to /var/pgsql_socket

4. I was unable to enable TCP connection yet

Reply

Answer 5

capaho

Level 4

3,673 points

Jul 22, 2011 10:21 PM in response to Yansolo

Check the firewall settings and make sure that port 5432 or whatever port Postgresql is using for incoming connections is open for the networks you want to access it from.

Reply

Answer 6

nikoneko

Level 1

15 points

Jul 22, 2011 10:24 PM in response to capaho

I have no firewall enable

Reply

Answer 7

capaho

Level 4

3,673 points

Jul 22, 2011 10:38 PM in response to nikoneko

If you have a server running on the Internet with the firewall disabled you can count the hours before it gets cracked.

Reply

Answer 8

nikoneko

Level 1

15 points

Jul 22, 2011 10:41 PM in response to capaho

I do have a mac mini server with no important information

with a firewall on my airport extreme

Thanks for the tip

Reply

Answer 9

Yansolo Author

Level 1

4 points

Jul 23, 2011 12:56 AM in response to nikoneko

Thanks for your replies.

I was able to connect but not from navcat

sudo -u _postgres psql template1 (or your database name)

I am able to connect using this command too

1. Need to add yourself or the account accessing the database to Postgres Users group (use admin tools)

So I agree that I have to add my user to the _postgres user group, but how do you did ?

Using the admin tools, I did not find this Postgres Users group ?

I don't understand why server's administrators are not members of this group already ...

2. Create a role on database login in as _posgres

Ok, I will do.

3. To login in navcat you need to check socket to /var/pgsql_socket

OK, I've already set the socket in Navcat to /var/pgsql_socket

4. I was unable to enable TCP connection yet

I am unable to enable it too. I tried to modify the config files in /var/pgsql, without success.

I have no firewall on my server too, it's protected by my router's firewall and NAT.

Thanks again for your help.

Reply

Answer 10

Jul 23, 2011 1:05 AM in response to Yansolo

View => Show system records.

on Workgroup manager.

Remember to select Local directory

Reply

Answer 11

capaho

Level 4

3,673 points

Jul 23, 2011 1:05 AM in response to Yansolo

If you are accessing your server through your LAN it will be filtered by your router's firewall regardless of the server firewall settings, as those packets are routed by your router. If you can't connect using the terminal on the server itself then you probably still have a configuration error somewhere.

As for NAT, the standard configuration for many non-industrial strength routers is to pass all NAT packets through to the destination device without filtering. Unless you have a router that allows you to configure its firewall to filter NAT packets, you have absolutely no protection if the server firewall is disabled.

Reply