 |
|
 |
|
|
|
|
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.
|
|
|
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
PostgreSQL HOST-BASED ACCESS (HBA) CONTROL FILE
| type |
database |
ip_address |
mask |
auth_type |
auth_argument |
| local |
all |
|
|
trust |
|
| host |
all |
127.0.0.1 |
255.255.255.255 |
trust |
|
| host |
template1 |
192.168.93.0 |
255.255.255.0 |
ident |
sameuser |
| host |
template1 |
192.168.12.10 |
255.255.255.255 |
md5 |
|
| host |
all |
192.168.54.1 |
255.255.255.255 |
reject |
|
| host |
all |
0.0.0.0 |
0.0.0.0 |
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
PostgreSQL IDENT-BASED AUTHENTICATION MAPS
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 63.170.212.30 255.255.255.0 ident mymap
That says, any user connecting from a machine with IP
address 63.170.212.30 and subnet mask 255.255.255.0 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
Get
Free Email Newsletters
|
|
|
|
|
