Book Image

PostgreSQL 10 Administration Cookbook - Fourth Edition

By : Simon Riggs, Gianni Ciolli
Book Image

PostgreSQL 10 Administration Cookbook - Fourth Edition

By: Simon Riggs, Gianni Ciolli

Overview of this book

PostgreSQL is a powerful, open source database management system with an enviable reputation for high performance and stability. With many new features in its arsenal, PostgreSQL 10 allows users to scale up their PostgreSQL infrastructure. This book takes a step-by-step, recipe-based approach to effective PostgreSQL administration. Throughout this book, you will be introduced to these new features such as logical replication, native table partitioning, additional query parallelism, and much more. You will learn how to tackle a variety of problems that are basically the pain points for any database administrator - from creating tables to managing views, from improving performance to securing your database. More importantly, the book pays special attention to topics such as monitoring roles, backup, and recovery of your PostgreSQL 10 database, ensuring high availability, concurrency, and replication. By the end of this book, you will know everything you need to know to be the go-to PostgreSQL expert in your organization.
Table of Contents (14 chapters)

Avoiding hardcoding your password

We all agree that hardcoding your password is a bad idea. This recipe shows you how to keep your password in a secure password file.

Getting ready

Not all database users need passwords; some databases use other means of authentication. Don't perform this step unless you know you will be using password authentication, and you know your password.

First, remove the hardcoded password from where you set it previously. Completely remove the password = xxxx text from the connection string in a program. Otherwise, when you test the password file, the hardcoded setting will override the details you are about to place in the file. Keeping the password hardcoded and in the password file is not any better. Using PGPASSWORD is not recommended either, so remove that also.

If you think someone may have seen the password, then change your password before placing it in the secure password file.

How to do it...

A password file contains the usual five fields that we require when connecting, as shown here:


Change this to the following:


The password file is located using an environment variable named PGPASSFILE. If PGPASSFILE is not set, then a default filename and location must be searched for, as follows:

  • On *nix systems, look for ~/.pgpass
  • On Windows systems, look for %APPDATA%\postgresql\pgpass.conf, where %APPDATA% is the application data subdirectory in the path (for me, that would be C:\)
Don't forget to set the file permissions on the file, so that security is maintained. File permissions are not enforced on Windows, though the default location is secure. On *nix systems, you must issue the following: chmod 0600 ~/.pgpass.
If you forget to do this, the PostgreSQL client will ignore the .pgpass file. While the psql tool will issue a clear warning, many other clients will just fail silently, so don't forget!

How it works...

Many people name the password file .pgpass, whether or not they are on Windows, so don't get confused if they do this.

The password file can contain multiple lines. Each line is matched against the requested host:port:dbname:user combination until we find a line that matches. Then, we use that password.

Each item can be a literal value or *, a wildcard that matches anything. There is no support for partial matching. With appropriate permissions, a user can potentially connect to any database. Using the wildcard in the dbname and port fields makes sense, but it is less useful in other fields. Here are a few examples:

  • myhost:5432:*:sriggs:moresecurepw
  • myhost:5432:perf:hannu:okpw
  • myhost:*:perf:gianni:sicurissimo

There's more...

This looks like a good improvement if you have a small number of database servers. If you have many different database servers, you may want to think about using a connection service file instead (see the next recipe), or perhaps even storing details on a Lightweight Directory Access Protocol (LDAP) server.