Note: The following is part of a series of steps to setup an email server using Exim 4.x, with imap and webmail access. It will use winbind to get user information from an NT server. If you found this page via a search engine it may not cover what you need or you may need to start at the beginning to understand everything I have done.

(Note: These instructions reference software that is now possibly much newer with many new or different configuration options. This page is being left up for reference.)

Quick Steps:

For PostgreSQL:

1. Download and install PostgreSQL
2. Inital setup and config files
3. Start PostgreSQL
4. Create a superuser for PostgreSQL
5. Set up the User(s)
6. Set up the Database(s)
7. Backing up the database

For iODBC:

1. Get iODBC Driver Manager
2. Install psqlODBC
3. Configure iODBC on *nix
4. Test ODBC on *nix
5. Troubleshooting on *nix

For MyODBC on Windows

1. Get MyODBC and Install (or Get psqlodbc and Install?)
2. Configuring a User DSN

For PostgreSQL

I'm using version 7.3.4.

Note: Databases are kept in /var/lib/pgsql

Documentation can be found here: http://www.postgresql.org/docs/

Some basic SQL: http://www.eh10.pwp.blueyonder.co.uk/gisq/howto/xfunaccess.htm

Another RedHat how-to: http://cfm.gs.washington.edu/~adioso/HOWTO/PostgreSQL/RHPostgreSQL.xml

1) Download and Install PostgreSQL

I used the RPMs from http://www.postgresql.org/mirrors-ftp.html.

2) Inital setup and config files

First initalize the database (only need to do this once):
su -m postgres
initdb -D /var/lib/pgsql/data
exit

There are now some files in /var/lib/pgsql/data that can be edited.

pg_hba.conf is used to specify client connection and authentication rules. Here are some examples:

#Type  Database  User     IP-Address       IP-Mask          Method
# allow local user with password
local  all       all                                        password
# allow local user without password
local  all       all                                        trust
# allow (private ip range) with password
host   all       all      192.168.1.0      255.255.255.0    password
# allow localhost, connecting via tcp/ip, without password
host   all       all      127.0.0.1        255.255.255.255  trust

pg_ident.conf is used to map ident/Unix usernames to PostgreSQL usernames.

postgresql.conf is for run-time options. This may be of help: Annotated postgresql.conf. Here is what I have changed in mine:

#       Connection Parameters
#
tcpip_socket = true
#ssl = false
 
max_connections = 27
superuser_reserved_connections = 2
 
port = 5432
hostname_lookup = false
#show_source_port = false

3) Start PostgreSQL

service postgresql start

To run at startup:
chkconfig postgresql on

4) Create a superuser for PostgreSQL

Postgresql has one superuser defined already, which is typically called postgres and is the user name PostgreSQL runs under. If you need to do "superuser" things and are logged in as root, you can:
su -l postgres

To make a superuser with a password, as root do:
su -l postgres
createuser -a -P user
exit
You will be prompted for a password, or you can leave the "-P" option off for none. Use:
createuser --help
for more options.

5) Set up the User(s)

For the LDAP backend a user is required. For security, I think it is best not to have this user be a superuser. Users can be created using the createuser, as shown above, or with the SQL command:
psql -d database_name -h host_name -U username -W
CREATE USER username WITH PASSWORD 'password';
If you need a database to connect to, try "template1".

See the docs for more. Also see "pg_ident.conf" for how to map *nix-to-PostgreSQL names and "pg_hba.conf" for connection restrictions.

Example user setup: User dbedit, with password, can access from all hosts and has the privileges of Select, Insert, Update, Delete, Rule, References, and Trigger. This is the user I will use when making an ODBC connection from MS Access. User ldapac, with no password, can access from localhost and has the privilege of Select, because I am not editing with LDAP at this time.

Note that privileges are set per table.

GRANT ALL ON TABLE table_name TO user;
GRANT SELECT ON TABLE table_name TO user;

6) Set up the Database(s)

