If you require authentication on certain directories using the Lighttpd web server, you can use Lighttpd's mod_auth module. It allows you to protect any directory in web server with access restrictions (just like Apache's password protected directory) .
Lighttpd supports both basic and digest authentication methods. Now consider following sample setup:
- Domain name: theos.in
- Directory (DocRoot) to protect with a password: /home/lighttpd/theos.in/http/docs
- Username: vivek
- Lighttpd password file: /home/lighttpd/.lighttpdpassword (this file should be outside default http document root)
How do I use Basic authentication method?
Easy to implement and password stored in cleartext format using files. If you are going to use this method make sure you use SSL (Secure Socket Layer) connection/encryption.
Step #1: Open /etc/lighttpd/lighttpd.conf file
Make sure mod_auth is loaded:
server.modules += ( "mod_auth" )Now add following three directives:
auth.debug = 2
auth.backend = "plain"
auth.backend.plain.userfile = "/home/lighttpd/.lighttpdpassword"
Where,
- auth.debug = 2 : Specify debug level (0 turns off debug message, 1 for authentication ok message and 2 for detailed/verbose debugging message). This is useful for troubleshooting authentication problem. It logs message in access.log and error.log files
- auth.backend = "plain" : You are using plain text backend (other options are ldap, htpasswd and others)
- auth.backend.plain.userfile = "/home/lighttpd/.lighttpdpassword" : Filename of the username:password storage
Next, you need specify which directory you want to password protect. For example, consider directory /home/lighttpd/theos.in/http/docs directory. Find out your domains virtual hosting section (theos.in) and append following text:
auth.require = ( "/docs/" =>
(
"method" => "basic",
"realm" => "Password protected area",
"require" => "user=vivek"
)
)
Where,
- auth.require = ( "/docs/" => : Directory name
- "method" => "basic", : Authentication type
- "realm" => "Password protected area", : Password realm/message
- "require" => "user=vivek" : Only user vivek can use /docs/
At the end, your configuration should read as follows:
$HTTP["host"] == "theos.in" {
server.document-root = "/home/lighttpd/theos.in/http"
server.errorlog = "/var/log/lighttpd/theos.in/error.log"
accesslog.filename = "/var/log/lighttpd/theos.in/access.log"
auth.require = ( "/docs/" =>
(
"method" => "basic",
"realm" => "Password protected area",
"require" => "user=vivek"
)
)
}
Save and close the file.
Step # 2: Create a password file
Create a plain text username (vivek) and password file:
# vi /home/lighttpd/.lighttpdpasswordAppend username:password:
vivek:mysecretepasswordWhere,
- vivek - is the name of a user. Please note that do not use a system user stored in /etc/passwd file. It is recommended that you use a different username that only exists for the purpose of authenticating password protected directories.
- mysecretepassword - is the password for user vivek (must be in clear text format for plain text method)
Save and close the file. Make sure file /home/lighttpd/.lighttpdpassword is readable by lighttpd:
# chown lighttpd:lighttpd /home/lighttpd/.lighttpdpasswordFinally, restart lighttpd server:
# /etc/init.d/lighttpd restartStep # 3: Test your configuration
Fire your browser and point a web browser to http://yourdomain.com/docs/ or http://localhost/docs/ or http://ip-address/docs. You should be prompted for a username and password.
lunedì 7 dicembre 2009
Lighttpd setup a password protected directory (directories)
Lighttpd - Docs:ModAuth - lighty labs
Docs »
Module mod_auth - Using Authentication¶
- Module mod_auth - Using Authentication
- Supported Methods
- basic
- digest
- Backends
- plain
- htpasswd
- htdigest
- ldap
- Configuration template
- Limitations
- See Also
Supported Methods¶
lighttpd supports both authentication methods described by
RFC 2617:basic¶
The Basic method transfers the username and the password in
cleartext over the network (base64 encoded) and might result
in security problems if not used in conjunction with a crypted
channel between client and server.digest¶
The Digest method only transfers a hashed value over the
network which performs a lot of work to harden the
authentication process in insecure networks.Backends¶
Depending on the method lighttpd provides various way to store
For basic auth:
the credentials used for the authentication.For digest auth:
- plain_
- htpasswd_
- htdigest_
- ldap_
- plain_
- htdigest_
plain¶
A file which contains username and the cleartext password
seperated by a colon. Each entry is terminated by a single
newline.e.g.:
agent007:secrethtpasswd¶
A file which contains username and the crypt()'ed password
seperated by a colon. Each entry is terminated by a single
newline.e.g.:
agent007:XWY5JwrAVBXsQYou can use htpasswd from the apache distribution to manage
those files.$ htpasswd lighttpd.user.htpasswd agent007Keep in mind that not all versions of htpasswd default to use
Apache's modified MD5 algorithm for passwords, which is
required by lighttpd. You can force most to use MD5 with:$ htpasswd -m <pwfile> <username>htdigest¶
A file which contains username, realm and the md5()'ed
password seperated by a colon. Each entry is terminated
by a single newline.e.g.:
agent007:download area:8364d0044ef57b3defcfa141e8f77b65You can use htdigest from the apache distribution to manage
those files.$ htdigest lighttpd.user.htdigest 'download area' agent007Using md5sum can also generate the password-hash:
#!/bin/sh user=$1 realm=$2 pass=$3 hash=`echo -n "$user:$realm:$pass" | md5sum | cut -b -32` echo "$user:$realm:$hash"To use it (spaces between arguments!):
$ htdigest.sh 'agent007' 'download area' 'secret' agent007:download area:8364d0044ef57b3defcfa141e8f77b65follow code is improved when you use for service:
#!/bin/sh export PATH="/bin:/usr/bin:/usr/sbin:$PATH" # when input ctrl-c, remove lockfile and exit trap '[ $lockstart -eq 1 ] && unlock $pfile && exit 0 || exit 0' INT pfile="/etc/lighttpd/conf.d/lighttpd.user" lockstart=0 remove=0 errmsg() { echo "$1" > /dev/stderr } user_check() { check_user=$1 grep "^${check_user}:" ${pfile} >& /dev/null return $? } lock() { lockfile="$1" lockfile="${lockfile}.lock" [ -f "${lockfile}" ] && { errmsg "WARNING: lock file ${lockfile} is already exists" errmsg " Wait minites for end of previous working ..." } while [ -f "${lockfile}" ]; do echo >& /dev/null ; done touch ${lockfile} lockstart=1 } unlock() { lockfile="$1" lockfile="${lockfile}.lock" [ -f "${lockfile}" ] && rm -f ${lockfile} && lockstart=0 } usage() { errmsg errmsg "lightdigest: lighttpd htdigest password generation program" errmsg "Scripted by JoungKyun.Kim <http://oops.org>" errmsg errmsg "Usage: $0 -[hd] -u user -p pass -r realm [-f password_file]" errmsg "Options:" errmsg " -h print this help messages" errmsg " -u user username" errmsg " -p pass password" errmsg " -r realm realm name" errmsg " -f filename password file [default: /etc/lighttpd/conf.d/lighttpd.user" errmsg " -d remove user" errmsg [ $lockstart -eq 1 ] && rm -f ${pfile}.lock exit 1 } opts=$(getopt df:hp:r:u: $*) [ $? != 0 ] && usage set -- ${opts} for i do case "$i" in -d) remove=1; shift;; -f) pfile="$2"; shift; shift;; -p) pass="$2"; shift; shift;; -r) realm="$2"; shift; shift;; -u) user="$2"; shift; shift;; --) shift; break; esac done #echo $user #echo $realm #echo $pass #echo $pfile #echo $remove [ -z "$user" ] && errmsg "ERROR: User is none!!" && usage [ ${remove} -eq 0 -a -z "${realm}" ] && errmsg "ERROR: Realm is none!!" && usage if [ -z "${pass}" -a ${remove} -eq 0 ]; then echo -n "Input new password : " read newpass echo -n "Reinput password for confirm : " read renewpass if [ "${newpass}" != "${renewpass}" ]; then errmsg "ERROR: Password is not match" exit 1 fi pass=${newpass} fi lock ${pfile} if [ ${remove} -eq 0 ]; then # User Add Mode hash=$(echo -n "${user}:${realm}:${pass}" | md5sum | cut -b -32) user_check ${user} already=$? [ -f "${pfile}" ] && cp -af ${pfile} ${pfile}.bak if [ ${already} -eq 0 ]; then # already exists perl -pi -e "s/^${user}:.*$/${user}:${realm}:${hash}/g" ${pfile} else # add new user echo "${user}:${realm}:${hash}" >> ${pfile} fi else # User Remove Mode tmp_htdigest="/tmp/lighttpd-htdiges.tmp.$$" cp -af ${pfile} ${pfile}.bak grep -v "^${user}:" ${pfile} > ${tmp_htdigest} mv -f ${tmp_htdigest} ${pfile} fi unlock ${pfile} exit 0To use it (don't use realm value! getopt of some bash version has bug.) :
# if you add or change $ lightdigest -u USERNAME -r REALM_NAME -f PASSWORD_FILE_PATH # if you want to remove use $ lightdigest -d -u USERNAMEldap¶
The ldap backend is performing the following steps to authenticate a user
- Init the LDAP connection
- Set Protocol version to LDAPv3
- If StartTLS if configured -> Configure CA certificate if supplied
- If StartTLS if configured -> Activate TLS using StartTLS
- If Bind DN is included -> Simple bind with Bind-DN and Bind-Password
- If there is no Bind-DN -> Simple bind anonymously
- Try up to two times a SUBTREE search of the base-DN with the filter applied.
- Retrieve the DN of the user matching the filter.
- Finally, re-init the connection (following the steps above), this time using the DN found using the filter and the password supplied by the user.
If all 9 steps are performed without any error the user is authenticated.
Configuration template¶
After setup the backend, edit the authentication configuration file to reflect your backend selected. The following is a configuration template.
## debugging # 0 for off, 1 for 'auth-ok' messages, 2 for verbose debugging auth.debug = 0 ## type of backend # plain, htpasswd, ldap or htdigest auth.backend = "htpasswd" # filename of the password storage for plain auth.backend.plain.userfile = "lighttpd-plain.user" ## for htpasswd auth.backend.htpasswd.userfile = "/full/path/to/lighttpd-htpasswd.user" ## for htdigest auth.backend.htdigest.userfile = "lighttpd-htdigest.user" ## for ldap # the $ in auth.backend.ldap.filter is replaced by the # 'username' from the login dialog auth.backend.ldap.hostname = "localhost" auth.backend.ldap.base-dn = "dc=my-domain,dc=com" auth.backend.ldap.filter = "(uid=$)" # if enabled, startTLS needs a valid (base64-encoded) CA # certificate unless the certificate has been stored # in a c_hashed directory and referenced in ldap.conf auth.backend.ldap.starttls = "enable" auth.backend.ldap.ca-file = "/etc/CAcertificate.pem" # If you need to use a custom bind to access the server auth.backend.ldap.bind-dn = "uid=admin,dc=my-domain,dc=com" auth.backend.ldap.bind-pw = "mysecret" # If you want to allow empty passwords # "disable" for requiring passwords, "enable" for allowing empty passwords auth.backend.ldap.allow-empty-pw = "disable" ## restrictions # set restrictions: # # ( <left-part-of-the-url> => # ( "method" => "digest"/"basic", # "realm" => <realm>, # "require" => "user=<username>" ) # ) # # <realm> is a string to display in the dialog # presented to the user and is also used for the # digest-algorithm and has to match the realm in the # htdigest file (if used) # auth.require = ( "/download/" => ( # method must be either basic or digest "method" => "digest", "realm" => "download archiv", "require" => "user=agent007|user=agent008" ), "/server-info" => ( # limit access to server information "method" => "digest", "realm" => "download archiv", "require" => "valid-user" ) "/protected-folder/" => ( # "method" => "digest", "realm" => "download archiv", "require" => "valid-user" ) ) # Or, using regular expressions: $HTTP["url"] =~ "^/download|^/server-info" { auth.require = ( "" => ( "method" => "digest", "realm" => "download archiv", "require" => "user=agent007|user=agent008" ) ) }When completed, enable the authentication configuration module. The way to enable the module varies from different distributions.
Limitations¶
- The implementation of digest method is currently not completely compliant with the standard as it still allows a replay attack. (i.e. not secure)
- LDAP authentication only allows alphanumeric uid's that do not contain punctuations. i.e.) john.doe will come up as "ldap: invalid character (a-zA-Z0-9 allowed) in username: john.doe"
- There seems to be no reasonable logging of failed login attempts yet
- As of 1.4.19 the group field inside the require directive is not yet implemented. So auth.backend.plain.groupfile is of no use at this moment.
See Also¶
htdigest.sh.txt - htdigest.sh from this article (it's easier to grab that way) (2,919 KB) ckujau, Sab 28 Mar 2009, 04:51:01 +0000
Sembra un pò più complessa di quella di apache..
Authentication, Authorization and Access Control - Apache HTTP Server
Getting it working
Here's the basics of password protecting a directory on your server.
You'll need to create a password file. This file should be placed somewhere not accessible from the web. This is so that folks cannot download the password file. For example, if your documents are served out of
/usr/local/apache/htdocsyou might want to put the password file(s) in/usr/local/apache/passwd.To create the file, use the
htpasswdutility that came with Apache. This will be located in thebindirectory of wherever you installed Apache. To create the file, type:
htpasswd -c /usr/local/apache/passwd/passwords rbowen
htpasswdwill ask you for the password, and then ask you to type it again to confirm it:
# htpasswd -c /usr/local/apache/passwd/passwords rbowen
New password: mypassword
Re-type new password: mypassword
Adding password for user rbowenIf
htpasswdis not in your path, of course you'll have to type the full path to the file to get it to run. On my server, it's located at/usr/local/apache/bin/htpasswdNext, you'll need to configure the server to request a password and tell the server which users are allowed access. You can do this either by editing the
httpd.conffile or using an.htaccessfile. For example, if you wish to protect the directory/usr/local/apache/htdocs/secret, you can use the following directives, either placed in the file/usr/local/apache/htdocs/secret/.htaccess, or placed inhttpd.confinside a <Directory /usr/local/apache/apache/htdocs/secret> section.
AuthType Basic
AuthName "Restricted Files"
AuthUserFile /usr/local/apache/passwd/passwords
Require user rbowenLet's examine each of those directives individually. The
AuthTypedirective selects that method that is used to authenticate the user. The most common method isBasic, and this is the method implemented bymod_auth. It is important to be aware, however, that Basic authentication sends the password from the client to the browser unencrypted. This method should therefore not be used for highly sensitive data. Apache supports one other authentication method:AuthType Digest. This method is implemented bymod_auth_digestand is much more secure. Only the most recent versions of clients are known to support Digest authentication.The
AuthNamedirective sets the Realm to be used in the authentication. The realm serves two major functions. First, the client often presents this information to the user as part of the password dialog box. Second, it is used by the client to determine what password to send for a given authenticated area.So, for example, once a client has authenticated in the
"Restricted Files"area, it will automatically retry the same password for any area on the same server that is marked with the"Restricted Files"Realm. Therefore, you can prevent a user from being prompted more than once for a password by letting multiple restricted areas share the same realm. Of course, for security reasons, the client will always need to ask again for the password whenever the hostname of the server changes.The
AuthUserFiledirective sets the path to the password file that we just created withhtpasswd. If you have a large number of users, it can be quite slow to search through a plain text file to authenticate the user on each request. Apache also has the ability to store user information in fast database files. Themod_auth_dbmmodule provides theAuthDBMUserFiledirective. These files can be created and manipulated with thedbmmanageprogram. Many other types of authentication options are available from third party modules in the Apache Modules Database.Finally, the
Requiredirective provides the authorization part of the process by setting the user that is allowed to access this region of the server. In the next section, we discuss various ways to use theRequiredirective.
mercoledì 2 dicembre 2009
Enabling / Disabling apace modules in command line on debian/ubuntu distribution
a2enmod <nomeModuloDaAttivare>
Digitandolo senza argomenti, restituisce l'elenco dei moduli attivabili:
/# a2enmod
Your choices are: actions alias asis auth_basic auth_digest authn_alias authn_anon authn_dbd authn_dbm authn_default authn_file authnz_ldap authz_dbm authz_default authz_groupfile authz_host authz_owner authz_user autoindex cache cern_meta cgi cgid charset_lite dav dav_fs dav_lock dbd deflate dir disk_cache dump_io env expires ext_filter fcgid file_cache filter headers ident imagemap include info ldap log_forensic mem_cache mime mime_magic negotiation perl php5 proxy proxy_ajp proxy_balancer proxy_connect proxy_ftp proxy_http rewrite setenvif speling ssl status substitute unique_id userdir usertrack version vhost_alias
Which module(s) do you want to enable (wildcards ok)?
Per disattivare moduli, invece, si usa
a2dismod
che funziona esattamente come a2enmod.Dopo aver fatto le modifiche, ricordarsi di riavviare apache
/# /etc/init.d/apache2 restart
giovedì 26 novembre 2009
Running lighttpd under apache2
LoadModule proxy_module modules/mod_proxy.so
LoadModule proxy_connect_module modules/mod_proxy_connect.so
LoadModule proxy_http_module modules/mod_proxy_http.so
LoadModule proxy_ftp_module modules/mod_proxy_ftp.so ProxyRequests Off
ProxyPreserveHost On
proxypass /byl http://localhost:81/byl
proxypassReverse /byl http://localhost:81/byl<Proxy http://localhost:81/>
Order Allow,Deny
Allow from all
</Proxy>
giovedì 19 novembre 2009
Semplici test OOP
##file: lib-doc.php
<?php
class Document{
private $code;
private $date;
private $costumer;
private $total;
private $vat;
private $imp;
private $i;
public $items;
public $template;
public function __construct($code, $date, $costumer){
$this->i = 0;
$this->template = 'document.tpl.php';
$this->code = $code;
$this->date = $date;
$this->costumer = $costumer;
$this->items = array();
}
public function addItem($title, $code, $qnt, $price, $nVat){
$this->i++;
$item = new Item($code, $title, $qnt, $price, $nVat, $this->i, $this);
$this->items[$this->i] = $item;
$this->totaliDocumento();
return $item;
}
public function removeItem($item){
if(isset($this->items[$item->getKey()])){
unset($this->items[$item->getKey()]);
$this->totaliDocumento();
}
}
public function changeQnt($item, $newQnt){
if(isset($this->items[$item->getKey()])){
$item->changeQnt($newQnt);
$this->totaliDocumento();
}
}
public function totaliDocumento(){
$this->total = 0;
$this->vat = 0;
$this->imp = 0;
foreach($this->items AS $item){
$this->total += $item->getImporto('totale');
$this->imp += $item->getImporto('imponibile');
$this->vat += $item->getImporto('iva');
}
}
public function attr($attr){
return $this->$attr;
}
public function output(){
$document = $this;
include($this->template);
}
public function outputItem($item){
include($item->template);
}
}
class Item Document{
private $qnt;
private $prezzo;
private $cIva;
public $titolo;
public $codice;
public $template;
/*
* rayKey e' la chiave dell'array items
* dell'istanza di ogni Item.
* Serve per tenerne la tracciabilita'
* (per rimuovere gli oggetti o modificarli)
*/
private $rayKey;
private $documento;
private $totale;
private $imponibile;
private $iva;
public function __construct($codice, $titolo, $qnt, $prezzo, $cIva, $key, $documento){
$this->template = 'item.tpl.php';
$this->documento = $documento;
$this->rayKey = $key;
$this->codice = $codice;
$this->titolo = $titolo;
$this->qnt = $qnt;
$this->prezzo = $prezzo;
$this->cIva = $cIva;
//calcolo i totali
$this->calcolaTotali();
}
public function getKey(){
return $this->rayKey;
}
/*
*Diciamo che voglio avere la certezza che
*aggiornando la quantita' o il prezzo
*dell'oggetto, i totali sono sempre aggiornati.
*Quindi qnt e prezzo sono privati, per aggiornarli
*bisogna utilizzare la relativa funzione che
*aggiorna anche i totali dell'oggetto.
*/
public function aggiornaQnt($qnt){
$this->qnt = $qnt;
$this->calcolaTotali();
}
public function aggiornaPrezzo($prezzo){
$this->prezzo = $prezzo;
$this->calcolaTotali();
}
private function calcolaTotali(){
$this->imponibile = $this->qnt * $this->prezzo;
$this->iva = ($this->imponibile * $this->cIva) / 100;
$this->totale = $this->imponibile + $this->iva;
$this->documento->totaliDocumento();
}
/*
* Anche i totali non sono modificabili
* direttamente ma dipendono da altre
* proprieta' dell'oggetto.
* L'unico modo per ottenerli al di fuori
* della classe, e' quindi questa funzione
*/
public function getImporto($importo = 'totale'){
switch($importo){
case 'totale':
return $this->totale;
break;
case 'imponibile':
return $this->imponibile;
break;
case 'iva':
return $this->iva;
break;
}
}
/*
* questo metodo serve solo per recuperare dall'esterno
* le variabli protette
*/
public function attr($attr){
return $this->$attr;
}
public function output(){
$item = $this;
include($this->template);
}
}
?>##file: index.php
<?php
require_once('lib-doc.php');$fattura = new Document('00001', '11 November 2009', 'Jhon Doe');$beer = $fattura->addItem('Beer Bottle', 'F6ASJK8', 6, 3.43, 20);
$chips = $fattura->addItem('Cheesy Chips', 'KBLN32', 2, 1.12, 20);
$popcorn = $fattura->addItem('Pop Corn', 'BCHREUD6', 2, 0.69, 20);
$fattura->removeItem($beer);
$beer_cans = $fattura->addItem('Beer Cans, x6', 'YHEF6DTT', 1, 5.99, 20);
//$fattura->changeQnt($chips, 4);$fattura->output();
?>##file: document.tpl.php
<div class="document document-<?php echo $document->attr('code'); ?>">
<h1>Document <?php echo $document->attr('code'); ?> of <?php echo $document->attr('date'); ?></h1>
<p>Costumer: <b><?php echo $document->attr('costumer'); ?></b></p>
<h2>Items Details</h2>
<p class="item-list">
<?php
foreach($document->items AS $item){
$document->outputItem($item);
}
?>
</p>
<h2>Document Totals:</h2>
<ul class="totals">
<li><b>Imponibile:</b> <?php echo $document->attr('imp'); ?>€</li>
<li><b>VAT:</b> <?php echo $document->attr('vat'); ?>€</li>
<li><b>Total:</b> <?php echo $document->attr('total'); ?>€</li>
</ul>
</div>##file: item.tpl.php
<?php
//questo DEVE dare ERRORE!
$item->aggiornaPrezzo(10.54);
?>
<div class="item item-<?php echo $item->codice; ?>">
<h3><?php echo $item->titolo; ?></h3>
<ul>
<li><b>Quantity:</b> <?php echo $item->attr('qnt'); ?></li>
<li><b>Cost per Unit:</b> <?php echo $item->attr('prezzo'); ?></li>
<li><b>VAT Appliable:</b> <?php echo $item->attr('cIva'); ?>%</li>
<li><b>Imponibile:</b> <?php echo $item->getImporto('imponibile'); ?>€</li>
<li><b>VAT:</b> <?php echo $item->getImporto('iva'); ?>€</li>
<li><b>Total:</b> <?php echo $item->getImporto('totale'); ?>€</li>
</ul>
</div>
giovedì 12 novembre 2009
Reminder: postgresql 8.4.1 Conditional Expressions
9.16.1. CASE
The SQL CASE expression is a generic conditional expression, similar to if/else statements in other programming languages:
CASE WHEN condition THEN result
[WHEN ...]
[ELSE result]
END
CASE clauses can be used wherever an expression is valid. Each condition is an expression that returns a boolean result. If the condition's result is true, the value of the CASE expression is the result that follows the condition, and the remainder of the CASE expression is not processed. If the condition's result is not true, any subsequent WHEN clauses are examined in the same manner. If no WHEN condition yields true, the value of the CASE expression is the result of the ELSE clause. If the ELSE clause is omitted and no condition is true, the result is null.
An example:
SELECT * FROM test; a
---
1
2
3
SELECT a,
CASE WHEN a=1 THEN 'one'
WHEN a=2 THEN 'two'
ELSE 'other'
END
FROM test; a | case
---+-------
1 | one
2 | two
3 | other
The data types of all the result expressions must be convertible to a single output type. See Section 10.5 for more details.
There is a "simple" form of CASE expression that is a variant of the general form above:
CASE expression
WHEN value THEN result
[WHEN ...]
[ELSE result]
END
The first expression is computed, then compared to each of the value expressions in the WHEN clauses until one is found that is equal to it. If no match is found, the result of the ELSE clause (or a null value) is returned. This is similar to the switch statement in C.
The example above can be written using the simple CASE syntax:
SELECT a,
CASE a WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'other'
END
FROM test; a | case
---+-------
1 | one
2 | two
3 | other
A CASE expression does not evaluate any subexpressions that are not needed to determine the result. For example, this is a possible way of avoiding a division-by-zero failure:
SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;
9.16.2. COALESCE
COALESCE(value [, ...])
The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display, for example:
SELECT COALESCE(description, short_description, '(none)') ...
Like a CASE expression, COALESCE only evaluates the arguments that are needed to determine the result; that is, arguments to the right of the first non-null argument are not evaluated. This SQL-standard function provides capabilities similar to NVL and IFNULL, which are used in some other database systems.
9.16.3. NULLIF
NULLIF(value1, value2)
The NULLIF function returns a null value if value1 equals value2; otherwise it returns value1. This can be used to perform the inverse operation of the COALESCE example given above:
SELECT NULLIF(value, '(none)') ...
If value1 is (none), return a null, otherwise return value1.
9.16.4. GREATEST and LEAST
GREATEST(value [, ...])LEAST(value [, ...])
The GREATEST and LEAST functions select the largest or smallest value from a list of any number of expressions. The expressions must all be convertible to a common data type, which will be the type of the result (see Section 10.5 for details). NULL values in the list are ignored. The result will be NULL only if all the expressions evaluate to NULL.
Note that GREATEST and LEAST are not in the SQL standard, but are a common extension. Some other databases make them return NULL if any argument is NULL, rather than only when all are NULL.
Source: http://www.postgresql.org/docs/8.4/interactive/functions-conditional.html
