Apache user permissions on PostgreSQL database

Question

I'm planning to deploy a Django site using Apache + mod_wsgi and PostgreSQL on Ubuntu 10.04.

I intend to connect to the database using IDENT authentication. For this I need to create a Postgresql user for Apache (www-data). I have chosen not to make this a superuser or provide any special privileges.

I have then created a database. I actually did this twice during testing. The first time I set the Apache user as the owner; the second time I set the owner as myself (superuser), and granted all privileges on the database to the Apache user.

When I use the Django syncdb management command (as myself), the tables created are not accessible to the Apache user. This can be resolved by granting all permissions to the Apache user for each table, but that's a bit of a nuisance.

The alternative seems to be allowing access as a superuser.

Is it considered safe/acceptable for my project to access a local db as a Postgresql superuser, and is it safe to use IDENT authentication? If not, what is the common practice?

EDIT: I've since found that switching PostgreSQL to use md5 authentication for local connections makes life easier.

When using ident authentication, connections to the database are via the Apache user during normal operation. When Django management commands are used, the connections are via the current user.

If you use MD5, both situations will connect to the database using the details specified in the DATABASES section of your settings.py file, avoiding the problems listed above.

I'm still interested to know if using a PostgreSQL superuser is wise.

score 4 · Accepted Answer · edited May 23 '17 at 10:31

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?

MattRowbum · Answer 2 · 2012-01-11T15:54:54.557

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.

Apache user permissions on PostgreSQL database

2 Answers2