Introduction to PostgreSQL 9

Exclusive offer: get 50% off this eBook here
PostgreSQL 9 Admin Cookbook

PostgreSQL 9 Admin Cookbook — Save 50%

Over 80 recipes to help you run an efficient PostgreSQL 9.0 database

$29.99    $15.00
by Simon Riggs | October 2010 | Open Source

In this article by Simon Riggs, author of PostgreSQL 9 Administration Cookbook, we will cover the following:

  • Introducing PostgreSQL 9
  • Getting PostgreSQL
  • Connecting to PostgreSQL
  • Enabling server access to network/remote users
  • Using graphical administration tools
  • Using psql query and scripting tools
  • Changing your password securely
  • Avoiding hardcoding your password
  • Using a connection service file
  • Troubleshooting a failed connection

 

PostgreSQL 9 Admin Cookbook

PostgreSQL 9 Admin Cookbook

Over 80 recipes to help you run an efficient PostgreSQL 9.0 database

  • Administer and maintain a healthy database
  • Monitor your database ensuring that it performs as quickly as possible
  • Tips for backup and recovery of your database
        Read more about this book      

(For more resources on PostgreSQL, see here.)

Introduction

PostgreSQL is a feature-rich general purpose database management system. It's a complex piece of software, but every journey begins with the first step.

We start with your first connection. Many people fall at the first hurdle, so we try not to skip too swiftly past that. We move on quickly to enabling remote users, and from there to access through GUI administration tools.

We also introduce the psql query tool, which is the tool used for loading our sample database.

Introducing PostgreSQL 9

PostgreSQL is an advanced SQL database server, available on a wide range of platforms.

One of the clearest benefits of PostgreSQL is that it is open source, meaning that you have a very permissive license to install, use, and distribute PostgreSQL without paying anyone fees or royalties. On top of that, PostgreSQL is well-known as a database that stays up for long periods, and requires little or no maintenance in many cases. Overall, PostgreSQL provides a very low total cost of ownership.

PostgreSQL is also noted for its huge range of advanced features, developed over the course of more than 20 years continuous development and enhancement. Originally developed by the Database Research group at the University of California, Berkeley, PostgreSQL is now developed and maintained by a huge army of developers and contributors. Many of those contributors have full-time jobs related to PostgreSQL, working as designers, developers, database administrators, and trainers. Some, but not many, of those contributors work for companies that specialize in services for PostgreSQL, such as Hannu and me. No single company owns PostgreSQL, nor are you required, or even encouraged, to register your usage.

PostgreSQL has the following main features:

  • Excellent SQL Standards compliance up to SQL 2008
  • Client-server architecture
  • Highly concurrent design where readers and writers don't block each other
  • Highly configurable and extensible for many types of application
  • Excellent scalability and performance with extensive tuning features

What makes PostgreSQL different?

The PostgreSQL project focuses on the following objectives:

  • Robust, high-quality software with maintainable, well-commented code
  • Low maintenance administration for both embedded and enterprise use
  • Standards-compliant SQL, interoperability, and compatibility
  • Performance, security, and high availability

What surprises many people is that PostgreSQL's feature set is more comparable with Oracle or SQL Server than it is with MySQL. The only connection between MySQL and PostgreSQL is that those two projects are open source; apart from that, the features and philosophies are almost totally different.

One of the key features of Oracle since Oracle 7 has been "snapshot isolation", where readers don't block writers, and writers don't block readers. You may be surprised to learn that PostgreSQL was the first database to be designed with this feature, and offers a full and complete implementation. PostgreSQL names this Multi-Version Concurrency Control (MVCC).

PostgreSQL is a general-purpose database management system. You defi ne the database that you would like to manage with it. PostgreSQL offers you many ways to work. You can use a "normalized database model", you can utilize extensions such as arrays and record subtypes, or you can use a fully dynamic schema using an extension named hstore . PostgreSQL also allows you to create your own server-side functions in one of a dozen different languages.

