pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
create database, user
7+ messages / 3 participants
[nested] [flat]

* create database, user
@ 2002-03-10 18:23 Thorsten Haude <postgresql@thorstenhau.de>
  2002-03-10 18:58 ` Re: create database, user Oliver Elphick <olly@lfix.co.uk>
  2002-03-10 19:16 ` Re: create database, user Josh Berkus <josh@agliodbs.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Thorsten Haude @ 2002-03-10 18:23 UTC (permalink / raw)
  To: PostgreSQL Novice ML <pgsql-novice@postgresql.org>

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: create database, user
  2002-03-10 18:23 create database, user Thorsten Haude <postgresql@thorstenhau.de>
@ 2002-03-10 18:58 ` Oliver Elphick <olly@lfix.co.uk>
  2002-03-10 20:04   ` Re: create database, user Thorsten Haude <postgresql@thorstenhau.de>
  1 sibling, 1 reply; 7+ messages in thread

From: Oliver Elphick @ 2002-03-10 18:58 UTC (permalink / raw)
  To: Thorsten Haude <postgresql@thorstenhau.de>; +Cc: PostgreSQL Novice ML <pgsql-novice@postgresql.org>

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 




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: create database, user
  2002-03-10 18:23 create database, user Thorsten Haude <postgresql@thorstenhau.de>
  2002-03-10 18:58 ` Re: create database, user Oliver Elphick <olly@lfix.co.uk>
@ 2002-03-10 20:04   ` Thorsten Haude <postgresql@thorstenhau.de>
  2002-03-10 19:58     ` Re: create database, user Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Thorsten Haude @ 2002-03-10 20:04 UTC (permalink / raw)
  To: PostgreSQL Novice ML <pgsql-novice@postgresql.org>

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.



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: create database, user
  2002-03-10 18:23 create database, user Thorsten Haude <postgresql@thorstenhau.de>
  2002-03-10 18:58 ` Re: create database, user Oliver Elphick <olly@lfix.co.uk>
  2002-03-10 20:04   ` Re: create database, user Thorsten Haude <postgresql@thorstenhau.de>
@ 2002-03-10 19:58     ` Josh Berkus <josh@agliodbs.com>
  2002-03-17 14:53       ` Re: create database, user Thorsten Haude <postgresql@thorstenhau.de>
  0 siblings, 1 reply; 7+ messages in thread

From: Josh Berkus @ 2002-03-10 19:58 UTC (permalink / raw)
  To: Thorsten Haude <postgresql@thorstenhau.de>; PostgreSQL Novice ML <pgsql-novice@postgresql.org>

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: create database, user
  2002-03-10 18:23 create database, user Thorsten Haude <postgresql@thorstenhau.de>
  2002-03-10 18:58 ` Re: create database, user Oliver Elphick <olly@lfix.co.uk>
  2002-03-10 20:04   ` Re: create database, user Thorsten Haude <postgresql@thorstenhau.de>
  2002-03-10 19:58     ` Re: create database, user Josh Berkus <josh@agliodbs.com>
@ 2002-03-17 14:53       ` Thorsten Haude <postgresql@thorstenhau.de>
  0 siblings, 0 replies; 7+ messages in thread

From: Thorsten Haude @ 2002-03-17 14:53 UTC (permalink / raw)
  To: PostgreSQL Novice ML <pgsql-novice@postgresql.org>

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: create database, user
  2002-03-10 18:23 create database, user Thorsten Haude <postgresql@thorstenhau.de>
@ 2002-03-10 19:16 ` Josh Berkus <josh@agliodbs.com>
  2002-03-17 16:43   ` Re: create database, user Thorsten Haude <postgresql@thorstenhau.de>
  1 sibling, 1 reply; 7+ messages in thread

From: Josh Berkus @ 2002-03-10 19:16 UTC (permalink / raw)
  To: Thorsten Haude <postgresql@thorstenhau.de>; PostgreSQL Novice ML <pgsql-novice@postgresql.org>; +Cc: pgsql-php

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: create database, user
  2002-03-10 18:23 create database, user Thorsten Haude <postgresql@thorstenhau.de>
  2002-03-10 19:16 ` Re: create database, user Josh Berkus <josh@agliodbs.com>
@ 2002-03-17 16:43   ` Thorsten Haude <postgresql@thorstenhau.de>
  0 siblings, 0 replies; 7+ messages in thread

From: Thorsten Haude @ 2002-03-17 16:43 UTC (permalink / raw)
  To: PostgreSQL Novice ML <pgsql-novice@postgresql.org>

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.



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2002-03-17 16:43 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-10 18:23 create database, user Thorsten Haude <postgresql@thorstenhau.de>
2002-03-10 18:58 ` Oliver Elphick <olly@lfix.co.uk>
2002-03-10 20:04   ` Thorsten Haude <postgresql@thorstenhau.de>
2002-03-10 19:58     ` Josh Berkus <josh@agliodbs.com>
2002-03-17 14:53       ` Thorsten Haude <postgresql@thorstenhau.de>
2002-03-10 19:16 ` Josh Berkus <josh@agliodbs.com>
2002-03-17 16:43   ` Thorsten Haude <postgresql@thorstenhau.de>

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