From: Oliver Elphick <olly@lfix.co.uk>
To: Thorsten Haude <postgresql@thorstenhau.de>
Cc: PostgreSQL Novice ML <pgsql-novice@postgresql.org>
Subject: Re: create database, user
Date: 10 Mar 2002 18:58:28 +0000
Message-ID: <1015786708.21922.514.camel@linda> (raw)
In-Reply-To: <20020310182306.GS808@eumel.yoo.net>
References: <20020310182306.GS808@eumel.yoo.net>
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
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: olly@lfix.co.uk, postgresql@thorstenhau.de, pgsql-novice@postgresql.org
Subject: Re: create database, user
In-Reply-To: <1015786708.21922.514.camel@linda>
References: <20020310182306.GS808@eumel.yoo.net> <1015786708.21922.514.camel@linda>
* 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