Column-level permissions in PostgreSQL - Mini Project: Build & Apply

Column-level permissions in PostgreSQL

📖 Scenario: You are managing a company database where sensitive employee information must be protected. You want to allow a user to see only certain columns of the employees table, such as their names and departments, but not their salaries.

🎯 Goal: Create a PostgreSQL setup where a user has permission to select only specific columns from the employees table, demonstrating column-level permissions.

📋 What You'll Learn

Create a table called employees with columns id, name, department, and salary.

Create a role called limited_user.

Grant SELECT permission on only the name and department columns of the employees table to limited_user.

Revoke any other SELECT permissions on the employees table from limited_user.

💡 Why This Matters

🌍 Real World

Column-level permissions help protect sensitive data in databases by restricting access to only necessary information for certain users.

💼 Career

Database administrators and developers often need to implement fine-grained access control to comply with privacy laws and company policies.

1

Create the employees table

Create a table called employees with columns id as integer primary key, name as text, department as text, and salary as integer.

PostgreSQL

# Your code here

Need a hint?

Use CREATE TABLE with the specified columns and types.

2

Create the role limited_user

Create a role called limited_user without login privileges.

PostgreSQL

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary INTEGER
);

# Your code here

Need a hint?

Use CREATE ROLE limited_user NOLOGIN; to create the role.

3

Grant column-level SELECT permissions

Grant SELECT permission on only the name and department columns of the employees table to the role limited_user.

PostgreSQL

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary INTEGER
);

CREATE ROLE limited_user NOLOGIN;

# Your code here

Need a hint?

Use GRANT SELECT (name, department) ON employees TO limited_user; to grant column-level permissions.

4

Revoke other SELECT permissions

Revoke any other SELECT permissions on the employees table from limited_user to ensure only the specified columns are accessible.

PostgreSQL

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary INTEGER
);

CREATE ROLE limited_user NOLOGIN;

GRANT SELECT (name, department) ON employees TO limited_user;

# Your code here

Need a hint?

Use REVOKE SELECT ON employees FROM limited_user; to remove full table select permissions.