Yansolo

Q: 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

Close

Q: How to connect to embedded POSTGRESQL on Mac OSX Lion Server

  • All replies
  • Helpful answers

Page 1 of 3 last Next
  • by haerulrijal,

    haerulrijal haerulrijal Jul 22, 2011 11:02 AM in response to Yansolo
    Level 1 (0 points)
    Jul 22, 2011 11:02 AM in response to Yansolo

    I have the same problem ...

  • by nikoneko,Helpful

    nikoneko nikoneko Jul 22, 2011 8:20 PM in response to Yansolo
    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)

  • by nikoneko,Solvedanswer

    nikoneko nikoneko Jul 22, 2011 10:11 PM in response to 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

  • by capaho,

    capaho capaho Jul 22, 2011 10:21 PM in response to Yansolo
    Level 4 (3,655 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.

  • by nikoneko,

    nikoneko nikoneko Jul 22, 2011 10:24 PM in response to capaho
    Level 1 (15 points)
    Jul 22, 2011 10:24 PM in response to capaho

    I have no firewall enable

  • by capaho,

    capaho capaho Jul 22, 2011 10:38 PM in response to nikoneko
    Level 4 (3,655 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.

  • by nikoneko,

    nikoneko nikoneko Jul 22, 2011 10:41 PM in response to capaho
    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

  • by Yansolo,

    Yansolo Yansolo Jul 23, 2011 12:56 AM in response to nikoneko
    Level 1 (0 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.

  • by daefromlos angeles,Helpful

    daefromlos angeles daefromlos angeles Jul 23, 2011 1:05 AM in response to Yansolo
    Level 1 (5 points)
    Jul 23, 2011 1:05 AM in response to Yansolo

    View => Show system records.

    on Workgroup manager.

    Remember to select Local directory

    Untitled.png

  • by capaho,

    capaho capaho Jul 23, 2011 1:05 AM in response to Yansolo
    Level 4 (3,655 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.

  • by Yansolo,

    Yansolo Yansolo Jul 23, 2011 1:20 AM in response to daefromlos angeles
    Level 1 (0 points)
    Jul 23, 2011 1:20 AM in response to daefromlos angeles

    Thanks daefromlos angeles

     

    I got the Postgres group now.

  • by Yansolo,

    Yansolo Yansolo Jul 23, 2011 1:25 AM in response to nikoneko
    Level 1 (0 points)
    Jul 23, 2011 1:25 AM in response to nikoneko

    Ok after adding my self to the Postgres Users group, I'am able to connect to the PostgreSQL database using user _postgres.

     

    Thanks

  • by haerulrijal,

    haerulrijal haerulrijal Jul 30, 2011 7:15 PM in response to Yansolo
    Level 1 (0 points)
    Jul 30, 2011 7:15 PM in response to Yansolo

    I just wrote about how to replace PostgreSQL on Mac OS X Lion using embedded configuration.

  • by Tim Bloom1,

    Tim Bloom1 Tim Bloom1 Aug 1, 2011 8:27 PM in response to Yansolo
    Level 1 (110 points)
    Aug 1, 2011 8:27 PM in response to Yansolo

    I'm unable to login via pgAdmin at all.  I put in the socket, and attempt to connect, but get nothing.  I've added myself to the postgres users group.

     

    I'm not familiar with what "2. Create a role on database login in as _posgres" involves.

     

    If I'm unable to login to manage it, how would I go about creating this?

Page 1 of 3 last Next