2 Replies Latest reply: Jun 11, 2013 7:03 AM by deivy
Todd Heberlein1 Level 1 Level 1 (30 points)

I am having troubles setting PostgreSQL to accept more secure network connections.


On my Mountain Lion system with Server.app I am trying to start my own copy of PostgreSQL running. Running the command


$ sudo serveradmin start postgres


gets things up and running, I can connect locally with psql.


But...  if I go into the file


/Library/Server/PostgreSQL/Data/pg_hba.conf


and change the line


host    all             all             127.0.0.1/32            trust


to


host    all             all             127.0.0.1/32            md5


or anything other than "trust", I get the error message


$ sudo serveradmin start postgres

postgres:error = "CANNOT_START_SERVICE_TIMEOUT_ERR"


I initially got this error message when trying to change the file org.postgresql.postgres.plist and pg_hba.conf to accept network connection from other hosts. I could get this, but only if the METHOD is set to "trust", which doesn't seem like a good idea.


Any idea how to configure org.postgresql.postgres.plist and pg_hba.conf on OS X 10.8 with Server.app such that I can have a more secure connections?


Thanks,


Mac Pro, OS X Mountain Lion (10.8.2)
  • 1. Re: postgresql connection other than "trust"?
    Todd Heberlein1 Level 1 Level 1 (30 points)

    Update for my own posts. The warnings are misleading.

     

    Despite (1) the warning generated when I start postgres

    $ sudo serveradmin start postgres

    postgres:error = "CANNOT_START_SERVICE_TIMEOUT_ERR"

     

    and despite (2) serveradmin fullstatus stating that postgres is not responding

    $ sudo serveradmin fullstatus postgres

    postgres:dataDirHasBeenInitialized = yes

    postgres:PG_VERSION = "9.2.1"

    postgres:dataDir = "/Library/Server/PostgreSQL/Data"

    postgres:postgresIsResponding = no

    postgres:dataDirIsDirectory = yes

    postgres:PGserverVersion = 0

    postgres:dataDirExists = yes

    postgres:setStateVersion = 1

     

    Postgres is up and running and I can login remotely (with a user with a password set) and locally as _postgres (with no password).

     

    For example, my entry in /Library/Server/PostgreSQL/Data/pg_hba.conf has the following lines:

    local   all             all                                     trust

    ...

    host    all             all             192.168.10.0/24         md5

     

    I can now login locally as "_postgres" without a password. I just need to explicity specify "-h localhost"

    $ psql -h localhost -U _postgres auditex

    psql (9.1.4, server 9.2.1)

    WARNING: psql version 9.1, server version 9.2.

             Some psql features might not work.

    Type "help" for help.

     

     

    auditex=#

     

    and remotely via my user "bob" with a password

    $ psql -h r2d2.lab.netsq.com -U bob auditex

    Password for user bob:

    psql (9.1.4, server 9.2.1)

    WARNING: psql version 9.1, server version 9.2.

             Some psql features might not work.

    Type "help" for help.

     

     

    auditex=>

  • 2. Re: postgresql connection other than "trust"?
    deivy Level 4 Level 4 (2,365 points)

    Todd, when you get messages:

     

    `postgres:error = "CANNOT_START_SERVICE_TIMEOUT_ERR" '

     

    and

     

    ` postgres:postgresIsResponding = no '

     

    are your wiki and webpages working?