PostgreSQL µHowTo - CONCEPT

completemiscreantΔιαχείριση Δεδομένων

28 Νοε 2012 (πριν από 4 χρόνια και 8 μήνες)

688 εμφανίσεις

PostgreSQL µHowTo

Alexander Krumpholz, 28 February 2002.


Introduction



PostgreSQL is a relational database management system.



PostgreSQL is free and the complete source is available.



PostgreSQL's actual version is 7.2. (Feb 2002)

Installation



Get Softwar
e from
http://www.postgresql.org


RPMS worked fine for PostgreSQL 7.2 under RedHat 7.2

We installed the following RPM
-
packages:





postgresql
-
libs
-
7.2
-
1PGDG





postgresql
-
7.2
-
1PGDG





postgresql
-
server
-
7.2
-
1PG
DG





postgresql
-
devel
-
7.2
-
1PGDG





postgresql
-
contrib
-
7.2
-
1PGDG





postgresql
-
perl
-
7.2
-
1PGDG





postgresql
-
jdbc
-
7.2
-
1PGDG



PostgreSQL usually runs as user and group
postgres

or
pgsql
.


RPM creates:
uid=26(postgres) gid=26(postgres), but doesn't set a p
assword, so only root can su
into postgres.


You can set a unix
-
password if you want.



Set the dbms to be started at boot time:


Update
/etc/init.d/postgresql

by adding the right runlevels (e.g.345):





# chkconfig:
-

85 15





# chkconfig: 345 85 15

Then

set the links by running these commands:





chkconfig
--
add postgresql





chkconfig postgresql reset

Note also that
/etc/init.d/postgresql

is installed root.root, mode 0700.


This mode
was changed to 0755 to enable status checks by any user.






After
installing the software, the database needs to be initialized as unix
-
user
postgres

(or pgsql) (automatically done by /etc/init.d/postgresql).


This creates the
default database
template1
, which also acts as template for future
databases.


New databases wi
ll be created as copies of
template1
.



Start the database





/etc/init.d/postgresql start



The user
postgres

can now login into database
template1





postgres
-
bash> psql template1





Welcome to psql, the PostgreSQL interactive terminal.






Type:


\
copyr
ight for distribution terms












\
h for help with SQL commands












\
? for help on internal slash commands












\
g or terminate with semicolon to execute query












\
q to quit






template1=#



Set a password for the database
-
user
po
stgres

(while being logged into the
database (e.g.
template1
)).





template1=# ALTER USER postgres WITH PASSWORD 'topsecret';





ALTER USER





template1=#



Log out of the database (
\
q
):





template1=#
\
q





alex
-
bash>



Configure the database to
not acce
pt

connections without a password:


Update
~postgres/data/pg_hba.conf

(pretty good documented config file):





#local





all







































trust





#host






all






127.0.0.1




255.255.255.255





trust





local






all







































password





host







all






127.0.0.1




255.255.255.255





password



Configure the database to
accept

connections via tcpip (e.g. needed for RT2)


Update
~postgres/data/postgresql.conf

(pretty good documented confi
g file):





#tcpip_socket = false





tcpip_socket = true

Database
-
User



Add database
-
users

via the SQL command
CREATE USER


or the SHELL
-
script
createuser
:





template1=# CREATE USER newuser WITH PASSWORD 'secret';





CREATE USER





template1=#






alex
-
bash> createuser





Enter name of user to add: test





Shall the new user be allowed to create databases? (y/n) y





Shall the new user be allowed to create more new users? (y/n) n





CREATE USER





alex
-
bash>

o

The first question (SQL option: CREA
TEDB) allows the user to create
databases. Not necessary if done for the user.

o

The second question (SQL option: CREATEUSER) allows the user to
create other users


AND REMOVES ALL SECURITY CHECKS! THE USER GETS
ADMIN RIGHTS AND CAN R/W/D ALL DATABASES!


Not

a problem if known, but the variable could have a better name like
'MAKEITAGOD' ;)



Delete database
-
users

via the SQL command
DROP USER


or the SHELL
-
script
dropuser
:





template1=# DROP USER newuser;





DROP USER





template1=#






alex
-
bash> dropuse
r newuser;





DROP USER





alex
-
bash>

Databases



Create databases

via the SQL command
CREATE DATABASE


or the SHELL
-
script
createdb
:





template1=# CREATE DATABASE newdatabase;





CREATE DATABASE





template1=#






alex
-
bash> createdb newdatabase





CREATE DATABASE





alex
-
bash>



Delete databases

via the SQL command
DROP USER


or the SHELL
-
script
dropuser
:





template1=# DROP DATABASE newdatabase;





DROP DATABASE





template1=#






alex
-
bash> dropdb newdatabase;





DROP DATABASE





alex
-
bash>

psql



psql is the user
-
interface (SQL) to the database.



If no parameter is given, psql expects:

o

a database
-
user called like the unix
-
user and

o

a database named like the unix
-
user.



All SQL commands must end with semicolon(;)



[TAB] can be used for autocomplet
ion of SQL commands in psql (but not all
allowed parameter are showing up!).



\
h

for help with SQL commands



\
?

to show internal psql commands like:

o

\
l

list all databases

o

\
dt

list tables

o

\
dS

list system tables

o

\
q

quit psql

Settings

postgresql.conf



~postgres/
data/postgresql.conf



PostgreSQL configuration file

pg_hba.conf



~postgres/data/pg_hba.conf



PostgreSQL HOST ACCESS CONTROL FILE

References



PostgreSQL:
http://www.postgresql.org



PostgreSQL: Introduction and Concepts:
(book)
http://www.ca.postgresql.org/docs/aw_pgsql_book/index.html


$Id: postgres_microhowto.html,v 1.2 2002/05/09 01:08:15 idm Exp $