Authenticating PostgreSQL Clients
Give Us Your Feedback

Editor's Note - 05.21.02

As you may have heard, last week Microsoft released another roll-up patch for Internet Exporer versions 5.0, 5.5, and 6.0. Unfortunately, this patch does not address all of the issues currently plaguing Internet Explorer. Find out more below.

Do you need a bulletproof (figurative, not literal!) web server? A web server that is impervious to attack? How about a web server that 100,000 people have tried to hack, with 0 successes? In fact, the manufacturer, Hydras, is offering a $100,000 USD reward to anyone that can break into one of these servers. Find out more about Hydras below.

Did you know that Gummi Bears or gelatin can be used in a very simple way to bypass 80% of all electronic fingerprint recognition devices? The technique, developed in Korea, can be performed in a standard kitchen using about $10 worth of common household items. Find out more below at CNN below.

For those of you who thought I was serious about a class action lawsuit against manufacturers and distributors of felt-tipped pens, Post-It notes and electrical tape, I was joking (see below) . I was being sarcastic, and I apologize for the confusion.

send comments/feedback

Authenticating PostgreSQL Clients

Hello Readers,

Todayís article is about security (authentication in particular) in PostgreSQL, the most advanced open-source database available anywhere (as its developers claim!!). So what about it? We know that security is a very important concern in the present day IT world. Itís no different with databases. If the connection to a database is not secure, there can be irrevocable damages done to the company or to the owner of the database. And of course, you know the net result Ė a substantial loss of money and the loss of creditability with the clients (More so, if the company happens to be a bank or a credit card company). So, security is one of the top most priorities even in the database world.

Letís discuss some of the ways in which we can authenticate users to ensure secure transactions in PostgreSQL. In this article, I will talk about client authentication. Encryption and Access control lists are some of the other key security issues. If you havenít heard these terms so far, donít panic. Youíll learn them in a short while.

Client authentication is one of the key features of PostgreSQL. With out it, either we sacrifice remote connectivity to the databases or allow just about anybody access to our most important data. PostgreSQL supports several types of authentication. Itís up to the database administrator to choose one of them.

Host-based client access is specified in the configuration file pg_hba.conf. Itís generally located in the PostgreSQL data directory (/usr/local/pgql/data/-- Generally the environment variable $PGDATA is set to this path). And itís installed automatically when you install PostgreSQL with the initdb command. I donít discuss initdb in this article. You may consult the man pages for more information on initdb.

The pg_hba.conf file controls: 1) Which hosts are allowed to connect 2) How users are authenticated on each host. 3) Databases accessible by each host

Iíll discuss each of these in just a minute. The configuration file is read on postmaster startup and when the postmaster receives a SIGHUP. Postmaster is generally the server in a PostgreSQL database system.

Sample pg_hba.conf file on a Linux box


type database ip_address mask auth_type auth_argument
local all     trust  
host all trust  
host template1 ident sameuser
host template1 md5  
host all reject  
host all krb5  
local sameuser     md5  
local all     md5 admins

Let me discuss what each of these entries means.

The type field tells us what the type of the connection is. There can be three different kinds of connections. They are: a) host b) hostssl and c) local

Records with the type field set to "host" indicate what different networked hosts can connect to the database. A record with "hostssl" type is similar, but adds the additional information, that the connection is over a secure socket layer (SSL). A "local" type tells that the connection is from the local host via a UNIX domain socket.

The second field is the database name. It can be one of the following: a) the name of a PostgreSQL database b) "all" to indicate all databases c) "sameuser" to allow access only to databases with the same name as the connecting user

The third and fourth fields are the IP address and the subnet mask of the host from which the connection is sought.

The fifth field is what is the most important to us. It is the authentication type field. As I said earlier, PostgreSQL supports different types of authentication.

Some of them are: a) Crypt b) Md5 c) Kerberos d) PAM e) Ident f) Password authentication and g) Trust

Here is the definition of each of the above mentioned authentication types. These definitions are taken from the config file itself.

trust: No authentication is done. Any valid username is accepted, including the PostgreSQL superuser. This option should be used only for hosts where all users are trusted.

password: Authentication is done by matching a password supplied in clear by the host. If no AUTH_ARGUMENT is used, the password is compared with the user's entry in the pg_shadow table.