PostgreSQL is highly extensible, so you can add your own datatypes, operators, index types, and functional languages. For example, you can override different parts of the system using plugins to alter the execution of commands or add a new optimizer.

All of these features offer a huge range of implementation options to software architects. There are many ways out of trouble when building applications and maintaining them over long periods of time.

In the early days, when PostgreSQL was still a research database, the focus was solely on cool new features. Over the last 15 years, enormous amounts of code have been rewritten and improved, giving us one of the most stable, large, software servers available for operational use.

You may also read that PostgreSQL was, or is, slower than My Favorite DBMS, whichever one that is. It's been a personal mission of mine over the last six years to improve server performance and the team have been successful in making the server highly performant and very scalable. That gives PostgreSQL enormous headroom for growth.

Who is using PostgreSQL? Prominent users include Apple, BASF, Genentech, IMDB.com, Skype, NTT, Yahoo, and The National Weather Service. PostgreSQL receives well in excess of 1 million downloads per year, according to data submitted to the European Commission, who concluded "...PostgreSQL, is considered by many database users to be a credible alternative...

We need to mention one last thing. When PostgreSQL was fi rst developed, it was named Postgres, and so many aspects of the project still refer to the word "postgres". For example, the default database is named postgres, and the software is frequently installed using the postgres userid. As a result, people shorten the name PostgreSQL to simply Postgres, and in many cases people use the two names interchangeably.

PostgreSQL is pronounced as "post-grez-q-l". Postgres is pronounced as "post-grez".

Some people get confused, and refer to "Postgre", which is hard to say, and likely to confuse people. Two names are enough, so please don't use a third name!

Getting PostgreSQL

PostgreSQL is 100% open source software.

PostgreSQL is freely available to use, alter, or redistribute in any way you choose. PostgreSQL's license is an approved open source license very similar to the BSD (Berkeley Distribution Software) license, though only just different enough that it is now known as TPL (The PostgreSQL License).

How to do it...

PostgreSQL is already in use by many different application packages, and so you may already find it installed on your servers. Many Linux distributions include PostgreSQL as part of the basic installation, or include it with the installation disk.

One thing to be wary of is that the version of PostgreSQL included may not be the latest release. It will typically be the latest major release that was available when that operating system release was published. There is usually no good reason to stick at that level—there is no increased stability implied there and later production versions are just as well-supported by the various Linux distributions.

If you don't yet have a copy, or you don't have the latest version, you can download the source code or download binary packages for a wide variety of operating systems from the following URL:
http://www.postgresql.org/download/

Installation details vary significantly from platform-to-platform and there aren't any special tricks or recipes to mention. Please, just follow the installation guide, and away you go. We've consciously avoided describing the installation processes here to make sure we don't garble or override the information published to assist you.

If you would like to receive e-mail updates of the latest news, then you can subscribe to the PostgreSQL announce mailing list, which contains updates from all the vendors that support PostgreSQL. You'll get a few e-mails each month about new releases of core PostgreSQL and related software, conferences, and user group information. It's worth keeping in touch with developments.

For more information about the PostgreSQL announce mailing list, visit the following URL:
http://archives.postgresql.org/pgsql-announce/

How it works...

Many people ask questions, such as "How can this be free?", "Are you sure I don't have to pay someone?", or "Who gives this stuff away for nothing?"

Open source applications such as PostgreSQL work on a community basis, where many contributors perform tasks that make the whole process work. For many of those people, their involvement is professional, rather a hobby, and they can do this because there is generally a great value for both contributors and their employers alike.

You might not believe it. You don't have to because It Just Works.

There's more...

Remember that PostgreSQL is more than just the core software. There is a huge range of websites offering add-ons, extensions, and tools for PostgreSQL. You'll also fi nd an army of bloggers describing useful tricks and discoveries that will help you in your work.

 

