Implementing access control for teams

In this chapter, you learned how to control access to your databases. This brings us to the final exercise in the course. You will create a new schema and table for holding data that will be provided to data scientists on your team. While your team members are likely responsible individuals, accidents can happen. You should only give these team members as much control over the database as required to do their job. These team members will have access to data on loans that have not been approved.

The schema analysis will be created.
The table unapproved_loan will be defined in this new schema.
User data_scientist will be created.
The user will be restricted to reading from the new table.

This exercise will reinforce the basics of access control for a database.

This exercise is part of the course

Creating PostgreSQL Databases

View Course

Exercise instructions

Create a new schema named analysis which will hold our new table.
Create a new table unapproved_loan within the analysis schema with a PRIMARY KEY (id), a FOREIGN KEY (loan_id) referencing the id key of the loan table, and a description TEXT field representing why the loan was rejected.
Add a user named data_scientist with password changeme.
Restrict the user to have read-only access to the analysis.unapproved_loan table.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Create the new analysis schema
CREATE SCHEMA ___;

-- Create a table unapproved loan under the analysis schema
CREATE TABLE ___ (
    id serial PRIMARY KEY,
    loan_id INTEGER REFERENCES loan(id),
    description ___ NOT NULL
);

-- Create 'data_scientist' user with password 'changeme'
___ USER ___ WITH ___ '___';

-- Give 'data_scientist' ability to use 'analysis' schema
GRANT ___ ON ___ ___ TO ___;

-- Grant read-only access to table for 'data_scientist' user
___ SELECT ON ___ TO ___;

Edit and Run Code