Configuring PostgreSQL

If you do not want to use the version of PostgreSQL bundled with Ubuntu, the latest PostgreSQL binary files and source are available at http://www.postgresql.org. The PostgreSQL packages are distributed as several files. At a minimum, you probably want the postgresql, postgresql-server, and postgresql-libs packages. You should see the README file in the FTP directory ftp://ftp.postgresql.org/pub/ to determine whether you need any other packages.

If you are installing from the Ubuntu package files, a necessary postgres user account (that is, an account with the name of the user running the server on your system) is created for you automatically:

$ fgrep postgres /etc/passwd postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash 

Otherwise, you need to create a user called postgres during the installation. This user shouldn't have login privileges because only root should be able to use su to become this user and no one will ever log in directly as the user. (See Chapter 14, "Managing Users," for more information on how to add users to an Ubuntu system.) After you have added the user, you can install each of the PostgreSQL packages you downloaded using the standard dpkg -i command for a default installation.

Initializing the Data Directory in PostgreSQL

After the packages have been installed, you need to initialize the data directory. To do so, you must first create the data directory and you must be the root user. The following example assumes that the data directory is /usr/local/pgsql/data.

Create the /usr/local/pgsql/data directory (using mkdir) and change the ownerships of the directory (using chown and chgrp) so it is owned by the user postgres. Then use su and, as the user postgres, issue the following commands:

# mkdir /usr/local/pgsql # chown postgres /usr/local/pgsql # chgrp postgres /usr/local/pgsql # su - postgres -bash-2.05b$ initdb -D /usr/local/pgsql/data The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale en_US.UTF-8. This locale setting will prevent the use of indexes for pattern matching operations. If that is a concern, rerun initdb with the collation order set to "C". For more information see the Administrator's Guide. creating directory /usr/local/pgsql/data... ok creating directory /usr/local/pgsql/data/base... ok creating directory /usr/local/pgsql/data/global... ok creating directory /usr/local/pgsql/data/pg_xlog... ok creating directory /usr/local/pgsql/data/pg_clog... ok creating template1 database in /usr/local/pgsql/data/base/1... ok creating configuration files... ok initializing pg_shadow... ok enabling unlimited row size for system tables... ok initializing pg_depend... ok creating system views... ok loading pg_description... ok creating conversions... ok setting privileges on built-in objects... ok vacuuming database template1... ok copying template1 to template0... ok Success. You can now start the database server using:  /usr/bin/postmaster -D /usr/local/pgsql/data or  /usr/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start 

This initializes the database and sets the permissions on the data directory to their correct values.

Caution

The initdb program sets the permissions on the data directory to 700. You should not change these permissions to anything else to avoid creating a security hole.

You can start the postmaster program with the following command (make sure you are still the user postgres):

$ postmaster -D /usr/local/pgsql/data & 

If you have decided to use a directory other than /usr/local/pgsql/data as the data directory, you should replace the directory in the postmaster command line with whatever directory you are using.

Tip

By default, Ubuntu makes the PostgreSQL data directory /var/lib/pgsql/data. This isn't a very good place to store the data, however, because most people do not have the necessary space in the /var partition for any kind of serious data storage. Note that if you do change the data directory to something else (such as /usr/local/pgsql/data, as in the examples in this section), you need to edit the PostgreSQL startup file (named postgres) located in /etc/init.d to reflect the change.

Creating a Database in PostgreSQL

Creating a database in PostgreSQL is straightforward, but it must be performed by a user who has permissions to create databases in PostgreSQLfor example, initially the user named postgres. You can then simply issue the following command from the shell prompt (not the PSQL client prompt, but a normal shell prompt):

# su - postgres -bash-2.05b$ createdb database 

where database is the name of the database you want to create.

The createdb program is actually a wrapper that makes it easier to create databases without having to log in and use psql. However, you can also create databases from within psql with the CREATE DATABASE statement. Here's an example:

CREATE DATABASE database; 

You need to create at least one database before you can start the pgsql client program. You should create this database while you're logged in as the user postgres. To log in as this user, you need to use su to become root and then use su to become the user postgres. To connect to the new database, you start the psql client program with the name of the new database as a command-line argument, like so:

$ psql sampledata 

If you don't specify the name of a database when you invoke psql, the command attempts to connect to a database that has the same name as the user as which you invoke psql (that is, the default database).

Creating Database Users in PostgreSQL

To create a database user, you use su to become the user postgres from the Linux root account. You can then use the PostgreSQL createuser command to quickly create a user who is allowed to access databases or create new database users, like this:

$ createuser phudson Shall the new user be allowed to create databases? (y/n) y Shall the new user be allowed to create more new users? (y/n) y CREATE USER 

In this example, the new user named phudson is created and allowed to create new databases and database users (you should carefully consider who is allowed to create new databases or additional users).

You can also use the PostgreSQL command-line client to create a new user by typing psql along with name of the database and then use the CREATE USER command to create a new user. Here is an example:

CREATE USER foobar ; 

Caution

PostgreSQL allows you to omit the WITH PASSWORD portion of the statement. However, doing so causes the user to be created with no password. This is a security hole, so you should always use the WITH PASSWORD option when creating users.

Note

When you are finished working in the psql command-line client, you can type \q to get out of it and return to the shell prompt.

Deleting Database Users in PostgreSQL

To delete a database user, you use the dropuser command, along with the user's name, and the user's access is removed from the default database, like this:

$ dropuser msmith DROP USER 

You can also log in to your database by using psql and then use the DROP USER commands. Here's an example:

$ psql demodb Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms  \h for help with SQL commands  \? for help on internal slash commands  \g or terminate with semicolon to execute query  \q to quit demodb=# DROP USER msmith ; DROP USER demodb=# \q $ 

Granting and Revoking Privileges in PostgreSQL

As in MySQL, granting and revoking privileges in PostgreSQL is done with the GRANT and REVOKE statements. The syntax is the same as in MySQL except that PostgreSQL doesn't use the IDENTIFIED BY portion of the statement because with PostgreSQL, passwords are assigned when you create the user with the CREATE USER statement, as discussed previously. Here is the syntax of the GRANT statement:

GRANT what_to_grant ON where_to_grant TO user_name; 

The following command, for example, grants all privileges to the user foobar on the database sampledata:

GRANT ALL ON sampledata TO foobar; 

To revoke privileges, you use the REVOKE statement. Here is an example:

REVOKE ALL ON sampledata FROM foobar; 

This command removes all privileges from the user foobar on the database sampledata.

Advanced administration and user configuration are complex topics. This section cannot begin to cover all the aspects of PostgreSQL administration or of privileges and users. For more information on administering PostgreSQL, see the PostgreSQL documentation or consult a book on PostgreSQL, such as PostgreSQL (Sams Publishing).