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 Cloud Server 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 Control Panel.

 
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: What about SSH? By default, your 'sshd_config' file will have this line:

 
# 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:

 
demo ~: sudo su - postgres
[sudo] password for demo:
postgres@demo:~$

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 server.

Connecting locally with psq

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:~$


Was this content helpful?




© 2014 Rackspace US, Inc.

Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License


See license specifics and DISCLAIMER