Chapter 21. Security

The goal of PostgreSQL security is to keep the bad guys out while letting the good guys in.

Security is a balancing act ”it is often the case that more secure installations are less convenient for authorized users. Finding the right balance depends primarily on two factors. First, "How much do you trust the people that have access to your machine?" The answer to that question is not as obvious at it may seem ”if your system is connected to the Internet, you have to extend your trust to everyone else on the Internet. The second question is "How important is it to keep your data private?" It's probably not very important to keep your personal CD catalog private, but if you are storing customer credit card numbers , you had better put in some extra effort to ensure privacy.

There are three aspects to PostgreSQL security:

• Securing the PostgreSQL data files

• Securing client access

• Granting and denying access to specific tables and specific users

The first aspect is the easiest ”the rules are simple and there aren't very many decisions that you have to make. The host operating system enforces file-level security. I'll explain how to ensure that your PostgreSQL installation has the proper ownerships and permissions in the next section.

Securing client access is relatively simple if you are on a secure network and complex if you are not. The main task in securing client access is authentication. Authentication is proving that you are who you say you are. PostgreSQL supports a variety of authentication, ranging from complete trust (meaning, "Ok, you say your name is bruce , who am I to argue?") to encryption and message digest protocols. I'll describe each authentication method in this chapter.

The first two aspects of PostgreSQL security are concerned with keeping the wrong people out of your database while letting the right people in. The last aspect determines what you can do once you are allowed inside a PostgreSQL database.