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
typedatabaseip_addressmaskauth_typeauth_argument local all trust host all127.0.0.1255.255.255.255trust hosttemplate1
192.168.93.0255.255.255.0identsameuserhosttemplate1 192.168.12.10255.255.255.255md5 hostall 192.168.54.1255.255.255.255reject
hostall 0.0.0.0 0.0.0.0krb5 localsameuser md5 local all md5admins
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…
Jay Fougere is the IT manager for the murdok network. He also writes occasional articles. If you have any IT questions, please direct them to Jay@https://murdok.org.
