Hierarchical access control

Creare database PostgreSQL

Darryl Reeves

Industry Assistant Professor, New York University

Access control with schemas

  • Schema - named container for db objects
  • Schemas can be used for access control
Creare database PostgreSQL

Example: schema use in finances database

  • Spouse access to finances database
  • public schema used by default
  • Two new schemas: me and spouse
CREATE SCHEMA me;

CREATE SCHEMA spouse;

CREATE TABLE me.account (...);

CREATE TABLE spouse.account (...);
Creare database PostgreSQL

Granting schema privileges

CREATE USER better_half WITH PASSWORD 'changeme';

GRANT USAGE ON SCHEMA spouse TO better_half;

GRANT USAGE ON SCHEMA public TO better_half;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA spouse;

TO better_half;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public

TO better_half;

Schema-based access control implemented

Creare database PostgreSQL

Using groups

  • Group - a type of role that identifies one or more users
  • Access control can be applied at group level
CREATE GROUP family;

GRANT USAGE ON SCHEMA public TO family;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA

public TO family;

ALTER GROUP family ADD USER fin
ALTER GROUP family ADD USER better_half;
Creare database PostgreSQL

Shared and individual data access

  • Shared schema access enabled to public schema
  • Individual schemas control data access
Creare database PostgreSQL

Let's practice!

Creare database PostgreSQL

Preparing Video For Download...