Creating and Managing Roles: ROLES

A role in PostgreSQL can represent either a single user or a group of users. It controls access to databases, tables, schemas, and other objects.

When you create a role, by default it's not tied to any specific user or group, but you can assign it properties and privileges that fit your needs.

Difference Between Roles and Users

So what about users? It's important not to mix things up here. In PostgreSQL, users are also considered roles, but with a twist: users always have the ability to log in. So, any role can be a user if it has the LOGIN attribute. If you just want a role to group users together or not let it log in, you can skip the LOGIN attribute.

Example:

• Role with LOGIN attribute = user.
• Role without LOGIN = group or "service role".

Creating a Role

To create roles, you use the CREATE ROLE command. The syntax looks like this:

CREATE ROLE <role_name> [WITH] [parameters];

Where parameters are role properties, like:

• LOGIN — ability to log in to the database.
• CREATEDB — ability to create databases.
• CREATEROLE — ability to create other roles.
• SUPERUSER — full access (these are the most dangerous roles, use with caution).
• PASSWORD — password for authentication.

Let's check out an example of creating a user role. Say you want to create a user student with login rights and a password. Run this query:

CREATE ROLE student WITH LOGIN PASSWORD 'securePassword123';

This command will create a role named student that can connect to the database.

Now let's create a teachers role, which will be a group. A group role doesn't need the LOGIN attribute, since users in the group will use their own accounts.

CREATE ROLE teachers;

Granting Privileges to Roles

Roles in PostgreSQL are made to give or restrict access to resources. For this, you use the GRANT and REVOKE commands.

Granting Privileges with GRANT

Command syntax:

GRANT <privileges> ON <object> TO <role>;

Let's create a courses table and give the teachers role rights to read and modify data in this table.

-- Create table
CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    teacher_id INT
);

-- Grant access
GRANT SELECT, INSERT, UPDATE ON courses TO teachers;

Now all users in the teachers role can view, add, and change data in the table.

Revoking Privileges with REVOKE

To take away privileges, use the REVOKE command. Syntax:

REVOKE <privileges> ON <object> FROM <role>;

To stop the teachers group from updating data in the courses table, run:

REVOKE UPDATE ON courses FROM teachers;

Role Hierarchy: Inheritance

Users can "inherit" privileges from a group role to make management easier. It's like passing down "inheritance" from one user to another.

The INHERIT keyword lets a role inherit privileges from another role. This is on by default, but you can change it.

Example:

-- Add user `john` to the `teachers` group
GRANT teachers TO john;

Now user john automatically gets all privileges assigned to the teachers group role.

Let's check the privileges:

\du john

If you want to turn off inheritance, create the role with the NOINHERIT attribute.

CREATE ROLE admin NOINHERIT;

Now users in the admin role won't be able to use its privileges by default.

Creating Role Groups for Access Management

Role groups make access management way easier, especially in big projects. Instead of giving rights to every user, you can add users to groups and give the groups the privileges they need.

Here's a simple example of a group role. Let's create some groups:

• students — group for students.
• teachers — group for teachers.

CREATE ROLE students;
CREATE ROLE teachers;

Assign users to groups:

-- Add user john to the students group
GRANT students TO john;

-- Add user jane to the teachers group
GRANT teachers TO jane;

Now, by giving table access to the students and teachers groups, you automatically give it to all their members!

Hands-on Exercise

1. Create a table exams with fields:
   • id: primary key;
   • subject: text;
   • teacher_id: teacher's ID.

CREATE TABLE exams (
    id SERIAL PRIMARY KEY,
    subject TEXT NOT NULL,
    teacher_id INT
);

2. Create the role groups students and teachers.

CREATE ROLE students;
CREATE ROLE teachers;

3. Give the teachers group rights to add and modify data in the exams table.

GRANT INSERT, UPDATE ON exams TO teachers;

4. Give the students group read-only access.

GRANT SELECT ON exams TO students;

5. Create a user alice and add her to the students group.

CREATE ROLE alice WITH LOGIN PASSWORD 'alicePassword';
GRANT students TO alice;

6. Check what privileges alice has by logging in with her account.

Now you know how to create roles, manage their privileges, and make admin life easier with group roles. In the next lecture, we'll dive into how to set up access using GRANT and REVOKE for more fine-grained control at the database, schema, and table level.