PostgreSQL 7.2 upgrade and crypto

PostgreSQL 7.2 upgrade and crypto

I upgraded to PostgreSQL 7.2 earlier this week.
I had a few problems, but they were really annoying.
The lesson is: observe, think, conclude.

Your first port of call should be the HISTORY file which comes with the PostgreSQL source.

The next step should be a backup of your data:

pg_dump dbname > dbname.pgdump

Then, and only then should you even consider installing 7.2.

Configuration

Much of this information is contained in my original PostgreSQL article
so I will only briefly describe what I did.

As root, I issued the following command to create a new PostgreSQL database cluster:


# su -l pgsql /usr/local/bin/initdb /usr/local/pgsql/data


Then I started the database daemon:


# /usr/local/etc/rc.d/010.pgsql.sh start


NOTE: in recent versions of PostgreSQL (e.g. 8.2), the command is:


# /usr/local/etc/rc.d/postgresql start


Then I created myself a new PostgreSQL user account:


# su pgsql
$ createuser dan


As dan, I issued the following command:


$ createdb dbname


Then I loaded the database:

psql dbname < dbname.pgdump

That’s when the problems started.

Timestamps

The first problem occurred during the loading of the data back into a new database:


ERROR: Bad timestamp external representation 'current_timestamp'


The solution is to edit my data and remove the quotes from
current_timestamp. I did
this using my favorite editor, joe.

This error occurred next:


ERROR: copy: line 2537, Bad timestamp external representation 'current'
lost synchronization with server, resetting connection ^C


This was a problem with my data. I had a timestamp field which contained the
value ‘current’. I modified the data within my 7.1 database, pg_dump’d the
data, and then did another import. This time the data import worked.

Relocated files

The PostgreSQL libraries move from /usr/local/lib/ to /usr/local/lib/postgresql/
with this upgrade. This should not cause you any problems. Unless you are using
features such as stored procedures. This one had me stumped for quite some time.

The error which occurred during data loading is:


ERROR: stat failed on file '/usr/local/lib/plpgsql.so': No such file or directory


The problem here is that the file actually resides at
/usr/local/lib/postgresql/plpgsql.so. Again, as with
the timestamp issue, this is a data problem. The fix is to modify my data file to
change the path. The line in question was:


$ grep plpgsql.so fp2migration.sql
CREATE OR REPLACE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS 'plpgsql', 'plpgsql_call_handler' LANGUAGE 'C';


Note that it is no longer necessary to use a full path to the lib.

I also encountered another error message:

ERROR: SearchSysCache: Bad cache id 27

This was caused by trying to load a 7.1 plpgsql.so into 7.2. I was greeted with this message
when I had the incorrect path in the function plpgsql_call_handler (see above). As mentioned
above, the path is no longer necessary.

I originally did a delete function, then a create function. That rendered my functions
unusable. I drop’d the database and then reloaded it. The error I was getting was:


ERROR: fmgr_info: function 16557: cache lookup failed


Groups

My original pg_dump did not include the groups and users I had created. That was my error.
I should have used pg_dumpall to get that data. See man pg_dumpall:


pg_dumpall -g dbname


I think I’ll start using pg_dumpall much more often as part of my backup strategy. Remember: users
and groups are global and can affect more than one database.

The Crypto

If you did a make clean after you installed
PostgreSQL, you’ll need to run make again before
building this add-on.

There is a cryptography library included with PostgreSQL. To install it, I followed these instructions
as found in the README.pgcrypto in the directory indicated below. Note that the 7.2
part of the directory may change depending upon which version you are using.


# cd /usr/ports/databases/postgresql7/work/postgresql-7.2/contrib/pgcrypto
edit Makefile and specify cryptolib = openssl
# gmake
# gmake install


If you haven’t already done so, you should then install PostgreSQL
and start it.

The crypto library will install three things:

• /usr/local/share/postgresql/contrib/pgcrypto.sql – sql used to create the crypto stored procedures
• /usr/local/share/doc/postgresql/contrib/README.pgcrypto – the documentation
• /usr/local/lib/postgresql/pgcrypto.so – the library

To access the library functions, you must load the stored procedures into your database.
This step will not be necessary if you are setting up a new PostgreSQL
server and you will be restoring a dump of a database which already
contains these functions.


psql db_name < /usr/local/share/postgresql/contrib/pgcrypto.sql

Please read the documentation supplied. But here’s an example which you can use to confirm
that the functions and library are functioning:

fp2migration=# select DIGEST('adfsd', 'md5');
                   digest
---------------------------------------------
 \374\373J|Nm\365\2314\224'\243m\026\314\035
(1 row)