See the documentation or use something like Webmin. (Note: I've found Webmin support for managing PostgreSQL lacking compared to it's support for MySQL. Maybe if I ever find time I'll help fix that, but until then be warned.) There is a Windows version of psql.

Quick psql notes:
To connect to a database via TCP/IP with a username and password use:
psql -d database_name -h host_name -U username -W
To connect to a database via TCP/IP, no password, and execute SQL from a file:
psql -d database_name -h host_name -U username <filename
To see a list of tables in the current database use:
\d
To exit psql use:
\q

For ours, basically everything is in one database table - name, phone number, email, snail-mail, etc. You need to figure out what PostgreSQL column types you need for your data. See the manual, User's Guide, Chapter 2 "Data Definition". MS Access has a field type "Yes/No", which translates as -1/0 respectively in Access (97), but will export as 1,0. I tried to use BOOLEAN (and set a translation option in ODBC), but there were too many problems (BoolAsChar=0, checkboxes worked but queries did not; vice-versa was vice-versa). Therefore I went with int2. For the rest VARCHAR(length) will work nicely.

Another choice you may want to make is converting everything to lowercase - database names, table names, and field names. Otherwise there are situations, such as when using psql, that the client will assume it should be lowercase. If you want mixed case and run into the "problem" that psql gives, simply enclose the name in double-quotes. Example:
SELECT * FROM "Table_Name";
While Webmin had no problem with mixed case table names it does with column names (v1.121). Access works without modification, but if you are going to be using PostgreSQL as a backend to OpenLDAP go with all lowercase names everywhere and save yourself a lot of problems.

Since we are migrating from a MS Access database, what follows are some relating notes.

What I did was open the MS Access database, created a query that got everything, after cleaning up the autonumber primary key, and exported it as a tab-delimited file. I also made sure no column names had spaces or were over 64 characters long.

How to rename fields in an Access query:
Generate your query via the wizard or however you want. Insert all the fields needed. Now switch to design view and find the field you want to change. Example:
Field: Name with Spaces
Change to:
Field: NoMoreSpaces: Name with Spaces
Remember that in the *nix world things are typically case sensitive.

To 'clean up' your autonumber field, if it is not used in relation to other tables, in an existing table (make a backup copy first!):

• Open the existing table in design view.
• Delete the primary key (assuming autonumber).
• Create a new field, same or different name, and make it an autonumber field and the primary key.

For importing:
To import a comma-delimited file of data into a table use
COPY table_name FROM 'filename' WITH DELIMITER ',' NULL '';
To import a tab-delimited file of data into a table use
COPY table_name FROM 'filename' WITH NULL '';
Make sure your import file has a "\." at the end on a line by itself with a hard return. Also note the NULL above is designated as a blank. PostgreSQL's default NULL is "\N", but most programs export as a blank. Once importing is finished it is recommended to run
VACUUM FULL table_name
to clean up, especially if there were errors. Note that COPY is an append operation. To delete all data use:
TRUNCATE TABLE table_name

Once everything is imported, create a permanent sequence generator for the primary key (assuming it was an autonumber field in Access and you did not set the field to type "serial" when creating). This will allow the auto_number to work when creating new records with Access.

SELECT max(primary_key_field) FROM table_name;
CREATE SEQUENCE sequence_name START previous_query_result-plus-1;
ALTER TABLE table_name ALTER COLUMN primary_key_field SET DEFAULT nextval('sequence_name');

If you did not create the field that is to be a primary key as a primary key (not null primary key) then:
CREATE UNIQUE INDEX index_name ON table_name (primary_key_field);
And remember to give users/groups the correct priviledges for sequence_name
GRANT ALL ON sequence_name TO user;

A quick note on the above when you have existing data: I thought I was being smart by creating the id field as "serial not null primary key", which creates the sequence automatically. Some how, after importing my data the sequence's last number was only 14 (and not 2564 like it should've been). This, of course, caused Access to complain that you can't have duplicate values for primary keys (it was trying to assign 15, which existed). Well, once a sequence is created you can't drop it without dropping the table. Moral of the story, for me anyway, is create the id field as "int4 not null primary", import the data, and then manually follow the steps above to create the sequence.

Tip: How to creat a view that lists only domains, and only once, from a field that contains email addresses:
(See my Exim how-to for how I make use of this.)

CREATE VIEW email_domains (domain) AS
SELECT LOWER(Substr(emailaddress,Strpos(emailaddress,'@')+1,(LENGTH(emailaddress)-Strpos(emailaddress,'@')))) AS X
FROM tblcontacts
GROUP BY LOWER(Substr(emailaddress,Strpos(emailaddress,'@')+1,(LENGTH(emailaddress)-Strpos(emailaddress,'@'))));

If you need more information about the back-sql setup, go here and read "Simple Example Setup". Notes about creating ldap_entries as a VIEW can be found there.

7) Backing up the database

Refer to the documentation, Chapter 9: "Backup and Restore", for more information.

How to backup without shutting down the database by specifing the host, database name, a user to connect as, to be prompted for a password, and to compress the output:
pg_dump -h localhost -d database_name -U username -W | gzip >file_name

For iODBC

The following is needed for back-sql. If you are only trying to interface MS Access with PostgreSQL you can skip this section.

1) Get iODBC Driver Manager

http://www.iodbc.org/

Available in an RPM. iODBC 2.50.3 or later needed & the devel-*.rpm as well. Install with:
rpm -ivh name_of.rpm

You may also want the SDK which has the odbctest program. The link is at the bottom of the page for downloading the binaries.
Note: Its extension is .taz. Just gunzip then tar -xvf to unpack.

2) Install psqlODBC

Download and compile the source. I'm using 7.2.5:
http://gborg.postgresql.org/
Take the time to read the documentation available there.

