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.
PostgreSQL uses layered security to control access to the database server and the data inside it.
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.
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.
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:
Roles that have this attribute are known as "users" and can be used to establish connections to the database server.
A role's password, if it has one, is stored in this attribute.
Only roles that have this attribute can be used to create databases.
Only roles that have this attribute can be used to create, drop and alter other roles.
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.
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.
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.