Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

How to connect to embedded PostgresDB on Mac OSX Mountain Lion Server?

This used to work before upgrade to Mountain Lion & OSX Server 10.8! All settings are still the same: User and password does still work, but only with connection "sudo psql -U _www -d tt-rss" in Terminal, not via browser: "Unable to connect to database (as _www to , database tt-rss):".


sudo lsof -i -P | grep -i ':5432'

Password:

postgres_ 53893 _postgres 3u IPv4 0x837f3595a51f6299 0t0 TCP localhost:5432 (LISTEN)


I tried to change to <string>listen_addresses=127.0.0.1</string> in /Library/Server/PostgreSQL/Config/org.postgresql.postgres.plist – without success!


Any ideas what to do besides waiting for OSX Server 10.8.1?

Mac mini, OS X Server, ML Server on a Mac mini Server

Posted on Jul 27, 2012 2:31 PM

Reply
Question marked as Best reply

Posted on Jul 27, 2012 3:24 PM

Hi,


First, a warning: if you change the way the included postgres service accepts connections, you will likely break other services that depend on postgres (profile manager, calendar, wiki, etc). Don't do this 🙂 If you want a DB for your own purposes, you might be better off setting up some separate DB instance. If you are intent on using the included postgres, you should make sure you're working in your own separate DB with some role account you created, and connect via unix domain socket so you don't need to change the postgres service config.


By default, the included postgres cluster should accept connections only on a unix domain socket, not a TCP socket. The following should produce a list of databases:


sudo psql -U _postgres --list


(note: the role account to use is _postgres not _www)


On a fresh server, you'd see:


List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges

-------------------+------------+----------+---------+-------+------------------ -------

caldav | caldav | UTF8 | C | C |

collab | collab | UTF8 | C | C |

device_management | _devicemgr | UTF8 | C | C |

postgres | _postgres | UTF8 | C | C |

template0 | _postgres | UTF8 | C | C | =c/_postgres +

| | | | | _postgres=CTc/_postgres

template1 | _postgres | UTF8 | C | C | =c/_postgres +

| | | | | _postgres=CTc/_postgres

webauth | webauth | UTF8 | C | C |

(7 rows)



To see a list of database users (aka 'role accounts'):


sudo psql -U _postgres template1

SELECT rolname FROM pg_roles;


On a fresh server, you'd see:


template1=# SELECT rolname FROM pg_roles;

rolname

------------

_postgres

caldav

_devicemgr

collab

webauth

(5 rows)


Note that _postgres is also the name of a system account, but sudo bypasses the need to su to _postgres first in order to connect. This comes down to the file permissions on the unix domain socket used by postgres; file permissions are what gate access to unix domain sockets, which can only be accessed from the local host.


woot:~ admin$ ls -alh /var/pgsql_socket

total 8

drwxr-xr-x 4 _postgres daemon 136B Jul 27 14:59 .

drwxr-xr-x 28 root wheel 952B Jul 19 13:42 ..

srwxrwx--- 1 _postgres _postgres 0B Jul 27 14:59 .s.PGSQL.5432

-rw------- 1 _postgres daemon 72B Jul 27 14:59 .s.PGSQL.5432.lock


psql executed as _postgres should work without having to specify a server address or unix domain socket path; this is because by default it uses the correct socket path (/var/pgsql_socket). If you are using some other tool besides the included psql, you may need to pass the socket path when connecting, e.g.:


sudo psql -h /var/pgsql_socket -U _postgres ...


If you are connecting from some other postgres API, make sure you are connecting to a socket path and not localhost or 127.0.0.1, and remember that you would need to be _postgres to successfully connect. Otherwise:


woot:~ admin$ id

uid=501(admin) gid=20(staff) groups=20(staff),405(com.apple.sharepoint.group.3),403(com.apple.sharepoint.gro up.1),12(everyone),33(_appstore),61(localaccounts),79(_appserverusr),80(admin),8 1(_appserveradm),98(_lpadmin),100(_lpoperator),204(_developer),401(com.apple.acc ess_screensharing),402(com.apple.access_ssh),404(com.apple.sharepoint.group.2)