If AUTH_ARGUMENT is specified, the username is looked up in that file in the $PGDATA directory. If the username is found but there is no password, the password is looked up in pg_shadow. If a password exists in the file, it is used instead. These secondary files allow fine-grained control over who can access which databases and whether a non-default password is required. The same file can be used in multiple records for easier administration. Password files can be maintained with the pg_passwd(1) utility. Remember, these passwords override pg_shadow passwords.

md5: Same as "password", but the password is encrypted while being sent over the network. This method is preferable to "password" except for pre-7.2 clients that don't support it. NOTE: md5 can use usernames stored in secondary password files but ignores passwords stored there. The pg_shadow password will always be used.

crypt: Same as "md5", but uses crypt for pre-7.2 clients. You can not store encrypted passwords in pg_shadow if you use this method.

ident: For TCP/IP connections, authentication is done by contacting the ident server on the client host. Remember, this is only as secure as the client machine. On machines that support Unix-domain socket credentials (currently Linux, FreeBSD, NetBSD, and BSD/OS), this method also works for "local" connections.

AUTH_ARGUMENT is required: it determines how to map remote user names to Postgres user names. The AUTH_ARGUMENT is a map name found in the $PGDATA/pg_ident.conf file. The connection is accepted if that file contains an entry for this map name with the ident-supplied username and the requested Postgres username. The special map name "sameuser" indicates an implied map (not in pg_ident.conf) that maps each ident username to the identical PostgreSQL username.

krb4: Kerberos V4 authentication is used. Allowed only for TCP/IP connections, not for local UNIX-domain sockets.

krb5: Kerberos V5 authentication is used. Allowed only for TCP/IP connections, not for local UNIX-domain sockets.

pam: Authentication is passed off to PAM (PostgreSQL must be configured --with-pam), using the default service name "postgresql" - you can specify your own service name, by setting AUTH_ARGUMENT to the desired service name. reject: Reject the connection. This is used to reject certain hosts that are part of a network specified later in the file. To be effective, "reject" must appear before the later entries.

Here is a sample pg_ident.conf file taken from the Linux box.

Sample pg_ident.conf file

This file controls ident-based authentication. It maps ident usernames to their corresponding PostgreSQL usernames. Entries are grouped by map name. Each record consists of three fields.

o map name
o ident username
o PostgreSQL username

It is read on postmaster startup and when the postmaster receives a SIGHUP. If you edit the file on a running system, you have to SIGHUP the postmaster for the changes to take effect. For example, the following entry equates user "james" on a remote system to PostgreSQL user "guest" in the map named "phoenix": MAP IDENT PGUSERNAME phoenix james guest "phoenix" can now be used by an "ident" record in $DATA/pg_hba.conf. Multiple maps may be specified in this file and used by pg_hba.conf. Note that it is possible for a remote user to map to multiple PostgreSQL usernames. The PostgreSQL username specified at connection time controls which one is used. If all ident usernames and PostgreSQL usernames are the same, you don't need this file. Instead, use the special map name "sameuser" in pg_hba.conf.

Hope your patience did not run out reading the files above. I am sure you realized how simple it is to make a map in our pg_ident.conf file and use it in the pg_hba.conf file for authentication.

Letís now make a map of our own. MAP IDENT PGUSERNAME mymap mike guest

The above entry maps the user Ďmikeí on the remote machine to the user Ďguestí on the machine running the PostgreSQL server. Now we can use this map to make an entry in the pg_hba.conf file like this:

type database ip_address mask auth_type auth_argument

host all ident mymap

That says, any user connecting from a machine with IP address and subnet mask is to be authenticated by using the map Ďmymapí in pg_ident.conf file. Very simple, isnít it?

That is about all for authentication in PostgreSQL. Hope you enjoyed the article. For more information on authentication, encryption and access control lists, please visit the links provided in the resources section. Have fun readingÖ

News Headlines

Claim Your Domain
Click Here

Get Free Email Newsletters

Get SecurityProNews!

Enter Email

Click For Advertising Info iEntry Sitemap News Headlines Your Subscription Print Version Archives More Newsletters News Headlines Click For Advertising Info

Resource Links:
MS IE patch misses the mark
Hydra blunts hackers' swords
New Threat: The 'Gummy Finger'
Most Common Viruses According To SARC
PostgreSQL Mirror Sites
PostgreSQL Technical Documentation
Authentication and Encryption

-- SecurityProNews is an iEntry, Inc. publication --
© 2002 iEntry, Inc.  All Rights Reserved  Privacy Policy  Legal