PostgreSQL Role and Permission Management

Introduction

In this lab, you will explore PostgreSQL role and permission management. The primary goal is to learn how to create roles with specific attributes and manage database privileges.

You will begin by creating a role named reporting_role with the NOLOGIN attribute, preventing it from directly connecting to the database. Then, you'll verify the role's creation. Subsequent steps will cover granting and testing database privileges, switching users to verify access, and revoking privileges from a role.

Skills Graph

Create a Role

In this step, you will learn how to create a role in PostgreSQL. Roles are used to manage database access and permissions.

1. Connect to the PostgreSQL server:

   Open a terminal and connect to the PostgreSQL server using the psql command-line tool as the postgres user.

   sudo -u postgres psql

   You should see a prompt similar to:

   postgres=#

2. Create the reporting_role role:

   Now, let's create the reporting_role using the CREATE ROLE command. We will specify the NOLOGIN attribute, which means this role cannot be used to directly connect to the database. It will only be used to grant permissions to other roles or users.

   CREATE ROLE reporting_role NOLOGIN;

   This command creates the role. You should see the following output:

   CREATE ROLE

3. Verify the role creation:

   To verify that the role has been created, you can use the \du command (short for \list users). This command lists all roles in the database.

   \du

   You should see reporting_role in the list of roles. The output will look something like this:

                                    List of roles
      Role name    |                         Attributes                         | Member of
   ----------------+------------------------------------------------------------+-----------
    labex          | Superuser, Create role, Create DB, Replication, Bypass RLS| {}
    postgres       | Superuser, Create role, Create DB                         | {}
    reporting_role | Cannot login                                               | {}

   Exit the psql shell.

   \q

Grant Database Privileges

In this step, you will learn how to grant database privileges to a role. We'll build upon the reporting_role created in the previous step.

1. Connect to the PostgreSQL server:

   Connect to the PostgreSQL server using the psql command-line tool as the postgres user.

   sudo -u postgres psql

2. Create a database:

   Let's create a database named reporting_db that our reporting_role will have access to.

   CREATE DATABASE reporting_db;

   You should see the following output:

   CREATE DATABASE

3. Connect to the reporting_db database:

   Connect to the newly created database:

   \c reporting_db

   The prompt should change to reporting_db=#.

4. Create a table:

   Let's create a simple table named sales_data within the reporting_db database.

   CREATE TABLE sales_data (
       id SERIAL PRIMARY KEY,
       product VARCHAR(255),
       amount DECIMAL
   );

   You should see the following output:

   CREATE TABLE

5. Grant SELECT privilege on the sales_data table to the reporting_role:

   Now, we will grant the SELECT privilege on the sales_data table to the reporting_role. This will allow any role or user that is a member of reporting_role to read data from the sales_data table.

   GRANT SELECT ON sales_data TO reporting_role;

   You should see the following output:

   GRANT

   Exit the psql shell.

   \q

Create User and Test Privileges

In this step, you will create a user and grant it membership to the reporting_role to test the privileges.

1. Connect to the PostgreSQL server:

   Connect to the PostgreSQL server using the psql command-line tool as the postgres user.

   sudo -u postgres psql

2. Connect to the reporting_db database:

   Connect to the reporting_db database:

   \c reporting_db

3. Create a user and grant membership to the reporting_role:

   Let's create a new user named report_user with a password (replace your_password with a strong password). Then, we'll grant this user membership to the reporting_role. This means report_user will inherit the privileges granted to reporting_role.

   CREATE USER report_user WITH PASSWORD 'your_password';
   GRANT reporting_role TO report_user;

   You should see the following output:

   CREATE ROLE
   GRANT

4. Insert data into the sales_data table:

   Insert some sample data into the sales_data table for testing purposes.

   INSERT INTO sales_data (product, amount) VALUES ('Laptop', 1200.00), ('Mouse', 25.00), ('Keyboard', 75.00);

   You should see the following output:

   INSERT 0 3

   Exit the psql shell.

   \q

5. Connect to the reporting_db database as the report_user:

   Open a new terminal window and use the following command:

   psql -d reporting_db -U report_user -W

   You will be prompted for the password you set for report_user. Enter it.

6. Test the privileges:

   Now, try to select data from the sales_data table:

   SELECT * FROM sales_data;

   You should see the data you inserted:

    id | product  | amount
   ----+----------+--------
     1 | Laptop   | 1200.00
     2 | Mouse    |   25.00
     3 | Keyboard |   75.00
   (3 rows)

   If you see this output, it means the report_user has the SELECT privilege on the sales_data table, which was granted through the reporting_role.

   Exit the psql shell.

   \q

Revoke Privileges

In this step, you will revoke the SELECT privilege from the reporting_role and verify that the report_user no longer has access to the sales_data table.

1. Connect to the PostgreSQL server as the postgres user:

   Switch back to the terminal where you are connected as the postgres user. Connect to the PostgreSQL server using the psql command-line tool as the postgres user.

   sudo -u postgres psql

2. Connect to the reporting_db database:

   Connect to the reporting_db database:

   \c reporting_db

3. Revoke the SELECT privilege from the reporting_role:

   Now, we will revoke the SELECT privilege on the sales_data table from the reporting_role.

   REVOKE SELECT ON sales_data FROM reporting_role;

   You should see the following output:

   REVOKE

   Exit the psql shell.

   \q

4. Connect to the reporting_db database as the report_user:

   Switch back to the terminal where you are connected as the report_user.

   psql -d reporting_db -U report_user -W

5. Verify access denial as report_user:

   As the report_user, run the SELECT query again to verify that you no longer have access to the data in the sales_data table:

   SELECT * FROM sales_data;

   You should see an error message similar to:

   ERROR:  permission denied for table sales_data

   This confirms that the report_user no longer has SELECT privileges on the sales_data table because the privilege was revoked from the reporting_role.

   Exit the psql shell.

   \q

Summary

In this lab, you have learned how to create roles, grant privileges, create users, assign roles to users, and revoke privileges in PostgreSQL. These are fundamental concepts for managing database security and access control.