PHP problems (added on 27 February 2002)

Today a PHP exploit was announced.
After implementing the recommendations within that advisory (i.e. a change to php.ini), I restarted
Apache, which refused to restart:


Syntax error on line 240 of /usr/local/etc/apache/httpd.conf:
Cannot load /usr/local/libexec/apache/libphp4.so into server: /usr/local/libexec/apache/libphp4.so: Undefined symbol "pg_encoding_to_char"


The line in question contained:


LoadModule php4_module libexec/apache/libphp4.so


The clue to what needs to be done is in the undefined symbol (i.e. pg_). I recompiled
and installed php4:


/usr/ports/www/mod_php4
make
pkg_delete -f mod_php4-4.1.1
make install
make clean


I could have used portupgrade. I did the make first to ensure the port built
successfully. Then, and only then did I delete the existing php installation.

Soundex/Fuzzy matching (added on 18 March 2002)

If you did a make clean after you installed
PostgreSQL, you’ll need to run make again before
building this add-on.

There is a SOUNDEX/fuzzy matching library included with PostgreSQL. It allows you to search for values
which sound similar to a given value. To install it, I followed these instructions
as found in the README.fuzzystrmatch in the directory indicated below:


# cd /usr/ports/databases/postgresql7/work/postgresql-7.2/contrib/fuzzystrmatch
# gmake
# gmake install


If you haven’t already done so, you should then install PostgreSQL.

The crypto library will install three things:

• /usr/local/share/postgresql/contrib/fuzzystrmatch.sql- sql used to create the SOUNDEX/fuzzy match stored procedures
• /usr/local/share/doc/postgresql/contrib/README.fuzzystrmatch – the fuzzy match documentation
• /usr/local/share/doc/postgresql/contrib/README.soundex – the soundex documentation
• /usr/local/lib/postgresql/fuzzystrmatch.so – the library

To access the library functions, you must load the stored procedures into your database.
This step will not be necessary if you are setting up a new PostgreSQL
server and you will be restoring a dump of a database which already
contains these functions.


psql db_name < /usr/local/share/postgresql/contrib/fuzzystrmatch.sql

Please read the documentation supplied. But here’s an example which you can use to confirm
that the functions and library are functioning:

freshports=# select levenshtein('MacDonald', 'McDonald');
 levenshtein
-------------
           1
(1 row)

freshports=# select levenshtein('MacDonald', 'Smith');
 levenshtein
-------------
           9
(1 row)

freshports=#
	
  


pgAdmin II - Accessing the database remotely (added on 13 August 2002)
  
	
	

	I have finally given in.  I'm going to try a Windows GUI for my database.  After looking at
	the list of related PostgreSQL projects,
	I decided to try pgAdmin II.  But in order to use
	this application, I need to tell PostgreSQL to accept connections from other boxes.  I will do
	this in two steps:

	

	
Allow remote connections
	
Restrict remote connections to a specific IP address
	

	

	According to the Run-time configuration information, I need to specify
	TCPIP_SOCKET = true in my ~pgsql/data/postgresql.conf file.  After 
	making this change, I stopped, then restarted the postmaster:

	

	# /usr/local/etc/rc.d/010.pgsql.sh stop

	# /usr/local/etc/rc.d/010.pgsql.sh start
	

	

	NOTE: in recent versions of PostgreSQL (e.g. 8.2), the command is:	
	

	# /usr/local/etc/rc.d/postgresql stop
	# /usr/local/etc/rc.d/postgresql start
	
	
	

I verified that incoming connections were being accepted:

[dan@bast:~] $ telnet undef 5432
Trying 192.168.0.56...
Connected to undef.unixathome.org.
Escape character is '^]'.
lkjadf
EFATAL 1:  invalid length of startup packet
Connection closed by foreign host.

This takes care of the first part of step 1. The next step is to ensure that the user can log in. For this, I read
Client Authentication. I discovered I need to alter
~pgsql/data/pg_hba.conf. I added the following line:

host       all         192.168.0.99  255.255.255.255    trust

This means to trust all incoming connections from 192.168.0.99. This may not be what you want. Please read
the documentation. In my situation, that IP address can be trusted. You decide what is right for you. After
making this change, I had to restart the postmaster (see above for how I did it before).

If you see the error:

No pg_hba.conf entry for host 192.168.0.99, user dan, database template1

...then you either forget to add the entry to the pg_hba.conf or restart the postmaster.

Installing pgAdmin II was pretty simple. I then started it and did a File->Connect. I supplied the IP address
of the box on which PostgreSQL was running, and the username. Then I clicked connect. So pgAdmin II seems like
a good tool.