And, there is a range of professional companies able to offer you help when you need it.

Connecting to PostgreSQL server

How do we access PostgreSQL?

Connecting to the database is most people's first experience of PostgreSQL, so we want to make it a good one. So, let's do it, and fix any problems we have along the way. Remember that a connection needs to be made securely, so there may be some hoops for us to jump through to ensure that the data we wish to access is secure.

Before we can execute commands against the database, we need to connect to the database server, giving us a session.

Sessions are designed to be long-lived, so you connect once, perform many requests, and then eventually disconnect. There is a small overhead during connection. That may become noticeable if you connect/disconnect repeatedly, so you may wish to investigate the use of connection pools. Connection pools allow pre-connected sessions to be served quickly to you when you wish to reconnect.

Getting ready

First, catch your database. If you don't know where it is, we'll probably have difficulty accessing it. There may be more than one, and you'll need to know the right database to access, and have the authority to connect to it.

How to do it...

You need to specify the following fi ve parameters to connect to PostgreSQL:

  • host or host address
  • port
  • database name
  • user
  • password (or other means of authentication, if any)

To connect, there must be a PostgreSQL server running on host, listening on port number port. On that server, a database named dbname and user must also exist. The host must explicitly allow connections from your client—this is explained in the next recipe, and you must also pass authentication using the method the server specifi es. For example, specifying a password won't work if the server has requested a different form of authentication.

Almost all PostgreSQL interfaces use the libpq interface library . When using libpq, most of the connection parameter handling is identical, so we can just discuss that once.

If you don't specify the preceding parameters, we look for values set through environment variables, which are as follows:

  • PGHOST or PGHOSTADDR
  • PGPORT (or set to 5432 if this is not set)
  • PGDATABASE
  • PGUSER
  • PGPASSWORD (though this one is defi nitely not recommended)

If you specify the first four parameters somehow, but not the password, then we look for a password file.

Some PostgreSQL interfaces use the client-server protocol directly, so the way defaults are handled may differ. The information we need to supply won't vary signifi cantly, so please check the exact syntax for that interface.

How it works...

The PostgreSQL server is a client-server database. The system it runs on is known as the host. We can access the PostgreSQL server remotely through the network. However, we must specify the host, which is a hostname, or a hostaddr , which is an IP address. We can specify a host of "localhost" if we wish to make a TCP/IP connection to the same system. It is often better to use a Unix socket connection, which is attempted if the host begins with a slash (/) and the name is presumed to be a directory name (default is /tmp).

On any system, there can be more than one database server. Each database server listens on exactly one "well-known" network port , which cannot be shared between servers on the same system. The default port number for PostgreSQL is 5432, which has been registered with IANA, and is uniquely assigned to PostgreSQL. (You can see it used in the /etc/services file on most *nix servers). The port number can be used to uniquely identify a specific database server if many exist.

A database server is also sometimes known as a "database cluster", because the PostgreSQL server allows you to define one or more databases on each server. Each connection request must identify exactly one database identifi ed by its dbname. When you connect, you will only be able to see database objects created within that database.

A database user is used to identify the connection. By default, there is no limit on the number of connections for a particular user. In more recent versions of PostgreSQL, users are referred to as login roles, though many clues remind us of the earlier naming, and it still makes sense in many ways. A login role is a role that has been assigned the CONNECT privilege.

Each connection will typically be authenticated in some way. This is defined at the server, so is not optional at connection time if the administrator has confi gured the server to require authentication.

Once you've connected, each connection can have one active transaction at a time and one fully active statement at any time.

The server will have a defined limit on the number of connections it can serve, so a connection request can be refused if the server is oversubscribed.

Inspecting your connection information

If you want to confirm you've connected to the right place and in the right way, you can execute some or all of the following commands:

SELECT inet_server_port();

This shows the port on which the server is listening.

SELECT current_database();

Shows the current database.

SELECT current_user;

This shows the current userid.

