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