You'll also need the (pre-compiled) Windows one if you plan on using the database with something like MS Access.

How to manually compile (for iodbc):

See the README.unix file for more information.

How to make an rpm of the source:

1. Place psqlodbc-[version].tar.gz in /usr/src/redhat/SOURCES
2. Create psqlodbc.spec in /usr/src/redhat/SPECS
3. Run rpmbuild -bi psqlodbc.spec to test compiling.
4. Run rpmbuild -bb psqlodbc.spec to build the binaries or psqlodbc -ba exim.spec to build all.

Note: I am running rpm 4.1.1-1.8x. See rpm.org for the latest.

To download my rpm or view my full spec file, go here.

3) Configure iODBC on *nix

Edit the /etc/odbc.ini file.

Here is a sample one using psqlodbc 7.2.5:

; begin odbc.ini
[ODBC Data Sources]
test = PostgreSQL ODBC Driver
 
[test]
Driver = /usr/lib/psqlodbc.so
Description = PostgreSQL Data Source
DSN = test
Servername = localhost
Port = 5432
;Socket = 4096
Protocol = 6.4 # 7.2 or other values
UserName = valid_user
Password = valid_passwd
Database = test
ReadOnly = no
ServerType = Postgres
FetchBufferSize = 99
ServerOptions =
ConnectOptions =
;Options = 3
Trace = 0
TraceFile = /var/log/PostgreSQL_test_trace.log
Debug = 0
DebugFile = /var/log/PostgreSQL_test_debug.log
 
[Default]
Driver = /usr/lib/psqlodbc.so
Description = PostgreSQL Data Source
Server = localhost
Port = 5432
UserName = valid_user
Password = valid_passwd
;Socket = 4096
;end odbc.ini

4) Test ODBC

This is where you need the odbctest from the SDK. It is not required, but can be helpful for verifying ODBC is working. If you are only going to access via Windows you don't need to do this, but it can be handy for troubleshooting.

cd to where you unpacked the SDK. In the odbcsdk/examples directory you will find odbctest. To run:
./odbctest

Now enter the name of the database to connect to that was defined in your odbc.ini file. In the above example it is test, so enter:
DSN=test

To exit:
exit

To download my rpm go here.

5) Troubleshooting

Don't forget to add allowed hosts in pg_hba.conf.

Again, PostgreSQL converts all name to lowercase when executing SQL unless the names are wrapped in double-quotes.

For psqlODBC on Windows

1) Get psqlODBC and Install

Download from here: http://gborg.postgresql.org/

Get the "Full" installation file if running pre-Windows2000, otherwise I recommend the "MSI" installation. I'm using 07_03_0200.

It is recommended that you load the additional ODBC server-side functions vi odbc.sql, which is available in the source download. At this time I have not as they do not seem to be needed for my simple contact database.

2) Configuring a User DSN

Reading that may be of interest:

• Using Microsoft Access with PostgreSQL at techdocs.postgresql.org
• Microsoft Access VBA with PostgreSQL at Gborg
• How-Tos and FAQs at Gborg
• Moving to PostgreSQL's Object-Relational DBMS at Linux Journal

When configuring the ODBC driver and MS Access is to be used, I have set:
Options, Datasource, page 1:

• Disable Genetic Optimizer: yes
• Keyset Query Optimization: yes
• Recognize Unique Indexes: yes
• Use Declare/Fetch: no
• CommLog: no
• Parse Statements: no
• Cancel as FreeStmt: no
• MyLog: no
• Unknown Sizes: Maximum
• Text as LongVarChar: yes
• Unknowns as LongVarChar: no
• Bool as Char: yes
• Max Varchar: 254
• Max LongVarChar: 8190
• Cache Size: 100
• SysTable Prefixes: dd_;

• Read Only: no
• Show System Tables: no
• LF <-> CR/LF conversion: yes
• Updatable Cursors: yes
• bytea as LO: no
• Row Versioning: no
• Disallow Premature: no
• True is -1: yes
• Server side prepare: no
• Int8 As: default
• Protocol: 7.X,6.4+
• OID Options: (none checked)
• Connect Settings: (blank)

I also noticed, when playing with a manually created User DSN, that if I modified the DSN I had to delete and then relink the tables to get any changed values to be used.

How to check and, if not exist, auto-create the DSN via Access 97 (odbc driver must be installed on the machine):
(Modified from http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q159691&LN=EN-US)

Create an Access table called tblODBCDataSources:
Database: Text, 50
UID: Text, 50
PWD: Text, 50
Server: Text, 50
ODBCTableName: Text, 50
LocalTableName: Text, 50, Primary Key
DSN: Text, 50
Driver: Text, 50
Option: Text, 255
Port: Text, 50