SELECT inet_server_addr();

Shows the IP address of the server that accepted the connection.

A user's password is not accessible using general SQL for obvious reasons.

You may also need the following:

SELECT version();

See also

There are many other snippets of information required to understand connections. Some of those are mentioned in this article. For further details, please consult the PostgreSQL server documentation.

PostgreSQL 9 Admin Cookbook Over 80 recipes to help you run an efficient PostgreSQL 9.0 database
Published: October 2010
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:
        Read more about this book      

(For more resources on PostgreSQL, see here.)

Enabling access for network/remote users

PostgreSQL comes in a variety of distributions. In many of these, you will find that remote access is initially disabled as a security measure.

How to do it...

  • Add/edit the following line in your postgresql.conf:
    listen_addresses = '*'
  • Add the following line as the first line of pg_hba.conf, to allow access to all databases for all users with an encrypted password:
    # TYPE DATABASE USER CIDR-ADDRESS METHOD
    host all all 0.0.0.0/0 md5

How it works...

The listen_addresses parameter specifies on which IP addresses to listen. This allows you to have more than one network card (NICs) per system. In most cases, we want to accept connections on all NICs, so we use "*", meaning "all IP addresses".

The pg_hba.conf contains a set of host-based authentication rules. Each rule is considered in sequence until one rule fires, or the attempt is specifically rejected with a reject method.

The preceding rule means a remote connection that specifies any user, or any database, on any IP address will be asked to authenticate using an md5 encrypted password.

Type = host means a remote connection.

Database = all means "for all databases". Other names match exactly, except when prefixed with a plus (+) symbol, in which case we mean a "group role" rather than a single user. You can also specify a comma-separated list of users, or use the @ symbol to include a fi le with a list of users. You can also specify "sameuser", so that the rule matches when we specify the same name for the username and database name.

User = all means "for all users." Other names match exactly, except when prefixed with a plus (+) symbol, in which case we mean a "group role" rather than a single user. You can also specify a comma-separated list of users or use the @ symbol to include a file with a list of users.

CIDR-ADDRESS consists of two parts: IP-address/sub-net mask. The subnet mask is specified as the number of leading bits of the IP-address that make up the mask. Thus /0 means 0 bits of the IP address, so that all IP addresses will be matched For example, 192.168.0.0/24 would mean match the first 24 bits, so any IP address of the form 192.168.0.x would match. You can also use "samenet" or "samehost".

Don't use the setting "password", as this allows a password in plain text.

Method = trust effectively means "no authentication". Other authentication methods include GSSAPI, SSPI, LDAP, RADIUS, and PAM. PostgreSQL connections can also be made using SSL, in which case client SSL certifi cates provide authentication.

There's more...

In earlier versions of PostgreSQL access through the network was enabled by adding the -i command line switch when you started the server. This is still a valid option, though now it means the following:

listen_addresses = '*'

So, if you're reading some notes about how to set things up, and this is mentioned, then be warned that those notes are probably long out of date. Not necessarily wrong, though worth looking further to see if anything else has changed.

See also

Look at installer- and/or operating system-specifi c documentation to find the standard location of files.

Using graphical administration tools

Graphical administration tools are often requested by system administrators.

PostgreSQL has a range of tool options. The two most popular options are as follows:

  • pgAdmin3
  • phpPgAdmin

We're going to describe pgAdmin3 in more detail here, because it is installed by default with the PostgreSQL Windows installer. That most likely makes it the most popular interface, even if many people choose to use server software running on Linux or variants.

How to do it...

pgAdmin3 is a client application that sends and receives SQL to PostgreSQL, displaying the results for you to browse. One pgAdmin client can access many PostgreSQL servers, and a PostgreSQL server can be accessed by many pgAdmin clients.

pgAdmin3 is usually named just pgAdmin. The 3 at the end has a long history, but isn't that important. It is not the release level. Current release level is 1.12 at time of writing.

