PostgreSQL - access control

Following from the PostgreSQL installation article, we now take a look at some important concepts you will need to be familiar with when working with PostgreSQL.

As such, we'll introduce access control and terms such as roles, attributes and privileges.


Overview

PostgreSQL uses layered security to control access to the database server and the data inside it.

This article isn't intended as an extensive treatment of the subject, please see Chapter 19 in the official documentation for more details.

My goal is to give you some basic familiarity with the core concepts of postgres access control, so you can get up and running quickly with your postgres databases.

The information presented here will seem abstract at first, but even limited familiarity with these concepts will allow you to make better sense of the next articles in this series.

Roles

Beginning with PostgreSQL version 8.1, every connection to the database server and every command running inside it is associated with a "role". Also, each object inside the database is owned by some role; and roles can be granted privileges for database objects they don't own.

A database "role" is similar in concept to a Linux "user", but a role can have membership in another role, so it encompasses the familiar Linux concepts of "users" and "groups".

Note that while they're conceptually similar, postgres database roles don't directly overlap with the Linux user accounts for your Slice; they are distinct entities, and roles only have meaning in reference to your database server.

Note too that roles are defined within the postgres data files, not in a separate configuration file.

Attributes

The access that a particular role has to the database server and its contents is determined by its "attributes" and "privileges". A role's attributes and privileges are stored with it inside the postgres data files.

We'll look at attributes first, and then privileges:

— LOGIN

Roles that have this attribute are known as "users" and can be used to establish connections to the database server.

— PASSWORD

A role's password, if it has one, is stored in this attribute.

— CREATEDB

Only roles that have this attribute can be used to create databases.

— CREATEROLE

Only roles that have this attribute can be used to create, drop and alter other roles.

— SUPERUSER

A role with this attribute can bypass all security restrictions, so it's conceptually similar to the Linux root account. It's generally not a good idea to work in the database as a superuser role; it's better to do your day-to-day work as a normal role.

Privileges

When an object is created in the database server, ownership is generally assigned to the role that was used to create that object, and only the owner role and any superusers will have access to it. "Privileges" must be granted to any non-owner roles that require access.

Some common privileges include: SELECT, INSERT, UPDATE, and DELETE; and there are several more.

The "postgres" role

During the installation process on our Ubuntu Hardy slice, PostgreSQL was automatically configured with a role aptly 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 and the postgres Linux user generally work together.

Summary

The concepts of roles, attributes, and privileges are key to working with PostgreSQL. Apart from actual work in a postgres database, they make for abstract subject matter.

As you work through the postgres articles, you can refer back to this one until you are comfortable with the terms.

In the next article, we'll learn how to establish local connections as the postgres role.

Mike

Article Comments:

Rit Li commented Wed Nov 11 17:39:58 UTC 2009:

Thank you for the article.

Alastair Brunton commented Mon Mar 01 09:58:45 UTC 2010:

Thanks for this article.. very informative.

Want to comment?


(not made public)

(optional)

(use plain text or Markdown syntax)