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 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.
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.
Source: Authored by Vincent Tran