Online College Courses for Credit



Author: Sophia Tutorial

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

See More

what's covered
This tutorial explores creating users in a database in two parts:
  2. Other Attributes


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;


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
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 
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 
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 
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.

The CREATE ROLE statement will allow us to create users with a variety of attributes.

Source: Authored by Vincent Tran