Managing User Accounts

As a PostgreSQL administrator, you may be responsible for creating user accounts and groups. You may also be responsible for granting and revoking privileges.

In most environments, there is a one-to-one mapping between a user's operating system identity and his PostgreSQL identity. In fact, your PostgreSQL username is often identical to your OS username.

In some cases, other configurations are useful. For example, you may want most of your users to identify themselves uniquely while providing an anonymous account for low-privileged guests. You may also have a client application that identifies itself rather than identifying the user (this is useful for utility applications that can be executed by any user without providing any sort of authentication).

A user account is shared between all databases within a given cluster. User groups are also shared between all databases within a cluster.

CREATE USER

There are two ways to create a new user: you can execute the CREATE USER command from within a client application (such as psql), or you can use the createuser shell script.

The complete syntax for the CREATE USER command is

CREATE USER user-name
 [[WITH] option ]...

option := SYSID user-id-number
 | [NO]CREATEDB
 | [NO]CREATEUSER
 | IN GROUP groupname [, ...]
 | [[UN]ENCRYPTED ] PASSWORD 'password'
 | VALID UNTIL 'expiration'

A user-name must conform to the usual rules for PostgreSQL identifiers: it should start with a letter (or an underscore) and should be at most 31 characters long. If you need to start a username with a number, just enclose the name in double quotes.

User account definitions are stored in the pg_shadow system table. You can view the layout of the pg_shadow table using the psql d meta-command:

movies=# d pg_shadow
 Table "pg_shadow"
 Column | Type | Modifiers
-------------+---------+----------
 usename | name |
 usesysid | integer |
 usecreatedb | boolean |
 usetrace | boolean |
 usesuper | boolean |
 usecatupd | boolean |
 passwd | text |
 valuntil | abstime |
Unique keys: pg_shadow_usename_index,
 pg_shadow_usesysid_index
Triggers: pg_sync_pg_pwd