When you start pgAdmin, you will be prompted to register a new server, as shown in the following screenshot:

Introduction to PostgreSQL 9

As shown in the preceding screenshot, note the five basic connection parameters, as well as other information.

The port number prompted is 2345, though this is deliberately not the default PostgreSQL port of 5432, presumably to force you to think about what setting should be used.

You should uncheck the Store password box.

If you have many database servers, you can group them together. Personally, I would avoid giving each server a colour, as green, yellow, and red are usually taken to mean status, which could easily be misinterpreted. Just give each server a sensible name.

You will then get access to the main browser screen, with the object tree view on the left, and properties on the top-right, as shown in the following screenshot:

Introduction to PostgreSQL 9

pgAdmin easily displays much of the data that is available from PostgreSQL. The information is context sensitive, allowing you to browse around and see everything quickly and easily. The information is not dynamically updated; this will only occur when you click to refresh, so keep function key F5 in mind when using the application.

You'll also find pgAdmin provides a TIP of the Day, though I would turn those off. Keep the Guru Hints option on, though luckily no chirpy paperclips offering suggestions.

pgAdmin also provides an Object Report generator and a Grant Wizard. These are useful for DBAs for review and immediate maintenance.

pgAdmin Query tool allows you to have multiple active sessions. The Query tool has a good-looking Visual Explain feature, as well as a Graphical Query Builder, as shown in the following screenshot:

Introduction to PostgreSQL 9

How it works...

pgAdmin provides a wide range of features, many of which are provided by other tools as well. That gives us the opportunity to choose which of those tools we like or dislike, and for many reasons, it is best to use the right tool for the right job, and that is always a matter of expertise, experience, and personal taste.

pgAdmin submits SQL to the PostgreSQL server, and displays the results quickly and easily. As a browser, it is fantastic. For performing small DBA tasks, it is ideal. As you might guess from these comments, I don't recommend pgAdmin for every task.

Scripting is an important technique for DBAs: you keep a copy of the task executed, and you can edit and resubmit if problems occur. It's also easy to put all the tasks in a script into a single transaction, which isn't possible using current GUI tools. pgAdmin provides pgScript, which only works with pgAdmin, so is much less easily ported. For scripting, I strongly recommend the psql utility, which has many additional features you'll grow to appreciate over time.

Although I use psql as a scripting tool, I also find it convenient as a query tool. Some people may find this strange, and assume it is a choice for experts only. Two great features of psql are the online help for SQL and "tab completion", that allows you to build up SQL quickly without having to remember the syntax.

pgAdmin also provides pgAgent, a task scheduler. Again, more portable schedulers are available, and you may wish to use those instead.

Also, a quick warning: when you create an object in pgAdmin, and if you use capitals anywhere in the object name, the object will be created with a mixed case name. If I ask for a table named MyTable, then the only way to access that table is by referring to it in double quotes as "MyTable".

Introduction to PostgreSQL 9

There's more...

phpPgAdmin is available at the following URL: http://phppgadmin.sourceforge.net/

There is an online demonstration of the software, so you can try it out yourself, and see if it does the job you want done. The following screenshot shows phpPgAdmin 4 displayed on a Windows Internet Explorer browser. Version 5 is available and works with PostgreSQL 9.

Introduction to PostgreSQL 9

One of the big contrasts with pgAdmin is that phpPgAdmin is browser-based, so it may be easier to provide secure access to administrators this way.

phpPgAdmin provides the familiar left-hand tree view of the database, and also provides a simple SQL query tool. Those are the basics for which you should be looking. Many additional features in pgAdmin3 aren't available, though if you follow my advice you will be doing much of your work using scripts, so this may not be a problem.

See also

You may also be interested in commercial tools of various kinds for PostgreSQL. A full listing is given in the PostgreSQL software catalogue at the following URL: http://www.postgresql.org/download/products/1

