Skip site navigation (1) Skip section navigation (2)

Re: create database, user

From: "Josh Berkus" <josh(at)agliodbs(dot)com>
To: Thorsten Haude <postgresql(at)thorstenhau(dot)de>,PostgreSQL Novice ML <pgsql-novice(at)postgresql(dot)org>
Cc: pgsql-php(at)postgresql(dot)org
Subject: Re: create database, user
Date: 2002-03-10 19:16:58
Message-ID: web-823225@davinci.ethosmedia.com (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-php
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

In response to

Responses

pgsql-novice by date

Next:From: Josh BerkusDate: 2002-03-10 19:58:32
Subject: Re: create database, user
Previous:From: Oliver ElphickDate: 2002-03-10 18:58:28
Subject: Re: create database, user

pgsql-php by date

Next:From: Josh BerkusDate: 2002-03-10 19:58:32
Subject: Re: create database, user
Previous:From: Oliver ElphickDate: 2002-03-10 18:58:28
Subject: Re: create database, user

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group