What Are Roles in PostgreSQL?
role in Postgres is one of the Postgres identifiers and a concept dedicated to group and manage database object privileges. Alternatively, it can be stated that role is used to denote:
a group of users, or
a container for privileges to be used or inherited by other roles.
Roles and role names (like some other objects) are global across a database cluster.
Postgres diverges from the SQL standard when it comes to roles. In the SQL standard the concept of a user is distinct from the concept of a role.
An initialized Postgres installation always comes with a one predefined role with a
superuser attribute. Most probably the name of the role will be the same as the operating initializing system user. This role can be used to connect to a database cluster with a
psql -U [role name] command and create additional roles.
A connection to a database is always associated with a role and is governed by the scope of privileges of that role.
Many Postgres command line commands, including
psql, when no role is expressly indicated use the name of the current operating system user. This works similarly for the default database name. Therefore, when the current operating system user is
johndoe simply entering
psql on the command line will try to establish a connection to the database
johndoe using the role
johndoe. If no such role or database exist a fatal error is thrown.
To expressly indicate the role to be used when establishing the connection use
$ psql -U otherjohndoe
To expressly indicate the database to which the connection should be established use
$ psql -d otherdatabase
-d options can be combined.
$ psql -U otherjohndoe -d otherdatabase
The above command line command will try to establish a connection using the role
otherjohndoe to the database
Once connected it is possible to change the current's connection role using the
SET ROLE command.
SET ROLE role_name;
To list roles existing in the current Postgres database cluster use
\du returns the list of all database cluster roles with indication of their names, attributes and memberships.
Creating & Dropping Roles
To create a role use
CREATE ROLE command.
CREATE ROLE admin;
To drop a role use
DROP ROLE command.
DROP ROLE admin;
A role cannot be dropped when there are database objects owned by it. To reassign or drop database objects globally within a given database use
REASSIGN OWNED or
DROP OWNED respectively.
Each role can have different attributes - granted to it at its creation or later - defining the scope of privileges the role has and/or its login behavior. The attributes include among others:
superuser - roles with
SUPERUSERattribute ignore all permission checks,
login - only roles with
LOGINattribute can establish initial connections,
password - only roles with
PASSWORDattribute require passwords for establishing connections,
database creation - only roles with
CREATEDBattribute can create/drop databases,
role creation - only roles with
CREATEROLEattribute can create/drop roles and grant/revoke role memberships.
To create a superuser role use the following command:
CREATE ROLE admin SUPERUSER;
To create a role with login and password attributes that can create databases and roles use the following command:
CREATE ROLE admin LOGIN PASSWORD 'foobar' CREATEDB CREATEROLE;
To alter an existing role use
ALTER ROLE command. For example, to a take database creation privilege from a role use the command
ALTER ROLE admin NOCREATEDB;
Ownership & Privileges
A role that creates an object becomes its owner.
CREATE DATABASE bike_shop;
An ownership can be transferred to another role.
ALTER DATABASE bike_shop OWNER TO jimmy;
Only a role owning an object can take effective actions on it unless it inherits respective privileges.
There are many kind of privileges. Among them are:
To grant privileges on a database object use
GRANT ... ON ... TO command.
GRANT INSERT ON users TO jimmy; GRANT ALL ON users TO jimmy;
To revoke privileges use
REVOKE ... ON ... FROM command.
Each role can be granted a membership in another role which allows it to use privileges of that another role.
Some roles can be created with the purpose of grouping privileges. Such roles can sometimes be referred to as group roles. Being granted a membership in a group role allows for sharing its privileges.
A role can use privileges of the role to which it is a member in two ways:
it can set (using the
SET ROLEcommand) the current's connection role to the role to which it is a member and use the privileges directly, or
use the privileges itself provided it has the
Member roles can be set to
NOINHERIT. Default in Postgres is
CREATEROLE attributes are not inherited.
To grant a membership in a given role use
GRANT [group role] TO [role];.
GRANT admins TO joe;
To revoke use
REVOKE [group role] FROM [role];.
REVOKE admins FROM joe;
To restore an initial connection role use
SET ROLE or
SET ROLE NONE; RESET ROLE;
All roles are members of the
PUBLIC role. Therefore granting privileges to
PUBLIC means granting them to all roles.