Postgres SQL

General

This guide is tested on ubuntu feisty with postgresql 8.2. It should work for former and future versions of both too.

General documentation you will find here: postgresql.org - interactive (!) manual with comments.

You should read this in advance to make shure the concepts behind postgresql configuration, startup and security (e.g. 'roles).

User Management

See postgresql-org - user management.

Ubuntu Feisty

Thanks to the article on hocuspok.us, it was very easy to set up postgresql-8.2 running on ubuntu.

Installation

Install postgresql

sudo apt-get install postgresql-8.2 postgresql-client-8.2

For administration we install pgadmin3

sudo apt-get install pgadmin3 pgadmin3-data

Set database admin account

Alter the password for the standard postgres user account (as root)

sudo su
postgres -c psql template1

Inside the database:

template1=# ALTER USER postgres WITH PASSWORD 'new_password';
template1=# \q

/etc/postgresql/8.2/main/postgresql.conf

Assure that the server is accessible on localhost

sudo gedit /etc/postgresql/8.2/main/postgresql.conf

listen_addresses = 'localhost'
password_encryption = on

/etc/postgresql/8.2/main/pg_hba.conf

Security and server settings. If you like to use the server in a network you have to pay attention to three points:

1. Add the server ip to the listen adress
2. Allow special clients to connect
3. Think about using ssl, if you connect through the internet

sudo gedit /etc/postgresql/8.2/main/pg_hba.conf

# - Connection Settings -
# what IP address(es) to listen on; 
# comma-separated list of addresses;
listen_addresses = 'localhost,server_IP' 
 
# Allow any user on the local system to connect to any database under
# any user name using Unix-domain sockets (the default for local
# connections).
#
# Database administrative login by UNIX sockets
local all all trust

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all md5

# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5

# Connections for all PCs on the subnet
#
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
# e.g. host all all 192.168.0.0/24 md5
# e.g. host all all 192.168.0.10 255.255.255.0 md5
host all all [ip address] [subnet mask] md5

Restart postgresql

sudo /etc/init.d/postgresql-8.2 restart

Initial registration with pgAdminIII

ODBC, Postgres and Openoffice

According to PostgreSQL-Datenbanken in OpenOffice.org 2.0 unter Linux.

Ubuntu Gutsy (7.10)

Openoffice <-> ODBC

For connection between openoffice and odebc:

apt-cache search odbc | grep unix
   unixodbc - ODBC tools libraries
   unixodbc-bin - Graphical tools for ODBC management and browsing

apt-get install unixodbc unixodbc-bin

With the command ODBCConfig you can manage the configuration files (part of the package unixodbc-bin). Start it as superuser with gksu or use it to manage your local database configuration files - stored in your home directory.

But for nowe, we will - later - edit the configuration files by ourself.

ODBC <-> Postgresql

For connection between the odbc layer and the database:

apt-cache search odbc
   odbc-postgresql - ODBC driver for PostgreSQL

apt-get install odbc-postgresql

Configuration files

Ubuntu installs its odbc libraries in /usr/lib/odbc. There are two versions of the postgres odbc driver: an ascii version (psqlodbca.so) and a unicode version (psqlodbcw.so). We are using the latter.

vim /etc/odbcinst.ini

[PostgreSQL]
Description = PostgreSQL ODBC Driver
Driver = /usr/lib/odbc/psqlodbcw.so
Setup = /usr/lib/odbc/libodbcpsqlS.so

For system wide database location you have to edit the /etc/odbc.ini file.

There is a section Driver. You have to insert exactly the same name as defined in the odbcinst.ini between the braces, e.g. use PostgreSQL, if your odbcinst.ini is defined as [PostgreSQL]:

vim /etc/odbc.ini

[pgTestDBSource]
Description = PostgreSQL Test Database
Driver = PostgreSQL
Servername = localhost
Database = pgtest
Port = 5432
ReadOnly = No

Establish connection

If you like to connect to an database via openoffice you should use a string like:

odbc://servername/databasename

Don't forget to allow connections in /etc/postgresql/8.2/main/pg_hba.conf (see above) to your database server!

The rest is really self explanatory within the openoffice database assistant ;)