Re: create database, user - Mailing list pgsql-novice

On Sun, 2002-03-10 at 18:23, Thorsten Haude wrote:
> Hi,
>
> 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?

It'll be in the docs somewhere; have you read the Administrator's Guide?


There is a kind of "root"; it is the "template1" database, which is
created when initdb is run.  Similarly there is a "root user", the
PostgreSQL administrator, who is the user who ran initdb, often
"postgres".  This user is created with privilege to create both users
and databases.


To connect in order to create a database, become the adminstrator and
either run the script createuser or connect to the "template1" database
and use CREATE DATABASE from there.  Similarly, use the script
createuser or connect to any database and use CREATE USER.

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

Put all the commands into a text file, not forgetting the terminating
semi-colons where they are needed.

For example:

$ cat >/tmp/sql.script <<EOI
---------------------- an SQL script file ---------------------
CREATE DATABASE junk;

CREATE USER junk_user;

\connect junk junk_user

CREATE TABLE a (fld1 SERIAL PRIMARY KEY, fld2 TEXT);
COMMENT ON TABLE a IS 'A dummy table';
COMMENT ON COLUMN a.fld1 IS 'Primary key';
COMMENT ON COLUMN a.fld2 IS 'Random text';

INSERT INTO a (fld2) VALUES ('some rubbish or other');

SELECT * FROM a;

---------------------------------------------------------------
EOI
$ su
[Password]
# su - postgres
$ psql -d template1 < /tmp/sql.script


Whether the above script succeeds in changing user depends on how
pg_hba.conf is set up.  If pg_hba.conf doesn't allow the connection, the
script will fail at the \connect.  You need the \connect, otherwise the
table would be created in template1 rather than in the new database.


--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "What good is it, my brothers, if a man claims to have
      faith, but has no deeds? Can such faith save him?
      Suppose a brother or sister is without clothes and
      daily food.   If one of you says to him, "Go, I wish
      you well; keep warm and well fed," but does nothing
      about his physical needs, what good is it? In the same
      way, faith by itself, if it is not accompanied by
      actions, is dead."          James 2:14-17