Thread: create database, user

create database, user

From
Thorsten Haude
Date:
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?

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

For the ones knowing MySQL, what I want to do is something like this:
- - - Schnipp - - -
[Connect as Administrator]
create database dbName;

grant all
    on dbName
    to userName
    identified by 'secret';

use inventory;

create table items
(
    [...]
);
- - - Schnapp - - -

Thanks in advance for any help!

Thorsten
--
Die Zensur ist das lebendige Geständnis der Großen, daß sie nur
verdummte Sklaven aber keine freien Völker regieren können.
    - Johann Nepomuk Nestroy

Re: create database, user

From
Oliver Elphick
Date:
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


Re: create database, user

From
"Josh Berkus"
Date:
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

Re: create database, user

From
Thorsten Haude
Date:
Hi,

* Oliver Elphick <olly@lfix.co.uk> [02-03-10 19:58]:
>On Sun, 2002-03-10 at 18:23, Thorsten Haude wrote:
>> 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?
Not the whole thing, but anything I thought would be useful. Not
enough (thought) obviously.

>> - 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.
I did that, and one of the problems was solved by \connect. It never
occured to me that you can use the backslash commands in a file.

The other problem is a password though. I changed one line in your
example to:
    CREATE USER junk_user encrypted password 'secret';
And it fails:
    ERROR:  parser: parse error at or near "encrypted"
What's wrong here?

>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.
I checked, and it should work.

Thank you very much for your help!

Thorsten
--
The opposite of the above statement is also true.

Re: create database, user

From
"Josh Berkus"
Date:
Thorsten,

> The other problem is a password though. I changed one line in your
> example to:
>     CREATE USER junk_user encrypted password 'secret';
> And it fails:
>     ERROR:  parser: parse error at or near "encrypted"
> What's wrong here?

You're probably using PostgreSQL 7.1, or you compiled PostgreSQL 7.2
 without current encryption libraries installed on your machine.

-Josh

Re: create database, user

From
Thorsten Haude
Date:
Hi,

* Josh Berkus <josh@agliodbs.com> [02-03-10 20:58]:
>> The other problem is a password though. I changed one line in your
>> example to:
>>     CREATE USER junk_user encrypted password 'secret';
>> And it fails:
>>     ERROR:  parser: parse error at or near "encrypted"
>> What's wrong here?
>You're probably using PostgreSQL 7.1, or you compiled PostgreSQL 7.2
> without current encryption libraries installed on your machine.
Indeed, I use 7.1 and was not aware that encrypted passwords are a new
feature of 7.2. I should have checked more carefully.

So I will rather install 7.2 right away instead of upgrading later.
Let's see where that will get me.

Thanks a lot!

Thorsten
--
Auch Hunger ist Krieg.
    - Willy Brandt

Re: create database, user

From
Thorsten Haude
Date:
Hi,

* Josh Berkus <josh@agliodbs.com> [02-03-10 20:16]:
>> - 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
Done.

>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.
I think I understand the role of the template dbs, but why are there
two of them?

>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.
Well, this will never reach the Internet, it is completely in-home.
But since one of the purposes of this application is the learning
effect, I'll follow your advice.

>You may wish to subscribe to the pgsql-php or pgsql-perl mailing lists.
I'll do that.

Thank you!

Thorsten
--
There is no drug known to man which becomes safer when its
production and distribution are handed over to criminals.