Schema privileges

The new software development team members are eager to get started on the loan management project. But Sandra, as the team lead, is not comfortable with so many people having direct access to the production version of the loan database (in the public schema). The rapidly growing team must implement some software development best practices which include setting up a development environment that is separated from the production environment. In this exercise, you will create a new schema to represent this development environment and grant members of the software development team privileges on this schema.

This exercise is part of the course

Creating PostgreSQL Databases

View Course

Exercise instructions

Create a new schema named development.
Grant the usage privilege on the development schema to dev_team.
Create a version of the loan table under the development schema.
Grant SELECT, INSERT, UPDATE, and DELETE privileges to the dev_team group for all development schema tables.

Hands-on interactive exercise

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

-- Create the development schema
___ ___ ___;

-- Grant usage privilege on new schema to dev_team
___ ___ ON SCHEMA ___ TO ___;

-- Create a loan table in the development schema
___ ___ ___.___ (
	borrower_id INTEGER,
	bank_id INTEGER,
	approval_date DATE,
	program text NOT NULL,
	max_amount DECIMAL(9,2) NOT NULL,
	gross_approval DECIMAL(9, 2) NOT NULL,
	term_in_months SMALLINT NOT NULL,
	revolver_status BOOLEAN NOT NULL,
	bank_zip VARCHAR(10) NOT NULL,
	initial_interest_rate DECIMAL(4, 2) NOT NULL
);

-- Grant privileges on development schema
___ ___, ___, ___, ___ ON ALL TABLES IN SCHEMA ___ TO ___;

Edit and Run Code