woot:~ admin$ psql --list

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"?


Hope this helps,

-dre


Updated: added more details

8 replies
Question marked as Best reply

Jul 27, 2012 3:24 PM in response to macxtra

Hi,


First, a warning: if you change the way the included postgres service accepts connections, you will likely break other services that depend on postgres (profile manager, calendar, wiki, etc). Don't do this 🙂 If you want a DB for your own purposes, you might be better off setting up some separate DB instance. If you are intent on using the included postgres, you should make sure you're working in your own separate DB with some role account you created, and connect via unix domain socket so you don't need to change the postgres service config.


By default, the included postgres cluster should accept connections only on a unix domain socket, not a TCP socket. The following should produce a list of databases:


sudo psql -U _postgres --list


(note: the role account to use is _postgres not _www)


On a fresh server, you'd see:


List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges

-------------------+------------+----------+---------+-------+------------------ -------

caldav | caldav | UTF8 | C | C |

collab | collab | UTF8 | C | C |

device_management | _devicemgr | UTF8 | C | C |

postgres | _postgres | UTF8 | C | C |

template0 | _postgres | UTF8 | C | C | =c/_postgres +

| | | | | _postgres=CTc/_postgres

template1 | _postgres | UTF8 | C | C | =c/_postgres +

| | | | | _postgres=CTc/_postgres

webauth | webauth | UTF8 | C | C |

(7 rows)



To see a list of database users (aka 'role accounts'):


sudo psql -U _postgres template1

SELECT rolname FROM pg_roles;


On a fresh server, you'd see:


template1=# SELECT rolname FROM pg_roles;

rolname

------------

_postgres

caldav

_devicemgr

collab

webauth

(5 rows)


Note that _postgres is also the name of a system account, but sudo bypasses the need to su to _postgres first in order to connect. This comes down to the file permissions on the unix domain socket used by postgres; file permissions are what gate access to unix domain sockets, which can only be accessed from the local host.


woot:~ admin$ ls -alh /var/pgsql_socket

total 8

drwxr-xr-x 4 _postgres daemon 136B Jul 27 14:59 .

drwxr-xr-x 28 root wheel 952B Jul 19 13:42 ..

srwxrwx--- 1 _postgres _postgres 0B Jul 27 14:59 .s.PGSQL.5432

-rw------- 1 _postgres daemon 72B Jul 27 14:59 .s.PGSQL.5432.lock


psql executed as _postgres should work without having to specify a server address or unix domain socket path; this is because by default it uses the correct socket path (/var/pgsql_socket). If you are using some other tool besides the included psql, you may need to pass the socket path when connecting, e.g.:


sudo psql -h /var/pgsql_socket -U _postgres ...


If you are connecting from some other postgres API, make sure you are connecting to a socket path and not localhost or 127.0.0.1, and remember that you would need to be _postgres to successfully connect. Otherwise:


woot:~ admin$ id

uid=501(admin) gid=20(staff) groups=20(staff),405(com.apple.sharepoint.group.3),403(com.apple.sharepoint.gro up.1),12(everyone),33(_appstore),61(localaccounts),79(_appserverusr),80(admin),8 1(_appserveradm),98(_lpadmin),100(_lpoperator),204(_developer),401(com.apple.acc ess_screensharing),402(com.apple.access_ssh),404(com.apple.sharepoint.group.2)

woot:~ admin$ psql --list

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"?


Hope this helps,

-dre


Updated: added more details

Jul 28, 2012 3:36 AM in response to dreness

Thanks dreness! Following your explanation I tried this below, what gives me the impression, that user _www is allowed to use the the right way of connection = /var/pgsql_socket.


admin@mac5:~$ sudo psql -h /var/pgsql_socket -U _www -l

List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges

-------------------+------------+----------+---------+-------+------------------ -------

caldav | caldav | UTF8 | C | C |

collab | collab | UTF8 | C | C |

device_management | _devicemgr | UTF8 | C | C |

postgres | _postgres | UTF8 | C | C |

roundcubemail | roundcube | UTF8 | C | C |

