The Penguinlinux.com Blog

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 Software 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-1PGDG
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 password, 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 will 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: \copyright 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 postgres (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 accept 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 config 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>

The first question (SQL option: CREATEDB) allows the user to create databases. Not necessary if done for the user.
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> dropuser 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:
a database-user called like the unix-user and
a database named like the unix-user.
All SQL commands must end with semicolon(;)
[TAB] can be used for autocompletion 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:
\l list all databases
\dt list tables
\dS list system tables
\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