Apache user permissions on PostgreSQL database

Having applications connect as a superuser is almost definitely unwise. Unless the application needs to actually create and/or drop databases itself (and this is extremely unlikely), I don't think it's ever necessary. If the application connects to a database as that database's owner, it is effectively a superuser within the confines of that database, which might not be too bad.

I generally have applications access the database using an account authenticating with MD5. It's possible, for example, to set up pg_hba.conf such that the application account is the only account that can use MD5 authentication, and all other users on the local machine use ident/peer authentication.

It sounds like what you actually needed here was a role to group the Apache user and the other Django users together, so you could grant them access en masse.

Postgresql does have ways to grant permissions for all tables etc in a schema at once, and also a way to specify default permissions to be applied to new objects. This previous answer may be helpful: How do you create a read-only user in PostgreSQL?

IDENT authentication ended up being more hassle than it was worth. Here's what I ended up doing to avoid the use of a PostgreSQL superuser role...

Switch to the postgres linux user:

sudo su - postgres

Edit the PostgreSQL host-based authentication configuration file:

nano /etc/postgresql/8.4/main/pg_hba.conf

Scroll to near the bottom of this file, looking for the line which looks like this:

local   all   all   ident

Change ident to md5, exit and save. This tells PostgreSQL to use an MD5-encrypted password for authentication on local connections. Now restart PostgreSQL:

/etc/init.d/postgresql-8.4 restart

Create a PostgreSQL user:

createuser django_user --pwprompt

Don't accept any of the special privileges when prompted. Now create a new database:

createdb -E UTF8 -O django_user django_db

Those options encode the database in UTF8 and set the owner to django_user. You can now exit back to the original linux user account:

exit

Your project settings file (settings.py) will need to include something like this:

DATABASES = {    'default': {        'ENGINE': 'django.contrib.gis.db.backends.postgis',        'NAME': 'django_db',         'USER': 'django_user',         'PASSWORD': '[your password]',         'HOST': '',         'PORT': '',     }}

When you run python manage.py syncdb or any other Django management commands, the settings above will be used to authenticate with the database.