Thread: create database, user
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
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
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
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.
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
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
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.