The following tools cover general administration, though other products not listed here specialize in development, data modeling, or model administration:

Using psql query and scripting tool

psql is the query tool supplied as part of the core distribution of PostgreSQL, so it is available and works similarly in all environments. This makes it an ideal choice for developing portable applications and techniques.

psql provides features for use as both an interactive query tool and as a scripting tool.

Getting ready

From here on, we will assume that "psql" is a sufficient command to allow you access to the PostgreSQL server. That assumes that all of your connection parameters are defaults, which may not be true.

Written out in full, the connection parameters would be as follows:

psql -h hostname -p 5432 -d dbname -U username -W

How to do it...

The easiest command is the one that executes a single SQL command and prints the output as:

$ psql -c "SELECT current_time"
timetz
-----------------
18:48:32.484+01
(1 row)

The –c command is non-interactive. If we want to execute multiple commands, we can write those commands in a text file, and then execute them using the –f option. The following command loads a very small and simple set of examples:

$ psql -f examples.sql

which produces the following output when successful:

SET
SET
SET
SET
SET
SET
CREATE SCHEMA
SET
SET
SET
DROP TABLE
CREATE TABLE
DROP TABLE
CREATE TABLE

The script examples.sql is very similar to a dump file produced by PostgreSQL backup tools, so this type of file, and the output it produces, are very common. PostgreSQL produces the name of the command as a "command tag" when it executes successfully, which is what produces the preceding output.

psql can also be used in interactive mode, which is the default, so requires no option:

$ psql
postgres=#

The first interactive command you'll need is the following:

postgres=# help

You can then type in SQL or other commands.

The last interactive command you'll need is:

postgres=# \quit

Unfortunately, you cannot type "quit" on its own, nor can you type "\exit" or other options. Sorry, just "\quit" or "\q" for short.

How it works...

psql allows you to enter the following two types of command:

  • psql "meta-commands"
  • SQL

A meta-command is a command for psql client, whereas SQL is sent to the database server. An example of a meta-command is "\q", that tells the client to disconnect. All lines that begin with "\" (backslash) as the non-blank first character are presumed to be meta-commands of some kind.

If it isn't a meta-command, then it's SQL. We keep reading SQL until we find a semicolon, so we can spread SQL across many lines and format it any way we find convenient.

The help command is the only exception. We provide this for people who are completely lost, which is a good thought, so let's start there ourselves:

There are two types of help, which are as follows:

  • \? provides help on psql meta-commands
  • \h provides help on specific SQL commands

For example:

postgres=# \h DELETE
Command: DELETE
Description: delete rows of a table
Syntax:
DELETE FROM [ ONLY ] table [ [ AS ] alias ]
[ USING usinglist ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ AS output_name ] [,]]

I find this a great way to discover or remember options and syntax.

You'll also like the ability to scroll back through the previous command history.

You'll get a lot of benefit from tab completion, which will fill in the next part of syntax just by pressing the Tab key. This also works for object names, so you can type in just the first few letters, and then press Tab; all of the options will be displayed, so you can type in just enough letters to make the object name unique and then hit Tab to get the rest. One-line comments are double-dash as follows:

-- This is a single-line comment

And multiline comments are like C and Java:

/*
* Multi-line comment
*/

You'll probably agree that psql looks a little daunting at first, with strange backslash commands. I do hope you'll take a few moments to understand the interface, and to keep digging for more. psql is one of the most surprising parts of PostgreSQL, and the tool is incredibly useful for database administration tasks when used alongside other tools.

There's more...

psql works across releases, though you may see a message like the following if you do so:

Introduction to PostgreSQL 9

psql on Windows can be a little problematic. I'd recommend you use a terminal emulator to connect to your server, and access psql from there.

See also

Check out some other useful features of psql, which are as follows:

  • Information functions
  • Output formatting
  • Execution timing by using the \timing command
  • Input/Output and editing commands
  • Automatic startup files: .psqlrc
  • Substitutable parameters ("variables")
  • Access to the OS command line