You can see the correlation between the pg_shadow table and the CREATE USER options. The user-name is stored in the usename column. The user-id-number value is stored in usesysid. The usecreatedb column reflects the [NO]CREATEDB option. usetrace is reserved for future use and is not currently used. The usesuper column reflects the value of the [NO]CREATEUSER option. (As you'll see in a moment, a user who is allowed to create new user accounts is considered to be a superuser.) The usecatupd determines whether a user can directly update PostgreSQL's system tables (using the INSERT, UPDATE, and DELETE commands). If usecatupd is false, you can update the system tables only indirectly, using other commands such as CREATE TABLE, CREATE USER, and so on. The only way to change usecatupd is to use the UPDATE command (that is, UPDATE pg_shadow SET usecatupd = true). The passwd and valuntil columns store the password and expiration, respectively.

Each of the option values are, well, optional. I'll describe them all here.

SYSID

Using the SYSID user-id-number option, you can assign a specific numeric user-id to a user. The PostgreSQL Reference Manual mentions that this option is useful if you want to correlate PostgreSQL user-ids with OS user-ids, but there's a more important use for the SYSID option.

When a user creates a database object (table, view, sequence, and so on), the object owner is not associated with the user's name, but with the user's SYSID. You can see this by looking at the layout of the pg_class system table:

movies=# d pg_class
 Table "pg_class"
 Column | Type | Modifiers
----------------+-----------+----------
 relname | name |
 reltype | oid |
 relowner | integer |
 relam | oid |
...
...
 relhassubclass | boolean |
 relacl | aclitem[] |
Unique keys: pg_class_oid_index,
 pg_class_relname_index

Notice that the relowner column is defined as an integer, not as a name. What happens if you delete a user that happens to own a database object? Let's see. First, we'll log in as user bruce and create a new table:

$ psql -d movies -q -U bruce
movies=> create table bruces_table ( pkey integer );
CREATE
movies=> SELECT * FROM pg_tables WHERE tablename = 'bruces_table';
 tablename | tableowner | hasindexes | hasrules | hastriggers
--------------+------------+------------+----------+------------
 bruces_table | bruce | f | f | f
(1 row)
movies=# q

Notice that bruces_table is owned by user bruce. Now, let's remove bruce's account:

$ psql -q -d movies
movies=# DROP USER bruce;
movies=# SELECT * FROM pg_tables WHERE tablename = 'bruces_table';
 tablename | tableowner | hasindexes | hasrules | hastriggers
--------------+-------------------+------------+----------+------------
 bruces_table | unknown (UID=105) | f | f | f
(1 row)

Now, bruces_table is owned by an unknown user (whose SYSID is 105). That's not really a problem in itself, but it can certainly lead to confusion. If you don't assign a specific SYSID, CREATE USER will choose the next highest number (starting at 100). That means that eventually, you may create a new user whose SYSID turns out to be 105-bruce's old SYSID. Suddenly your brand new user owns a whole mess of database objects. You can recover from this sort of problem by adding a new user with a specific SYSID[6].

[6] You can also fix this problem by updating the relowner value in pg_class, but that's living dangerously.

Privileges (CREATEDB and CREATEUSER)

When you create a new user, you can control whether the user is allowed to create new databases. You also can control whether the user is allowed to create new users. Giving a user the right to create new databases will rarely, if ever, pose a security risk, but allowing a user to create new users can. When you grant a user CREATEUSER privileges, that user becomes a superuser in your cluster. Let me say that again in a slightly different way: A user who has CREATEUSER privileges can bypass all security restrictions in your database cluster. You can explicitly deny CREATEUSER privileges by specifying NOCREATEUSER. NOCREATEUSER is assumed if you don't specify either value.

The CREATEDB option grants the user the right to create new databases (within the cluster). You can specify NOCREATEDB to prohibit the user from creating new databases. If you specify neither CREATEDB nor NOCREATEDB, CREATE USER will assume NOCREATEDB.

Group Membership (IN GROUP)

You can assign a new user to one or more groups by including the IN GROUP clause. For example, to create a user named bernard as a member of the developers and administrators groups:

CREATE USER bernard IN GROUP developers, administrators;

If you don't assign the new user to a group, he will be a member of the pseudo-group PUBLIC, but no other groups.

PASSWORD and Password Expiration

The final two options are somewhat related. You can create an initial password for a new user by including the PASSWORD, ENCRYPTED PASSWORD, or UNENCRYPTED PASSWORD option. If you don't specify a password when you create a new user (and you are using passwords to authenticate client connections), the user will not be able to log in. If you choose to create an ENCRYPTED PASSWORD, the password will be stored, in encrypted form, in the pg_shadow system table. If you choose to create an UNENCRYPTED PASSWORD, it will also be stored in pg_shadow, but in cleartext form. If you create a password without specifying ENCRYPTED or UNENCRYPTED, CREATE USER will look to the PASSWORD_ENCRYPTION server option to decide whether to store the password in cleartext or encrypted form.

Be aware that unencrypted passwords are visible to any PostgreSQL superuser.

The VALID UNTIL'expiration' option controls password expiration. If you omit VALID UNTIL, the initial password will never expire. If you include VALID UNTIL'expiration', the password will become invalid after the time and date indicated by expiration.

createuser

The createuser shell script is a bit easier to use than CREATE USER because it prompts you for all required information. Here is sample createuser session:

$ createuser
Enter name of user to add: bernard
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
Password:
CREATE USER

There's a serious gotcha that always trips me up when I use createuser. Notice in the previous example that createuser has prompted me for a password. When you see the Password: prompt, createuser is asking for your password, not the password to be assigned to the new user. createuser is just a shell script that connects to the server and executes a CREATE USER command on your behalf. You must authenticate yourself to the server, so createdb needs to know your password. If you invoke createuser with the -pwprompt flag (or -P for short), createdb will also prompt you for the new user's password:

$ createuser pwprompt
Enter name of user to add: bernard
Enter password for user "bernard":
Enter it again:
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
Password:
CREATE USER

You can see the differencewhen I am supposed to enter bernard's password, createuser is kind enough to use a more descriptive prompt. When I have finished answering all createuser's questions, I am prompted for my password.

ALTER USER

You can modify the attributes of existing user accounts with the ALTER USER command. The ALTER USER command is similar to CREATE USER:

ALTER USER user-name
 [[WITH] option ]...

option := [NO]CREATEDB
 | [NO]CREATEUSER
 | [[UN]ENCRYPTED ] PASSWORD 'password'
 | VALID UNTIL 'expiration'

You can use ALTER USER to change a user's privileges (CREATEDB and CREATEUSER) and password information (PASSWORD and VALID UNTIL). You cannot use ALTER TABLE to change a user's SYSID. You can change a user's group membership, but not with ALTER USER; you must use the ALTER GROUP command for that.

DROP USER

Removing obsolete user accounts is easy: use the DROP USER command:

DROP USER user-name

You must be a PostgreSQL superuser to use DROP USER. When you drop a user, PostgreSQL will not delete any objects (tables, views, sequences) owned by that userthey will be owned by a "mystery" owner. You cannot drop a user who owns a database.

GRANT and REVOKE

After you have created a new user, you must decide which database objects (tables, views, and sequences) that user should be able to access, and what kinds of access they should have. For each user/object combination, you can grant SELECT, INSERT, UPDATE, DELETE, REFERENCES, and trIGGER privileges. I'll show you how to grant and revoke privileges in Chapter 23.

You can imagine that assigning individual privileges for every user of every table would be rather time-consuming and difficult to maintain. You can reduce the administrative overhead by creating user groups.

Managing Groups

You can define named groups of users to make your administrative life much easier to manage. Every group can include zero or more users. Every user can belong to one or more groups. When you grant or revoke privileges for an object, you can identify a specific user or a group of users.

Every user is automatically a member of the group PUBLIC. PUBLIC is actually a virtual groupyou can't add or remove members and you can't drop this group, but you can associate privileges with PUBLIC.

Groups are much easier to manage if they correspond to usage roles in your organization. For example, you might create groups named developers, guests, clerks, and administrators. Laying out groups so that they reflect real-world user groups makes it much easier to assign access privileges to your database objects. Of course, any given user can belong to many groups. For example, a member of the developers group might also be an administrator.

Group definitions are stored in the pg_group system table. Like database users, group definitions are shared by all databases within a cluster.

CREATE GROUP

A PostgreSQL superuser can create a new group using the CREATE GROUP command:

CREATE GROUP group-name [[WITH] option [...]]

option := SYSID group-id-number
 | USER username, ...

The group-name must meet the usual rules for PostgreSQL identifiers (31 characters or less, quoted, or starting with an underscore or a letter).

You can include a SYSID value if you want to assign a specific numeric ID for the new group. Like user accounts, a group is referenced by its numeric ID, not by name. We users know each group by name, but any table that refers to a group will refer to the numeric value. You might assign a specific numeric ID to a group for the same reasons that you might assign a specific ID to a user (see the previous section for more information).

You can assign group membership in three ways:

• Use the IN GROUP option in the CREATE USER command
• List the usernames in the USER option of CREATE GROUP
• Change the group membership using the ALTER GROUP command

A typical CREATE GROUP command might look something like this:

CREATE GROUP developers USER bernard,lefty;

This command creates a new group named developers that initially has two members: bernard and lefty.

ALTER GROUP

Using the ALTER GROUP command, you can add members to a group, or remove users from a group. The format of the ALTER GROUP command is

ALTER GROUP group-name {ADD|DROP} USER user-name [, ...]

Only PostgreSQL superusers can alter a group.

DROP GROUP

The DROP GROUP command deletes a group. The format of the DROP COMMAND is

DROP GROUP group-name

You can drop a group only if you are PostgreSQL superuser.

Now let's change focus from security-related issues to another important administrative concernbackup and recovery.