pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: Thorsten Haude <postgresql@thorstenhau.de>
To: PostgreSQL Novice ML <pgsql-novice@postgresql.org>
Cc: pgsql-php@postgresql.org
Subject: Re: create database, user
Date: Sun, 10 Mar 2002 11:16:58 -0800
Message-ID: <web-823225@davinci.ethosmedia.com> (raw)
In-Reply-To: <20020310182306.GS808@eumel.yoo.net>

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



view thread (7+ messages)  latest in thread

Message-ID: <web-823225@davinci.ethosmedia.com>
Permalink:  ../web-823225@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-823225@davinci.ethosmedia.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: josh@agliodbs.com, postgresql@thorstenhau.de, pgsql-novice@postgresql.org
  Subject: Re: create database, user
  In-Reply-To: <web-823225@davinci.ethosmedia.com>
  References: <20020310182306.GS808@eumel.yoo.net> <web-823225@davinci.ethosmedia.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox