Login vs group roles in PostgreSQL - Hands-On Comparison

Understanding Login vs Group Roles in PostgreSQL

📖 Scenario: You are managing a small company's database. You need to set up users and groups to control access to the database resources properly.This project will help you understand how to create individual login roles (users) and group roles, and how to assign users to groups in PostgreSQL.

🎯 Goal: Build a simple PostgreSQL role structure with one login role and one group role. Then assign the login role to the group role to manage permissions efficiently.

📋 What You'll Learn

Create a login role named alice with a password

Create a group role named managers

Grant the managers group role to the alice login role

Verify the role membership setup

💡 Why This Matters

🌍 Real World

Database administrators use login and group roles to manage user access and permissions efficiently in real companies.

💼 Career

Understanding roles and permissions is essential for database security and user management in many IT and data jobs.

1

Create a login role named alice

Write a SQL command to create a login role called alice with the password 'securepass'. Use CREATE ROLE alice LOGIN PASSWORD 'securepass';

PostgreSQL

-- Your code here to create login role alice

Need a hint?

Use CREATE ROLE with LOGIN and PASSWORD options.

2

Create a group role named managers

Write a SQL command to create a group role called managers without login privileges. Use CREATE ROLE managers NOLOGIN;

PostgreSQL

CREATE ROLE alice LOGIN PASSWORD 'securepass';
-- Your code here to create group role managers

Need a hint?

Group roles do not have login rights, so use NOLOGIN.

3

Grant the group role managers to the login role alice

Write a SQL command to grant the group role managers to the login role alice. Use GRANT managers TO alice;

PostgreSQL

CREATE ROLE alice LOGIN PASSWORD 'securepass';
CREATE ROLE managers NOLOGIN;
-- Your code here to grant managers to alice

Need a hint?

Use GRANT group_role TO login_role; to assign group membership.

4

Verify the role membership of alice

Write a SQL query to check the roles that alice is a member of. Use SELECT rolname FROM pg_roles WHERE pg_has_role('alice', oid, 'member');

PostgreSQL

CREATE ROLE alice LOGIN PASSWORD 'securepass';
CREATE ROLE managers NOLOGIN;
GRANT managers TO alice;
-- Your code here to verify alice's group membership

Need a hint?

Use the pg_has_role function to check role membership.