Re: create database, user

Thorsten,

> I want to create a database and a matching user for a web
> application.
> I want to do all that from an SQL file but I have problems with both
> 'create database' and 'create user'.
>
> - I have still not grasped how I should connect to the DBMS for
> things
> like that; there is no root. Is there a document describing best
> practices somewhere?

First, read: http://www.postgresql.org/idocs/index.php?user-manag.html

all of it.

Your "root" user for PostgreSQL should be the user "postgres", unless
you have used a non-standard install. In a secure system, the
postgres user should belong to the group "daemon", own the pgsql/
driectory tree, and have a secure password, both on the system and on
the database. The postgres user will also own the databases template1
and template0. In a regular install, the postgres user will have
inhereted whatever passowrd it had on the host system.

> - Are there any special provisions you have to follow to do create
> database, user, tables and other object with one SQL file?

There's two approaches you can take ... the easy but less secure, and
the labor-itensive but very secure.

The easy:
<in a shell script>
psql -U postgres -c "CREATE USER wwwuser WITH PASSWORD 'www-password'
CREATEDB;" template1
psql -U wwwuser -c "CREATE DATABASE wwwdatabase;" template1

This will give you a new database with wwwuser as its owner. wwwuser
will have full rights on all database objects. However, this is a not
terribly secure setup, and I wouldn't reccomend it for any public
website.

Instead, for a public website, you want to create the database with one
user as its owner ... say "wwwowner", and a second as the "user" which
the web scripts use (e.g. "wwwuser"). In this second case, you have
to go through the labor of defining *specific* permissions for each
and every object for "wwwuser":

GRANT SELECT, INSERT, UPDATE ON table1 TO wwwuser;

And don't forget your sequences, too:

GRANT SELECT, UPDATE ON table1_id_id_seq TO wwwuser;

It's a pain, but worth it the first time you get hacked. This way,
even if a cracker hijacks your web scripts, he can't do anything that
wwwuser doesn't have permissions for. One of the things I'm cautious
about is that wwwuser doesn't have DELETE permissions on anything, or
any permissions on the security log table.

Of course, wwwuser should not have a shell account, either.

You may wish to subscribe to the pgsql-php or pgsql-perl mailing lists.

-Josh Berkus