Online College Courses for Credit

+
CREATE USER/ROLE to Add Users

CREATE USER/ROLE to Add Users

Rating:
(0)
Author: Sophia Tutorial
Description:

Identify a correctly composed CREATE USER/ROLE statement to create users in the database.

(more)
See More
Tutorial

what's covered
This tutorial explores creating users in a database in two parts:

  1. CREATE ROLE
  2. Other Attributes

1. CREATE ROLE

For many databases, there is a separation between user accounts and groups. User accounts would be created and added to groups. For best practices, permissions are set to a group so that the users are added to a group. The permissions that are added to the group would be applied to the account. In PostgreSQL, we use roles for both users and groups. Logically, roles that can log into the database are the same as users which are called login roles.

When we have roles that contain other roles, they are called group roles which are the same as groups in other databases.

The basic way to create a role uses the CREATE ROLE statement like:

CREATE ROLE <rolename>;

For example, if we wanted to create a role named “newaccount”, we can run the following:

CREATE ROLE newaccount;

We can see which roles exist in our database by running the following command:

SELECT rolname FROM pg_roles;

File:11494-3200-1.png

Note that the column is rolname and not rolename. We can see the newaccount is the last role in the list. All of the role names that start with the prefix pg_ are system roles within the database. Note that this new account cannot log in as we have not defined the LOGIN attribute to that role.

To create a login role, we must use the LOGIN attribute and the initial password. Let us go ahead and create one:

CREATE ROLE myaccount
LOGIN
PASSWORD 'mypassword';

This will create a login role named myaccount with the password mypassword. Note that we do have to use single quotes around the password. Note that even with this account created, in our web interface, we will not be able to switch to it. However, if we wanted to log in through the psql client tool, the command would look like this:

psql -U myaccount -W postgres

The command would then prompt you for the password to enter in.


2. Other Attributes

With the creation of the login role, there are other attributes that you could use. The superuser attribute for example is one that can override all access restrictions in the database. It is a role that should be used only when truly needed as it can basically do anything in a database. It would look like:

CREATE ROLE adminaccount
SUPERUSER
LOGIN
PASSWORD 'secretpassword';

Other type of role is one that can create other databases in PostgreSQL. To do so, the CREATEDB attribute would be needed:

CREATE ROLE dbaccount
CREATEDB
LOGIN
PASSWORD 'securePass1';

Another parameter that can be used is the VALID UNTIL attribute. This will allow is to pass in a date and time in which the role’s password is no longer valid. This way, the user can no longer log in after that. This can be useful for individuals that may no longer work at the company after a time such as a contractor.

CREATE ROLE contractaccount
LOGIN
PASSWORD 'securePass1'
VALID UNTIL '2025-01-01';

After a second January 1st, 2025, the password for the role contractaccount would no longer be valid.


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 will allow us to create users with a variety of attributes.