Posted on

PostgreSQL Cheat Sheet

PostgreSQL is one of our favorite database engines for a variety of reasons.  Here is our cheat sheet to help you get online and get around Postgres with minimal effort.

Database Access Security

Database security is handled primarily in two place, from the system service level via a file called pg_hba.conf and within the database metadata files themselves.   The pg_hba.conf file controls what level of credentials are needed based on what IP address the requesting connection is coming from.   The metadata within the engine itself generally controls user level access once they are connected and approved at the system level.

Systemwide Configuration via pg_hba.conf

This file matches IP address with a set of rules to determine how much data you need to provide in the first place before getting access to the database engine.   It includes the IP address, the username trying to connect, and what type of validation is needed.

The data comes in a series of tab separated columns including:

  • Connection Type
    • local = from local tty connection
    • host = from an internet connection
  • Database = which database is the user trying to connect to?
  • User = which user they are connecting as.
  • IP Address = what address are they coming from?
  • Method = how shall we authenticate them?
    • md5 = let them in if the password matches
    • ident sameuser = let them in in the password matches and their login user matches the user they are trying to connect as
    • trust = let them in as long as the ip address matches, no password required

Finding pg_hba.conf

The pg_hba.conf file lives in various places depending on the flavor of Linux.

  • Red Hat, CentOS, Fedora = /var/lib/pgsql/data/
  • Ubuntu = /etc/postgresql/<version>/main/

Command Line Access

You can do a lot of maintenance operations or test queries using the command line interpreter.  The command line in PostgreSQL is accessed via the psql command.   The most often used parameters with psql are to connect as a user other than your login user, provide your password, and give it the name of the database on which to connect.

Example:

# psql -U other_name -W other_db

Command Line Shortcuts

From the command line there are a variety of shortcuts to help you navigate around the database engine or see what is going on.  Here are a few of the most useful:

  • List Databases: \l
  • List (display) Tables : \dt
  • List Columns in a Table: \d <tablename>

Creating A Database

Here is how you create a new database that is owned by  a specific user.  This assumes a “clean slate” install.   You will need to have the postgres user login credentials and/or root access.  You will be creating a PostgreSQL user and password and will change the system-level postgres daemon security settings to allow access with the password regardless of which user you login as.

# # login as postgres user or su postgres if you are root

# psql
psql> create user lance with password ‘cleveland’;
psql> create database myfirstdb with owner lance;
psql> \q
# vi /var/lib/pgsql/data/pg_hba.conf

While in pg_hba.conf change this line:

local   all         all                              ident

to this:

local   all         all                              md5

Backing Up / Dumping Your Data

Data dumps are a quick way to put the schema, data, or a combination of both out into a file that can be used to re-create the database on other systems or just back it up to a remote location.  The PostgreSQL command for this is pg_dump.  It takes the same parameters as thecommand line access.

Simple Data Dump

Example:

# pg_dump -U myname -W the_db_name > dump_thedb_2010_0704_001.sql

Clean Data Dump

This is the format to use if you want to ensure the entire database is dropped & re-created when loading on a new system.

Example:

# pg_dump --clean --create -U myname -W the_db_name > dump_thedb_2010_0704_001.sql

Reloading Dumped Data

To reload such a script into a (freshly created) database named the_db_name :

# psql -d the_db_name -f dump_thedb_2010_0704_001.sql

If the clean data dump method was used you will want to login as postgres and let the sql script create the database:

# su postgres

# psql -f dump_thedb_2010_0704_001.sql

Summary

There are plenty more tips, tricks, and shortcuts.  These will get you started.  We’ll add more as time allows.