PostgreSQL on Debian

Published: March 18, 2010
—
Databases

PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness.

Installing PostgreSQL

Let’s go ahead and install it

demo@server:~$ sudo aptitude install postgresql

Configuration

We’ll make two adjustments to the configuration file to enable some automagic maintenance of our databases

demo@server:~$ sudo nano /etc/postgresql/8.3/main/postgresql.conf

Enable track_counts and autovacuum by uncommenting them (that is, removing the ’#’)

(...)

track_counts = on

(...)

autovacuum = on

The track_counts enables collection of statistics on database activity, and is needed for the auto-vacuum daemon. The purpose of the auto-vacuum daemon is to automate the execution of VACUUM and ANALYZE commands.

VACUUM garbage-collects and analyzes a database. It reclaims storage occupied by deleted tuples, because tuples that are deleted or obsoleted by an update are not physically removed until a VACUUM is performed.

ANALYZE collects statistics about a database. The statistics collected usually include a list of some of the most common values in each column, and a histogram showing the approximate data distribution in each column. These statistics help in choosing the most appropriate query plan, thus improving the speed of query processing.

When enabled, the auto-vacuum daemon runs periodically and checks for tables that have had a large number of tuples inserted, updated or deleted.

Time to apply the changes by restarting the database server with

demo@server:~$ sudo /etc/init.d/postgresql-8.3 restart

Database access

Our database server is freshly installed, but our user hasn’t got access to it quite yet, as can be seen with the following

demo@server:~$ psql
psql: FATAL:  Ident authentication failed for user "demo"

The psql program is the PostgreSQL interactive terminal. It enables you to type in queries interactively, issue them to PostgreSQL, and get the results immediately. What happened above is that we were not allowed access since we haven’t setup a PostgreSQL role.

PostgreSQL manages database access permissions using the concepts of roles. A role is a database user, a group of database users, or both, depending on how the role has been configured. Every connection to the database server, and every command running inside it is associated with a role. In addition, all objects inside the database is owned by some role. Furthermore, roles can be granted privileges for database objects they don’t own.

The concept of PostgreSQL roles is similar to that of Linux users and groups. Although they are conceptually alike, the terms don’t overlap. Roles only have meaning in reference to the database server.

Even though database roles are completely separate from operating system users, it might be convenient to give them identical names. The reason is that many applications assume the name of the current operating system user by default, so you don’t have to specify the user name for each command.

Creating roles

It is good practice to create a role that has the CREATEROLE and CREATEDB privileges, but is not a superuser, then use this role for all routine management of databases and roles. This approach avoids the dangers of operating as a superuser for tasks that don’t really require it.

Be careful with the CREATEROLE privilege, because there is no concept of inheritance for the privileges of a CREATEROLE-role. That means that even if a role doesn’t have a certain privilege but is allowed to create other roles, it is possible to create another role with different privileges than its own. For example, if the role “user” has the CREATEROLE privilege but not the CREATEDB privilege, he can create a new role with CREATEDB privilege. This doesn’t apply to creating roles with superuser privileges. In order to do that, the role must already be a superuser.

During the PostgreSQL installation process, a database role and a user account named postgres were created for us in order to bootstrap the database system. This role is a PostgreSQL superuser, and so has full control over the database server and the objects stored in it. We’ll use it to give our user access to create roles and databases.

demo@server:~$ sudo -u postgres createuser -S demo
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y

Alternatively, that is if you don’t want to answer the questions above, you can issue the command with additional parameters

# same as above, but without questions
demo@server:~$ sudo -u postgres createuser -S -d -r demo

We’ve now created a role that gives us access to create databases, and to create new roles. Let’s try to connect to the database again

demo@server:~$ psql
psql: FATAL:  database "demo" does not exist

Creating databases

At the end of the last section, we created a role with the ability to make new databases as well as new roles. We still got an error message when trying to connect to the database server, because psql looks for a database name which is identical to the role name trying to connect, unless you specify the database name.

We’ll create the database for our role next, and specify UTF-8 encoding

demo@server:~$ createdb -E utf8 demo

Trying the psql command again we get the following

demo@server:~$ psql

This will put you in the psql terminal which looks something like the following

Welcome to psql 8.3.9, 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

demo=>

psql is very powerful, but three commands are good to know right from the start. Those are listing databases

Welcome to psql 8.3.9, 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

demo=> \l
        List of databases
   Name    |  Owner   | Encoding  
-----------+----------+-----------
 demo      | demo     | UTF8
 postgres  | postgres | SQL_ASCII
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
(4 rows)

demo=>

Listing roles

demo=> \du
                               List of roles
 Role name | Superuser | Create role | Create DB | Connections | Member of 
-----------+-----------+-------------+-----------+-------------+-----------
 demo      | no        | yes         | yes       | no limit    | {}
 postgres  | yes       | yes         | yes       | no limit    | {}
(2 rows)

demo=>

And finally, quitting

demo=> \q
demo@server:~$

Deleting roles and databases

Roles can be deleted provided that you’re not trying to drop the current role, and that there are no database objects depending on the role.

If you try to drop the demo role with the demo user, you’ll get the following error

demo@server:~$ dropuser demo
dropuser: removal of role "demo" failed: ERROR:  current user cannot be dropped

In order to delete this role, we need to run the psql command with the postgres role like this

demo@server:~$ sudo -u postgres dropuser demo
dropuser: removal of role "demo" failed: ERROR:  role "demo" cannot be dropped because some objects depend on it
DETAIL:  owner of database demo

That didn’t work because the demo role has a database associated with it. To remove the user, we need to remove the role from the database, either by deleting the database, or by transferring the ownership of it to another role.

Deleting the database is done with

# since the demo role owns the database you can drop
# the demo database either with the demo user
demo@server:~$ dropdb demo

# or with the postgres user
demo@server:~$ sudo -u postgres dropdb demo

Changing ownership of the database from demo to test is easiest done with superuser privileges. Only a superuser or the database owner can change these settings provided that the database owner also is a member of the new role.

Transfer ownership of the database with the postgres superuser

demo@server:~$ sudo -u postgres psql
Welcome to psql 8.3.9, 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=# \l
        List of databases
   Name    |  Owner   | Encoding  
-----------+----------+-----------
 demo      | demo     | UTF8
 postgres  | postgres | SQL_ASCII
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
(4 rows)

postgres=# ALTER DATABASE demo OWNER TO test;
ALTER DATABASE
postgres=# \l
        List of databases
   Name    |  Owner   | Encoding  
-----------+----------+-----------
 demo      | test     | UTF8
 postgres  | postgres | SQL_ASCII
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
(4 rows)

postgres=# \q
demo@server:~$

Now we can delete the demo role with the postgres user (because we can’t delete ourselves)

demo@server:~$ sudo -u postgres dropuser demo

Roles for specific projects

If you want to use PostgreSQL as the database back-end for a project, then setting up a role and database dedicated to said project – as opposed to all databases – is a good idea.

To create a user that isn’t a superuser, and is not allowed to create databases, nor able to create new roles, and needs a password to connect, issue the command below

demo@server:~$ sudo -u postgres createuser -S -D -R -P project_user

Create a new database with project_user as the database owner

demo@server:~$ sudo -u postgres createdb -O project_user site_live

To connect to the site_live database with the project_user role, you must specify the host, or you’ll get an authentication error even if you submit the correct password. Like this

demo@server:~$ psql -U project_user -W -d site_live -h localhost

The above command roughly translates into “run psql as the project_user user (-U), prompt for a password before connecting to the database (-W), if the correct password is provided, then connect to the database ‘site_live’ (-d) using the ‘localhost’ host name (-h)”.