Creating a postgres db + user + password etc on CentOS - outside of WF

Question

Hi all. I had the idea that I'd try to set up a Vagrant-box (virtualization thingy) that replicates the WF environment as closely as possible, to use as a staging server of sorts for my Django project (+ Gunicorn + Redis + Celery + Supervisor etc...). Basically the goal is to learn how all of these things fit together in an environment that is at least somewhat close to the final WF environment. Now, I'm out of my depth here, being mostly a frontend-guy who dabbles, but I've come this far and damned if I should give up now. But fair warning: fuzzy questions ahead.

I'm provisioning the box with the stuff I need via a bash-script that Vagrant runs as root for me upon creating the virtual CentOS 6.4 machine, installing Python2.7 and all the other dependencies I might need, creating folder structure that replicates the home folder structure I have on WF etc. So far so good, after much sweating/swearing.

Now, the last piece of the puzzle is getting PostgreSQL up and running - which I have done, installation-wise, but I'm having issues with grasping how to programmatically create and set password for the DB user I'd like to use for connecting from django.

I've got the thing down to having Postgres + Psycopg2 working as far as I can tell, so it's just the step where I create the actual db. It's only for testing/staging purposes, so security is not important.

Stuff that confuses me:

Needed/preferred relationship between Postgres users/roles, db owner.
Need for editing the pg_hba.conf file to use md5 instead of ident (wut?).
How to create a db, a user with enough privileges and set a password, all programmatically from a bash script without needing interactive input in the shell. Maybe use some sort of environment variables...? Halp.

So, as you can see, I'm out of my depth, but soo close to having this thing work, and then I can be done with it. Frantic googling gives me a host of articles that give hints, but they all skip various parts of the process (and almost always rely on being done in an interactive shell), which makes it insanely hard to piece it together. So a gentle nudge or snippet to push me in the right direction would be much appreciated. Thanks.

Accepted Answer

Our setup doesn't do anything fancy with PostgreSQL users and roles. In general, you have a single DB user who is the owner of a single DB (although you can have additional users if you need to).

Each user is set up in pg_hba.conf like this:

local       your_db   your_db_user                         password
host        your_db   your_db_user        0.0.0.0/0        password

If you want to script the creation of the user and database in bash without prompting for user input, then you can do it like this:

First, create a .pgpass file in the root of the home directory of the user that will be running the bash script. The permission on .pgpass should be set to 600, and should contain the local credentials for your PostgreSQL superuser, like this:

localhost:*:*:superuser_name:superuser_password

Then, as that user, you can execute the following commands from bash to create the user and database:

psql -U superuser_name -c "create user \"your_db_user\" with password 'your_db_password'"
psql -U superuser_name -c "create database \"your_db\" with owner \"your_db_user\" encoding='utf8' template template0"

Finally, be sure to reload or restart your PostgreSQL service to pick up the changes to pg_hba.conf.

Hope that helps!

Creating a postgres db + user + password etc on CentOS - outside of WF

Follow this question

Related questions