Renaming database in PostgreSQL

Sometimes you have a system with legacy naming standards, but you really want to switch over to the new standard to keep all the scripting clean without some exceptions no one is going to remember in 12 months. Oracle had the command ALTER DATABASE, but since Oracle 10 you need to take the database offline and do some magic. MySQL got the RENAME DATABASE option with release 5.1.7 and lost the option again with release 5.1.23 as it was eating data.

Luckily PostgreSQL still has the command ALTER DATABASE so let rename a database and it’s owner. Before we start we need the password and then we need to login as the PostgreSQL superuser postgres or another account with similar privileges. So first we check the database name and owner.

postgres=# \l
                                    List of databases
     Name     |  Owner   | Encoding  |  Collation  |    Ctype    |   Access privileges   
--------------+----------+-----------+-------------+-------------+-----------------------
 dbu0001      | dbu0001  | UTF8      | en_US.UTF-8 | en_US.UTF-8 | 

Now we rename the database owner as we made a typo and we need to set the password again.

postgres=# alter user dbu0001 rename to dbo0001;
NOTICE:  MD5 password cleared because of role rename
ALTER ROLE
postgres=# alter user dbo0001 password 'yeaxaureiraeLohsh6deJ2ohngahpu9a';
ALTER ROLE

The second task is to rename the database to the correct name.

postgres=# alter database dbu0001 rename to dbs0001;
ALTER DATABASE

And basically we are now done as the ownership was already modified when we renamed the account with our first statement, but let check what PostgreSQL says it now has.

postgres=# \l
                                    List of databases
     Name     |  Owner   | Encoding  |  Collation  |    Ctype    |   Access privileges   
--------------+----------+-----------+-------------+-------------+-----------------------
 dbs0001      | dbo0001  | UTF8      | en_US.UTF-8 | en_US.UTF-8 | 

Please don’t forget to update the connection strings for applications using this database and maybe GRANTS that have been set and the pg_hba.conf file.

Starting to stop SQL-injections, part 2

In a previous posting I gave an example on how to make database queries safer by using parameter binding and basically stopping SQL-injections. The next step is to make the code more readable and maintainable. This doesn’t sound a like a priority for secure software development, but readable code is also code that can be verified and maintained by other people. It gives you the edge to debug problems quickly and invites others to supply patches. So let take the example where the previous posting ended.

$sth = $dbh_rw->prepare('select userid from accounts where username = ?');
$sth->execute(array($form_username));

For one or two parameters this may work, but when queries become bigger you need to start counting and counting beyond three is a bad idea in most cases. So let change the question mark with a named variable called ‘:username’ in this example. One could then use the function bindParam() to specify which named variable needs to be replaced and has additional features, but in this example we use the standard binding during in execute phase.

$sth = $dbh_rw->prepare('select userid from accounts where username = :username');
$sth->execute(array(':username'=>$form_username));

Please remember to use a named variable only once in a SQL-query as it will only be replaced one time and not multiple times.

Starting to stop SQL-injections

In a lot of PHP-examples strings are concatenated before a database query is being executed as below. Some examples advise to use PHP-functions mysql_real_escape_string() and/or addslashes() to make database query safe against SQL-injections. But this isn’t really a solution as when using addslashes() also requires the use of stripslashes() after retrieving data from a database. Some sites show the lack of a proper implementation and show the famous \’ string in a website.

$sth = $dbh_rw->prepare('select userid from accounts where username = "'.$form_username.'"');
$sth->execute();

Like in Perl with DBI, also PHP has PDO that allows for variables to be parameterised while executing a query as in the example below. This removes the need for home made solutions that don’t cover all usecases and allows for a way to provide a stable and more secure interface for your applications when communicating with databases.

$sth = $dbh_rw->prepare('select userid from accounts where username = ?');
$sth->execute(array($form_username));

This doesn’t stop the need of sanitizing variables like as with input from users.

PostgreSQL en NULL

Mijn docenten zullen wel de koude rillingen krijgen, maar soms heb je gewoon attribuut op je tupel staan die de geen waarde heeft. NULL is hier de algemene benaming voor zoals C-programmeurs die ook wel kennen. De grap komt hoe je tupels gaat selecteren die geen waarde hebben. Veel mensen zie je het volgende statement gebruiken en daarna zoeken waarom hun code fout loopt.

# select * from table where column1 = NULL;

Het correcte statement hiervoor staat hieronder en de truuk zit hem in de IS. Je wilt geen vergelijking van de waarde van het attribuut, maar je wilt iets weten over de staat van het attribuut.

# select * from table where column1 IS NULL;
# select * from table where column1 IS NOT NULL;

De tweede regel is een klein toegift om te kijken of het attribuut niet NULL vertegenwoordigd. Zal de komende periode kijken of ik nog meer van dit soort leuke PostgreSQL dingetjes kan posten.

AWL vervuiling opschonen

SpamAssassin heeft de optie om te leren en te scoren op basis van een combinatie van e-mail en IP-adres. Nu lijkt deze optie zinvol en het lijkt te werken, maar hoever het schaalt is nog de vraag. Wat het schalen gaat beïnvloeden is de hoeveelheid combinaties die in de database staan en hoe snel deze combinaties te doorzoeken zijn. Helaas is er geen standaardoplossing in SpamAssassin om de AWL-tabel op te schonen, maar gelukkig zijn er opties binnen PostgreSQL om dit te regelen.

De eerste stap is om de AWL-tabel aan te passen door een attribuut toe te voegen met het volgende SQL-commando:

alter table awl add lastupdate timestamp with time zone default now();

De tweede stap is om een trigger te definiëren en aan de tabel te koppelen met het volgende SQL-commando:

CREATE OR REPLACE FUNCTION trg_handle_awl_lastupdate() RETURNS TRIGGER AS $BODY$
BEGIN
IF NEW.lastupdate = OLD.lastupdate THEN NEW.lastupdate := now(); END IF;
RETURN NEW;
END;
$BODY$ LANGUAGE 'plpgsql';
CREATE TRIGGER trg_handle_timestamp BEFORE UPDATE ON awl FOR EACH ROW EXECUTE PROCEDURE trg_handle_awl_lastupdate();

Vanaf dit moment zal het attribuut lastupdate elke keer worden bijgewerkt wanneer de combinatie door SpamAssassin wordt gezien en daardoor ook de tabel bijwerkt. Door nu wekelijks of dagelijks een SQL-script te draaien die bijvoorbeeld elke combinatie die te lang onaangeraakt is te verwijderen. Zoals de voorbeeld code hieronder.

delete from awl
where ( lastupdate < = now() - interval '4 months' and count > 1 )
or ( lastupdate < = now() - interval '3 months' and count = 1 );

Belangrijk om mee te nemen dat het soms even kan duren voordat bepaalde combinaties weer worden gezien. Veel mailinglisten komen meestal wel eens per maand voor. De interval van 3 maanden zou deze lijsten dus voldoende tijd moeten geven om een score te vormen.