• Sales: 1-800-961-2888
  • Support: 1-800-961-4454

PostgreSQL - Access Control


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.







© 2011-2013 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