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