Changing your password securely

If you are using password authentication, then you may wish to change your password from time to time.

How to do it...

The most basic method is to use psql. The \password command will prompt you for a new password, and then again to confirm. Connect to psql, and type the following:

\password

Enter new password.

This causes psql to send an SQL statement to the PostgreSQL server containing an already encrypted password string. An example of the SQL statement sent is as follows:

ALTER USER postgres PASSWORD ' md53175bce1d3201d16594cebf9d7eb3f9d';

Whatever you do, don't use "postgres" as your password. This will make you vulnerable to idle hackers, so make it a little more difficult than that, please.

Make sure you don't forget it either. It could prove difficult to maintain your database if you can't get access to it later.

How it works...

As changing the password is just an SQL statement, any interface can do this. Other tools also allow this, such as:

  • pgAdmin3
  • phpPgAdmin

If you don't use one of the main password changes routes, you can still do this yourself using SQL from any interface. Note that you need to encrypt your password, because if you do submit a password in plaintext, like the following:

ALTER USER myuser PASSWORD 'secret'

then it will be shipped to the server in plaintext, though luckily will still be stored in an encrypted form.

PostgreSQL doesn't enforce a password change cycle, so you may wish to use more advanced authentication mechanisms, such as GSSAPI, SSPI, LDAP, RADIUS, and so on.

Avoiding hardcoding your password

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

Getting ready

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

First, remove the hardcoded password from where you'd 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 just about to place in the file. Having 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 need to connect, so that we can use file permissions to make the password more secure:

	   host:port:dbname:user:password
such as myhost:5432:postgres:sriggs:moresecure

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

  • On *nix systems, check for ~/.pgpass.
  • On Windows systems, check %APPDATA%\postgresql\pgpass.conf, where %APPDATA% is the Application Data subdirectory in the path. (For me, that would be C:\)

Don't forget: Set the file permissions on the file, so that security is maintained. The 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 silently. So don't forget!

How it works...

Many people name the password file as .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, and 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 could potentially connect to any database. Using the wildcard in the dbname and port fields makes sense, though is less useful in other fields.

Here are a few examples:

  • myhost:5432:*:sriggs:moresecurepw
  • myhost:5432:perf:hannu:okpw
  • myhost:*:perf:gabriele:maggioresicurezza

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, or perhaps even storing details on an LDAP server.

Using a connection service file

When the number of connection options gets too much, you may want to think about using a connection service file.

The connection service file allows you to give a single name to a set of connection parameters. This can be accessed centrally to avoid the need for individual users to know the host and port of the database, and is more resistant to future change.

How to do it...

First, create a file named pg_service.conf with the following contents:

 [dbservice1]
host=postgres1
port=5432
dbname=postgres

You can then either copy it into place at /etc/pg_service.conf or another agreed central location. You can then set the environment variable PGSYSCONFDIR to that directory location. Now, you can then specify a connection string like the following:

service=dbservice1 user=sriggs

The service can also be set using an environment variable named PGSERVICE.

How it works...

This applies to libpq connections only, so does not apply to JDBC.

The connection service file can also be used to specify the user, though that would mean that the username would be shared.

pg_service.conf and .pgpass can work together, or you can use just onr or the other, as you choose. Note that the pg_service.conf file is shared, and so is not a suitable place for passwords.

Troubleshooting a failed connection

This section is all about what you should do when things go wrong.

Bear in mind that 90% of problems are just misunderstandings, and you'll be on track again fairly quickly.

