PostgreSQL - local connections as postgres

In this article, we'll connect to the database server as the "postgres" role, using the psql client software for the first time.


The "postgres" role

As we learned in the previous article, the PostgreSQL installation process created a database role named "postgres". This role has the SUPERUSER attribute, and so has full control over the database server and the objects stored in it. It also has the LOGIN attribute, so is frequently referred to as a database "user".

At the same time, a normal Linux user account was created with the same name, "postgres".

The postgres database role (user) and the postgres Linux user generally work together. Each is configured with a blank password, which enhances security! That will be explained as we go along.

pg_hba.conf - Client Authentication

By default, access to the database server as the postgres role is highly restricted. The postgres configuration file 'pg_hba.conf' specifies how client connections are authenticated.

Let's open this file and take a look:

sudo nano /etc/postgresql/8.3/main/pg_hba.conf

We'll work with this config file more extensively in a later article; for now we're only interested in a few lines:

# Database administrative login by UNIX sockets
local   all         postgres                          ident sameuser

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

Essentially, this says that the postgres database user (role) can make local UNIX socket connections to all databases stored in this postgres server, as long as the local OS user invoking that connection is identified with the same name as this role, "postgres".

WHEW!

Stated more simply, only the Linux user account named "postgres" can be used to establish local UNIX socket connections as the postgres database role.

The 'pg_hba.conf' file can be updated to allow less restricted local and remote connections, but that's a subject for a later article.

su to the postgres Linux user

So if we're going to connect to the postgres server, we'll first need to login to the slice as the postgres Linux user. It's a normal Linux user account, the same kind of account our setup tutorials recommend you create for day-to-day logins and admin work on your slice. But how can we access it?

Recall that the postgres Linux user was created with a blank password by aptitude's installation script. The Linux-PAM system won't allow logins with an empty password, and logins from the console authenticate against PAM — so we won't be able to login as 'postgres' using the AJAX console in the SliceManager.

What about SSH? By default, your 'sshd_config' file will have this line:

PermitEmptyPasswords no

And besides that, our setup tutorials recommend you altogether disable password authentication in 'sshd_config'. So a login over SSH is not possible either, unless we setup hostkeys for this user, which we won't consider here.

So a blank password has paradoxically made this account a difficult one to access! That's a bonus for security, however odd it may seem.

The key to logging in as 'postgres' will be the Linux su command. Here's how we'll use it:

sudo su - postgres

If we don't prepend 'su' with 'sudo', we'll be asked for the slice's root password, which is inconvenient.

The '- [username]' after 'su' tells it to load that user's normal shell environment.

You should now see something like this:

su to the postgres user

Great! Now we're ready to access the database server.

Introducing psql

There are a variety of tools that can be used to access a PostgreSQL database server, and most admins prefer a graphical client for involved database work. But the standard tool is called psql — a powerful command line client that can access both local and remote postgres servers. The official psql documentation will be an indispensable resource if you plan to do serious work with this tool.

The 'postgresql-client-8.3' package was included in the installation of the 'postgresql' virtual package. So psql is already available on our Ubuntu Hardy slice.

Connecting locally with psql

When logged in as the postgres Linux user, connecting with psql is this simple:

postgres@demo:~$ psql -U postgres
...
Welcome to psql 8.3.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=#

We're in!

The database server has authenticated the connection based on the fact that the specified role (-U postgres) matches the name of the Linux user which initiated the connection.

If we don't specify a role when connecting to our postgres server, it's assumed we mean to specify a role with the same name as the OS user initiating the connection. In this case the names do match, but many times they won't — so it's a good habit to explicitly specify a role, as doing so will reduce confusion if we meet with a connection error.

Now, using the '\du' command we can query the database server about existing roles:

postgres=# \du
...
                               List of roles
 Role name | Superuser | Create role | Create DB | Connections | Member of 
-----------+-----------+-------------+-----------+-------------+-----------
 postgres  | yes       | yes         | yes       | no limit    | {}
(1 row)

That's the expected answer since we haven't created any additional roles just yet.

Exit psql

To close our psql session, we can type Ctrl-D or use the '\q' command. We'll be returned to the shell prompt, logged in as the postgres Linux user.

postgres=# \q
...
postgres@demo:~$

Summary

We've had our first look at the psql client software and the 'pg_hba.conf' file. Also, the steps necessary to make our first connection have brought together some of the concepts introduced in the access control article.

In the next article we'll learn how to create and delete database roles.

From there we can move on to creating and deleting databases, and then to configuring postgres for less restricted local and remote connections.

Mike

Article Comments:

Kiz commented Fri Oct 09 12:24:48 UTC 2009:

not related to your article, but i'm wondering what the name of Mac's font you use on the screenshot ( http://articles.slicehost.com/assets/2009/4/9/su-to-postgres.jpg ) ^^? is it free? where i could get it for my Linux Box.. thank you in advance..

Mike commented Sun Nov 15 22:40:14 UTC 2009:

@Kiz the font is "Monaco 13pt". I don't believe it's free, see: http://www.myfonts.com/fonts/apple/monaco/

Yoyo commented Wed Aug 31 07:13:36 UTC 2011:

Amazing! So simple and straight forward explanation. Thank you sooooo much.

RLM commented Fri May 24 16:45:47 UTC 2013:

Thank you very much for your article. I was frustrated and very angry by my inability to get from server install to connecting to the ROLE postgres. The searches that I did always assumed that that step was done. Why is this info not in the 2300 page documentation in chapter 20 where it should be. This is my first time doing a server and maybe this is the way that kind of install works and every body knows this. Thanks again

andru commented Sun Jul 14 09:42:45 UTC 2013:

Not so far I have found new cool tool to work with PostgreSQL on ubuntu — Valentina Studio. Its free edition can do things more than many commercial tools!!

I very recommend check it. http://www.valentina-db.com/en/valentina-studio-overview

Want to comment?


(not made public)

(optional)

(use plain text or Markdown syntax)