Simplifying Postgres permissions

If you are anything like me, you would prefer to deal with as few headaches when developing software as absolutely possible. One recent headache that decides to revisit me occasionally is Postgres permissions. For users/platforms where you need a single user (that isn't the "postgres" user), while offering password authentication with both local and remote access, keep reading to find out how you can ensure that your permissions and table ownership are sane-ish. This is my gathering together of other information from docs, blogs, and tech articles.

First, you need to install Postgres. I'll assume you've already done that.

Next, you need to create the user that will be performing all of your operations (schema updates, data manipulation, etc.).

$ sudo -u postgres psql
# create user DBUSER with password 'PASSWORD';
# create database DATABASE;
# grant all privileges on database DATABASE to DBUSER;
# \q
$ 

After creating and updating your initial database, you now need to update your Postgres configuration to ensure that you can actually log in with your user.

$ sudo vim /etc/postgresql/9.1/main/pg_hba.conf

Scroll to the bottom of your configuration file. About 10 lines up (at least in Postgres 9.1), you should see a line that reads:

local   all             all                               peer

Change the last column to read "md5":

local   all             all                               md5

Then save and quit the editor.

After your configuration is updated, you need to restart Postgres:

$ sudo /etc/init.d/postgresql restart

From here, if you ever want to modify your schema, use data from a client, etc., you only need to log in with the user you created earlier, and everything will just work. You will be able to alter your schema as necessary, select, update, insert, and delete. In a lot of ways, this behaves a lot like a just-installed MySQL configuration after you've set a username/password for the root user (and only ever use the root user), but where the user *only* has access to manipulate the one database. This can be very useful when you've got your one Postgres install for personal projects, but want to silo your different projects into different named databases.

If you've already got some tables created with the Postgres user, and you need to change ownership, the accepted answer over on StackOverflow can get you all fixed up.

Long-term, you may want to create additional read/write users (no DDL updates), read-only users, etc., but if you just want to get a new application going, this can get you there.

Please upvote on Reddit and Hacker News.