Create new user on Postgres.app

Here is how to create a user and grant remote login with the Mac OS X Postgres.app without adding a system account.

Postgres.app is a nice, packaged version of Postgresql that just runs out of the box on Mac OS X. Most tutorials for Postgres assume you are using system users to authenticate anything connecting to the database server, but on Mac OS X this may not make as much sense.

Here are the steps to allow remote connections and create a user:

  1. Download Postgress.app from their website.
  2. Drag the app to /Applications
  3. Run the app by double clicking it in Applications or via Spotlight Search. It will ask for permission to make some system changes, allow it to do so.
  4. Click run psql
  5. Create the database you want to grant access to: 
    CREATE DATABASE test_db;
  6. Create a normal user/role that doesn't use system authentication: 
    CREATE USER test_user WITH PASSWORD 'test_password_here_134325235235';
  7. Shut down Postgres.app by clicking it's elephant menu icon then clicking Quit
  8. Edit /Users/YOUR_USERNAME/Library/Application Support/Postgres/var-9.5/postgresql.conf:

    Find the comment that mentions listen_addresses and add the last line below:

    #listen_addresses = 'localhost'		# what IP address(es) to listen on;
					# comma-separated list of addresses;
					# defaults to 'localhost'; use '*' for all
					# (change requires restart)
listen_addresses = '*'

    This will allow connections from anywhere, but no one can actually connect until you perform the next step.

  9. Edit /Users/YOUR_USERNAME/Library/Application Support/Postgres/var-9.5/pg_hba.conf:

    Add this line to the end of the file, modifying the user and database:

    host 	test_db	test_user	0.0.0.0/0		md5

    This allows test_user to connect to the database test_db from any IP address, using the md5 authentication scheme. In general do not set other values for the last option as these are insecure (unless you take other steps).

  10. Restart Postgres.app by double clicking it in Applications (or via Spotlight, my favorite)
  11. On another machine (possibly a Docker image or other type of VM), run this to connect (replace HOST_IP_ADDRESS with an IP from ifconfig): 
    psql --host=HOST_IP_ADDRESS --username=test_user test_db

Comments (0)

Sudo Hints
http://www.sudohints.com/article.php/2016072411464198