Sample data for the above table:
Database: PostgreSQL_database_name
UID: valid_user
PWD: valid_password
Server: name_or_ip
ODBCTableName: PostgreSQL_table_name
LocalTableName: as_it_will_appear_in_access
DSN: name_for_the_dsn
Driver: PostgreSQL
Option: A0=0;A6=;A7=100;A8=4096;B0=254;B1=8190;B9=0;BI=0;C2=dd_
Port: 5432

See below for options. Note that options not specified use their default values.

Create a Module called CreateDSN:

'***************************************************************
'The DoesTblExist function validates the existence of a TableDef
'object in the current database. The result determines if an
'object should be appended or its Connect property refreshed.
'***************************************************************
 
Function DoesTblExist(strTblName As String) As Boolean
   On Error Resume Next
   Dim db As Database, tbl As TableDef
   Set db = CurrentDb
   Set tbl = db.TableDefs(strTblName)
   If Err.Number = 3265 Then   ' Item not found.
      DoesTblExist = False
      Exit Function
   End If
   DoesTblExist = True
End Function
 
Function CreateODBCLinkedTables() As Boolean
   On Error GoTo CreateODBCLinkedTables_Err
   Dim strTblName As String, strConn As String
   Dim db As Database, rs As Recordset, tbl As TableDef
   ' ---------------------------------------------
   ' Register ODBC database(s)
   ' ---------------------------------------------
   Set db = CurrentDb
   Set rs = db.OpenRecordset("tblODBCDataSources")
   With rs
      While Not .EOF
         DBEngine.RegisterDatabase rs("DSN"), _
                  rs("Driver"), _
                  True, _
                  "Description=VSS - " & rs("DataBase") & _
                  Chr(13) & "Server=" & rs("Server") & _
                  Chr(13) & "Database=" & rs("DataBase")
         ' ---------------------------------------------
         ' Link table
         ' ---------------------------------------------
         strTblName = rs("LocalTableName")
         strConn = "ODBC;"
         strConn = strConn & "DSN=" & rs("DSN") & ";"
         strConn = strConn & "DATABASE=" & rs("DataBase") & ";"
         strConn = strConn & "UID=" & rs("UID") & ";"
         strConn = strConn & "PWD=" & rs("PWD") & ";"
         strConn = strConn & "OPTION=" & rs("Option") & ";"
         strConn = strConn & "TABLE=" & rs("ODBCTableName")
         If (DoesTblExist(strTblName) = False) Then
            Set tbl = db.CreateTableDef(strTblName, _
                          dbAttachSavePWD, rs("ODBCTableName"), _
                          strConn)
            db.TableDefs.Append tbl
         Else
            Set tbl = db.TableDefs(strTblName)
            tbl.Connect = strConn
            tbl.RefreshLink
         End If
 
          rs.MoveNext
      Wend
   End With
   CreateODBCLinkedTables = True
   MsgBox "Refreshed ODBC Data Sources", vbInformation
CreateODBCLinkedTables_End:
   Exit Function
CreateODBCLinkedTables_Err:
   MsgBox Err.Description, vbCritical, "MyApp"
   Resume CreateODBCLinkedTables_End
 
'   PG_ODBC_PARAMETER           ACCESS_PARAMETER
'   *********************************************
'   READONLY                    A0
'   PROTOCOL                    A1
'   FAKEOIDINDEX                A2  'A2 must be 0 unless A3=1
'   SHOWOIDCOLUMN               A3
'   ROWVERSIONING               A4
'   SHOWSYSTEMTABLES            A5
'   CONNSETTINGS                A6  'must be a value, blank is A6=;
'   FETCH                       A7  'must be a value, try 100
'   SOCKET                      A8  'default 4096
'   UNKNOWNSIZES                A9  ' range [0-2]
'   MAXVARCHARSIZE              B0  'must be a value, default 254
'   MAXLONGVARCHARSIZE          B1
'   DEBUG                       B2
'   COMMLOG                     B3
'   OPTIMIZER                   B4  ' note that 1 = _cancel_ generic optimizer...
'   KSQO                        B5
'   USEDECLAREFETCH             B6
'   TEXTASLONGVARCHAR           B7
'   UNKNOWNSASLONGVARCHAR       B8
'   BOOLSASCHAR                 B9
'   INVERSEBOOL?                BI  '"true is -1"?, 0=checked?
'   PARSE                       C0
'   CANCELASFREESTMT            C1
'   EXTRASYSTABLEPREFIXES       C2  'must be a value, default dd_
 
End Function
'**** End Code ***

Now just call CreateODBCLinkedTables() in the database's AutoExec macro (RunCode). You may want to rem out the message dialog "Refreshed ODBC Data Sources" in the code above if you do not want your users bothered by it each time it loads. Note that if you check the User DSN it will seem that the OPTION value didn't pass. However, if you open the linked table in design view and then look at it's properties you will see OPTION there.