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 settings

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

# 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 - not working

Not possible until now --Apos 17:32, 28 September 2007 (CEST).

See the TODO 's at the end of this section.

1. libodbcpsql.so is not installed
2. Cannot compile unixodbc manually due to compile error

Don't read further.

---

1. 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

2. 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

Unfortunately there ist no libpostgresql.so library in this file. You have to build it by yourself. On your ubuntu installation you'll find the documentation

firefox file://usr/share/doc/odbc-postgresql/docs/unix-compilation.html

First install the unixodbc package:

apt-get install unixodbc 

To be able to compile the source package of the odbc driver you have to install the development package of the server (where #.# is you postgres version):

apt-get install postgresql-server-dev-8.2 cdbs

cdbs is needed to compile the following source-package. There might be missing some other packages on your particular system.

apt-get source psqlodbc
cd psqlodbc-*
dpkg-buildpackage
dpkg -i 

3. Edit the configuration files

Ubuntu installs its odbc libraries in /usr/lib/odbc

vim /etc/odbcinst.ini

[PostgreSQL]
Description = PostgreSQL ODBC Driver

TODO libodbcpsql.so does not exit after installation of unixodbc package.

Driver = /usr/lib/odbc/libodbcpsql.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