How to do it...

  • Check whether the database name and username are accurate: You may be requesting a service on one system when the database you require is on another system. Recheck your credentials. Check especially that you haven't mixed things up so that you are using the database name as the username and/or the username as the database name. If you receive "too many connections", then you may need to disconnect another session before you can connect, or wait for the administrator to re-enable the connections
  • Check for explicit rejections: If you receive the following error message:
    pg_hba.conf rejects connection for host ...

    then your connection attempt has been explicitly rejected by the database administrator for that server. You will not be able to connect from the current client system using those credentials. There is little point attempting to contact the administrator, as you are violating an explicit security policy in what you are attempting to do.

  • Check for implicit rejections: If the error message you receive is:
    no pg_hba.conf entry for ...

    then there is no explicit rule that matches your credentials. This is likely an oversight on the part of the administrator, and is common in very complex networks. Please contact the administrator, and request a ruling on whether your connection should be allowed (hopefully) or explicitly rejected in the future.

  • Check whether the connection works with psql: If you're trying to connect to PostgreSQL from anything other than the psql command-line utility, switch to that now. If you can make psql connect successfully, yet cannot make your main connection work correctly, then the problem may be in the local interface you are using.
  • Check whether the server is up: If a server is shut down, then you cannot connect. The typical problem here is simply mixing up to which server you are connecting. You need to specify the hostname and port, so it's possible you are mixing up those details.
  • Check whether the server is up and accepting new connections: A server that is shutting down will not accept new connections, apart from superusers. Also, a standby server may not have the hot_standby parameter enabled, preventing you from connecting.
  • Check whether the server is listening correctly.
  • Check the port on which the server is actually listening: Confirm that the incoming request is arriving on interface listed in the listen_addresses parameter, or whether it is set to * for remote connections, or localhost for local connections.
  • Check whether the database name and username exist: It's possible the database or user no longer exists.
  • Check the connection request: Check whether the connection request was successful, yet was somehow dropped after connection. You can confirm this by looking at the server log when the following parameters are enabled:
    log_connections = on
    log_disconnections = on
  • Check for other disconnection reasons: If you are connecting to a standby server, it is possible that you have been disconnected because of hot standby conflicts.

There's more...

Client authentication and security are the rapidly changing areas between releases. You will also find differences between maintenance-release levels.

The PostgreSQL documents can be viewed at the following URL: http://www.postgresql.org/docs/current/interactive/client-authentication.html

Always check which release levels you are using before consulting the manual or asking for support. Many problems are caused simply by confusing the capabilities between release levels.

Summary

In this article we covered topics such as an introduction to PostgreSQL 9, downloading and installing PostgreSQL 9, connecting to a PostgreSQL server, enabling server access to network/remote users, using graphical administration tools, using psql query and scripting tools, changing your password securely, avoiding hardcoding your password, using a connection service file, and troubleshooting a failed connection.


Further resources on this subject:


PostgreSQL 9 Admin Cookbook Over 80 recipes to help you run an efficient PostgreSQL 9.0 database
Published: October 2010
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

About the Author :


Simon Riggs

Simon Riggs is one of the few Major Developers and Committers on the PostgreSQL database project, and is also CTO of 2ndQuadrant, providing 24x7 support and services to PostgreSQL users worldwide. Simon has worked with enterprise-class database applications for more than 20 years, with prior certifications on Oracle, Teradata and DB2. Simon is responsible for much of the database recovery and replication code in PostgreSQL, and designed or wrote many of the latest performance enhancements. He uses his operational experience to contribute to many aspects of both internal design and usability.

Books From Packt


Mastering phpMyAdmin 3.3.x for Effective MySQL Management
Mastering phpMyAdmin 3.3.x for Effective MySQL Management

MySQL Admin Cookbook
MySQL Admin Cookbook

MySQL 5.1 Plugin Development
MySQL 5.1 Plugin Development

Nginx HTTP Server
Nginx HTTP Server

PostgreSQL 9.0 High Performance
PostgreSQL 9.0 High Performance

YUI 2.8: Learning the Library
YUI 2.8: Learning the Library

OpenCart 1.4 Beginner's Guide
OpenCart 1.4 Beginner's Guide

Learning jQuery 1.3
Learning jQuery 1.3


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software