Online College Courses for Credit

+
CREATE ROLE to Create Groups

CREATE ROLE to Create Groups

Rating:
(0)
Author: Sophia Tutorial
Description:

Identify a correctly composed CREATE ROLE statement to create groups/roles in the database.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores using the CREATE ROLE to create groups in a database.

The CREATE ROLE statement as we looked at in the last tutorial can be used to create a user or a group depending on how it is used. This is something unique to PostgreSQL as other databases will typically have a different concept to separate the user and group. We have already looked at the creation of a role for a user. This role would need the LOGIN attribute with a password that is set. Group roles on the other hand would not have a LOGIN attribute or password as users are meant to inherit the permissions from the group role as a best practice.

The structure of the command would look like this:

CREATE ROLE <rolename>
<WITH>
[SUPERUSER]
[CREATEDB]
[CREATEROLE]
[INHERIT]
[LOGIN]
[CONNECTION]
[IN];

The keyword WITH is an optional keyword in the CREATE ROLE command. Notice that we have a lot of different options here that can be used. From the SUPERUSER to the BYPASSRLS option, there is a default option with NO in front of it like NOSUPERUSER or NOBYPASSRLS that is available. By default, it is used if the specific parameter is not used.

We looked at the SUPERUSER, CREATEDB, and LOGIN options in the prior tutorial. As a reminder, the SUPERUSER is one that has the ability to override all access restrictions. It can be a dangerous status as the role can drop any objects or access any data. To create a role using the SUPERUSER attribute, the account must be a SUPERUSER.

The CREATEDB attribute just defines if the role is able to create databases. Typically a database administrator role would benefit from this attribute.

The CREATEROLE attribute allows you to create new roles, ALTER roles and DROP other roles.

The INHERIT option in this case is the default choice. There is the NOINHERIT attribute. This attribute determines if the role inherits the permissions and privileges of roles that it is a member of. A role that has the INHERIT attribute has the ability to use the database permissions that have been granted to all of the roles that it is directly or indirectly a member of.

The CONNECTION LIMIT determines how many concurrent connections the role can make. The default is set to -1 meaning that there is no limit.

The IN ROLE will list one or more existing roles in which the new role would be immediately added as a member. This could be a login role or another group role. For example, you could have an executive role that is set up to be part of a management role so that the executive role will get all of the permissions that the management role has.

There are other attributes that can be used with the CREATE ROLE that are very specific to a given scenario that we will not cover.

We can use the different attributes together in a single CREATE ROLE statement. For example, you could have created an admin role that has the ability to create databases and roles:

CREATE ROLE adminrole
CREATEDB
CREATEROLE;

This would create a group role called adminrole.


try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
The CREATE ROLE statement can be used to create group roles. Login roles can be a part of these.