Homebrew installed PostgreSQL for use by multiple accounts

homebrewnetwork-userpermissionpostgresql

I'm using Homebrew to install PostgreSQL (PG) on a MacBook Air.

admin user brew installed PostgreSQL.

I'd like another (mobile account) user to be able to use the database for Rails development. admin is owner of /usr/local/ but mobile user is member of a group that has rwx permission. brew doctor is happy and mobile user can successfully brew install

How best to set up permissions for this to work?

for instance, running initdb /usr/local/var/postgres -E utf8 from the admin account created a database for the admin user. And, now it would seem, it wants PG to be launched by that admin user.

I'd really like either a neutral account to launch PG process or ideally the mobile account to be able to launch and quit an instance of PG as needed.

Point being PG doesn't need to be running ALL the time (LaunchDaemon). Ideally, it's a LaunchAgent in mobile user's account.

Thoughts?

Best Answer

According to the postgres output, you need to run the postgres server as the same user that creates the database with initdb. 

mr-clean:~ smurf$ initdb /usr/local/var/postgres -E utf8
The files belonging to this database system will be owned by user "smurf".
This user must also own the server process.

That means the same user will need to launch the postgres server process. Other users will still be able to launch the client and connect to it, though. So you just need to do the "admin" tasks - setup and daemon control - with that owner account.

If you want to do everything with the one mobile account, then just run initdb as mobile instead of as admin after doing the brew install postgresql as normal. (It's not who you brew install as that matters, it's who you initdb as.) This'll create the database, and rwx permissions on /usr/local/var (not /usr/local) will be enough to allow this. Then do all your PG work as mobile. The LaunchAgent should work with this approach, though I haven't tested it.

To allow multiple users to effectively manage the postgres server (launch and stop the daemon), set it up basically the same way, with a dedicated postgres (or mobile) account that both does the initial initdb and brings the server up or down. Then create a little script to ssh in to localhost as postgres and run the command to bring the server up or down with the same command you'd use in the single-user case. And set up ssh keys so that all the accounts you want to manage PG can ssh in as postgres (that is, cat their public keys to postgres' ~/.ssh/authorized_keys file). Now you can effectively have multiple users manage Postgres without deviating from the dedicated-daemon-user model that Postgres expects and fiddling with file permissions and so on.

This ssh technique works for any service that needs to be controlled by a dedicated account, and will generalize to the case where you're running your things on different machines.

Related Question