w3resource

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.

Option Description
SUPERUSER
NOSUPERUSER
These options determine whether the new role is a "superuser". NOSUPERUSER is the default.
CREATEDB
NOCREATEDB
These options define a role's ability to create databases. NOCREATEDB is the default.
CREATEROLE
NOCREATEROLE
These options determine whether a role will be permitted to create new roles (that is, execute CREATE ROLE). A role with CREATEROLE privilege can also alter and drop other roles. If not specified, NOCREATEROLE is the default.
INHERIT
NOINHERIT
These options determine whether a role "inherits" the privileges of roles it is a member of. INHERIT is the default.
LOGIN
NOLOGIN
These options determine whether a role is allowed to log in; that is, whether the role can be given as the initial session authorization name during client connection. NOLOGIN is the default, except when CREATE ROLE is invoked through its alternative spelling CREATE USER.
REPLICATION
NOREPLICATION
These options determine whether a role is allowed to initiate streaming replication or put the system in and out of backup mode. NOREPLICATION is the default.
CONNECTION LIMIT connlimit If role can log in, this specifies how many concurrent connections the role can make. -1 (the default) means no limit.
PASSWORD password Sets the role's password.
ENCRYPTED UNENCRYPTED These key words control whether the password is stored encrypted in the system catalogs.
VALID UNTIL 'timestamp' The VALID UNTIL clause sets a date and time after which the role's password is no longer valid. If this clause is omitted the password will be valid for all time.
IN ROLE role_name The IN ROLE clause lists one or more existing roles to which the new role will be immediately added as a new member.
IN GROUP role_name IN GROUP is an obsolete spelling of IN ROLE.
ROLE role_name The ROLE clause lists one or more existing roles which are automatically added as members of the new role.
ADMIN role_name The ADMIN clause is like ROLE, but the named roles are added to the new role WITH ADMIN OPTION, giving them the right to grant membership in this role to others.
USER role_name The USER clause is an obsolete spelling of the ROLE clause.
SYSID uid The SYSID clause is ignored but is accepted for backward compatibility.

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.

Name Description
login privilege

Only roles that have the LOGIN attribute can be used as the initial role name for a database connection. To create a role with login privilege, use either:

Syntax:

CREATE ROLE name LOGIN;
CREATE USER name;

Note: CREATE USER is equivalent to CREATE ROLE except that CREATE USER assumes LOGIN by default, while CREATE ROLE does not.

superuser status A database superuser bypasses all permission checks, except the right to log in. This is a dangerous privilege and should not be used carelessly; it is best to do most of your work as a role that is not a superuser.
database creation A role must be explicitly given permission to create databases (except for superusers, since that bypass all permission checks).
role creation A role must be explicitly given permission to create more roles (except for superusers, since that bypass all permission checks).
initiating replication A role must explicitly be given permission to initiate streaming replication (except for superusers, since that bypass all permission checks).
password A password is only significant if the client authentication method requires the user to supply a password when connecting to the database.

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



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/PostgreSQL/postgresql-database-roles.php