template0 | _postgres | UTF8 | C | C | =c/_postgres +

| | | | | _postgres=CTc/_postgres

template1 | _postgres | UTF8 | C | C | _postgres=CTc/_postgres+

| | | | | =c/_postgres

tt-rss | _www | UTF8 | C | C |

webauth | webauth | UTF8 | C | C |

(9 rows)



So it seems that apple restricted the access to the embedded Database Server with Mountain Lion. I accept this fact and I am willing to turn it back to the former state knowing this is in a way "insecure"! I just need to know how to do it and I do hope to get the needed configuration settings … or a statement that says: The embedded Database Server is protected against any TCP socket access by hardcoded settings! Again: thanks for any further help!

Aug 5, 2012 9:41 AM in response to macxtra

These are the instructions I used to access the PostgreSQL server embedded in Lion Server the first time. I had to redo numbers 1 and 2 after upgrading to Mountain Lion and now it works again. No ill effects on Server app. (Original post here).


1. edit file /system/library/LaunchDaemons/org.postgresql.postgres.plist


deteteв lines

<string>-c</string>

<string>listen_addresses=</string>

2. edit file /var/pgsql/pg_hba.conf


add lines

host all all 192.168.0.0/24 trust

3. edit file /var/pgsql/postgresql.conf

edit lines

#listen_addresses = 'localhost'

listen_addresses = '*'

#port = 5432

port = 5432

4. restart mac os x server

Aug 20, 2012 5:07 PM in response to Gerald Tangren

Mountain Lion Server uses a version of postgres that's in the actual Server.app. The appropropriate pg_hba.conf is in /Library/Server/PostgreSQL/Data/. Modifying the launchdaemon and adding the appropriate settings in the hba file will get things running for you. If you check the OS X Server email list archives, I posted a message about the Server postgres install a few days ago (in particular, installing the pgcrypt library). That post provides a good overview of how things have changed in Mountain Lion Server.

Aug 21, 2012 12:34 PM in response to Gerald Tangren

Again, this is instructions specific to getting pgcrypto working, but there are some nuggests of information related to paths and what-not that may be helpful...


========


Hello folks,


I just banged my head against the wall for a period of time trying to get pgcrypt installed on Mountain Lion Server. Here's the scoop, to save anyone else the headaches...


- It looks like modules, libraries, etc. for many of server's services are getting moved into Server.app itself (/Applications/Server.app/Contents/ServerRoot/)


- Postgresql stuff lives at /Applications/Server.app/Contents/ServerRoot/usr/share/postgresql/


- Download the source to Postgresql 9.1.4 (http://www.postgresql.org/ftp/source/v9.1.4/)


- Untar the source with "tar xvfz postgresql-9.1.4.tar.gz"


- Change directory to the source directory "cd postgresql-9.1.4"


- Run "./Configure"


- Run "make"


- Change directory to the pgcrypt source directory "cd contrib/pgcrypto/"


- Run "make"


- Run "make install" (*You could change the install locations for "make install", but I prefer this route in case I want to use pgcrypt with another Postgresql install outside of server)


- Copy the .so file to Server.app "cp /usr/local/pgsql/lib/pgcrypto.so /Applications/Server.app/Contents/ServerRoot/usr/lib/"


- Copy the .control file to Server.app "cp /usr/local/pgsql/share/extension/pgcrypto.control /Applications/Server.app/Contents/ServerRoot/usr/share/postgresql/extension/"


- Copy the .sql file to Server.app "cp /usr/local/pgsql/share/extension/pgcrypto--1.0.sql /Applications/Server.app/Contents/ServerRoot/usr/share/postgresql/extension/"


- For some reason, I can't get the new Postgresql 9.x "create extension" functionality to work from psql. I was successfully able to issue it from the Query Tool of PGAdmin with "create extension pgcrypto"


- You can test the functionality by using the pgcrypt() and gen_salt() functions to set a column value "UPDATE Vendor set password = crypt('test', gen_salt('md5')) where id = 1;"

How to connect to embedded PostgresDB on Mac OSX Mountain Lion Server?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.