PostgreSQL: Database Roles

Introduction

Database-level roles are database-wide in their permissions scope. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up. Roles are created by users (usually administrators) and are used to group together privileges (Privilege is a right to execute a particular type of SQL statement, or a right to access another user's object. ) or other roles.

Table of contents

Database Roles

Role Attributes

Role Membership

Database Roles

PostgreSQL manages database access permissions using the concept of roles. You can create a role using CREATE ROLE SQL command. CREATE ROLE adds a new role to a PostgreSQL database cluster. You must have CREATEROLE privilege or be a database superuser to use this command.

Syntax:

CREATE ROLE name [ [ WITH ] option [ ... ] ]

Parameters:

name: The name of the new role.

Examples:

Create a role (without a password) that can log in :

CREATE ROLE david LOGIN;

Create a role with a password :

CREATE USER david WITH PASSWORD 'ks839#@S';

Create a role with a password that is valid until the end of 2014. After one second in 2015, the password is no longer valid.

CREATE ROLE david WITH LOGIN PASSWORD 'ks839#@S' VALID UNTIL '2015-01-01';

Create a role that can create databases and manage roles:

CREATE ROLE admin WITH CREATEDB CREATEROLE;

Compatibility:

The CREATE ROLE statement is in the SQL standard, but the standard only requires the syntax :

CREATE ROLE name [ WITH ADMIN role_name ]

Multiple initial administrators, and all the other options of CREATE ROLE are PostgreSQL extensions.

Alter Role

ALTER ROLE command is used to changes the attributes of a PostgreSQL role.

Syntax:

ALTER ROLE name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'

ALTER ROLE name RENAME TO new_name

ALTER ROLE name [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE { name | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER ROLE { name | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER ROLE { name | ALL } [ IN DATABASE database_name ] RESET ALL

Examples:

Change a role's password:

ALTER ROLE davide WITH PASSWORD 'hu8jmn3';

Remove a role's password:

ALTER ROLE davide WITH PASSWORD NULL;

Change a password expiration date, specifying that the password should expire at midday on 4th May 2015 using the time zone which is one hour ahead of UTC:

ALTER ROLE chris VALID UNTIL 'May 4 12:00:00 2015 +1';

Make a password valid forever:

ALTER ROLE fred VALID UNTIL 'infinity';

Give a role the ability to create other roles and new databases:

ALTER ROLE miriam CREATEROLE CREATEDB;

Give a role a non-default setting of the maintenance_work_mem parameter:

ALTER ROLE worker_bee SET maintenance_work_mem = 100000;

Give a role a non-default, database-specific setting of the client_min_messages parameter:

ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;

Compatibility:

The ALTER ROLE statement is a PostgreSQL extension.

Role Attributes

A database role can have a number of attributes that define its privileges and interact with the client authentication system.

CREATE ROLE name LOGIN;
CREATE USER name;

A role's attributes can be modified after creation with ALTER ROLE. See the reference pages for the CREATE ROLE and ALTER ROLE commands for details.

A role can also have role-specific defaults for many of the run-time configuration settings. For example, if for some reason you want to disable index scans anytime you connect, you can use:

ALTER ROLE myname SET enable_indexscan TO off;

Role Membership

It is frequently convenient to group users together to ease management of privileges: that way, privileges can be granted to, or revoked from, a group as a whole. In PostgreSQL, this is done by creating a role that represents the group, and then granting membership in the group role to individual user roles.

To set up a group role, first create the role:

Syntax:

CREATE ROLE name;

Typically a role being used as a group would not have the LOGIN attribute, though you can set it if you wish.

Once the group role exists, you can add and remove members using the GRANT and REVOKE commands:

GRANT group_role TO role1, ... ;
REVOKE group_role FROM role1, ... ;

You can grant membership to other group roles, too (since there isn't really any distinction between group roles and non-group roles). The database will not let you set up circular membership loops. Also, it is not permitted to grant membership in a role to PUBLIC.

The members of a group role can use the privileges of the role in two ways. First, every member of a group can explicitly do SET ROLE to temporarily "become" the group role. Second, member roles that have the INHERIT attribute automatically have use of the privileges of roles of which they are members, including any privileges inherited by those roles. As an example, suppose we have done :

CREATE ROLE david LOGIN INHERIT;
CREATE ROLE admin NOINHERIT;
CREATE ROLE wheel NOINHERIT;
GRANT admin TO david;
GRANT wheel TO admin;

Immediately after connecting as role david, a database session will have use of privileges granted directly to david plus any privileges granted to admin, because joe "inherits" admin's privileges. However, privileges granted to wheel are not available, because even though joe is indirectly a member of wheel, the membership is via admin which has the NOINHERIT attribute. Afte r:

SET ROLE admin;

To destroy a group role, use DROP ROLE:

DROP ROLE name;

Any memberships in the group role are automatically revoked (but the member roles are not otherwise affected).

Previous: PostgreSQL Sequence
Next: PostgreSQL Privileges