Date: 2019-03-19
Time: 09:30–17:30
Room: Training Room -2
Level: Intermediate
Feedback: Leave feedback
This tutorial will be broken down into three main pieces, which are Authentication, Authorization, and Row Level Security.
Advanced Authentication
PostgreSQL supports a number of different authentication mechanisms and while many of them are quite simple and easy to use, the enterprise-level authentication systems require setup beyond PostgreSQL and a deeper understanding of how authentication works.
This section will cover all of the authentication mechanisms supported by PostgreSQL from SCRAM to LDAP, and go over implementing the two most prevelant enterprise authentication schemes- Kerberos/GSSAPI (used extensively by universities and businesses, and is the authentication system for Microsoft Windows) and Client-Side Certificates / SSL (used by many governments and high security systems). We will go into detail on how to integrate PostgreSQL into these enterprise authentication schemes, and cover the different options and limitations across the authentication mechanisms.
Authorization in PostgreSQL - Deciphering GRANT
PostgreSQL has a ton of different objects, containers, and permissions that can be granted on them (either explicitly as individual grants, or implicitly as part of another action).
We'll review all of the objects in PostgreSQL and what the various options are for controlling access to those objects, from schemas and tables, through views which enforce row-based controls, down to sequences and functions. We'll also discuss how to manage roles, and rights through the use of default permissions on objects.
Row Level Security
PostgreSQL has long had a complex and interesting set of permissions available through the GRANT system. There is another system which exists in many other RDBMS's known as row-level security (RLS), where the rows returned are filtered based on a policy implemented on the table, and only rows passing a certain policy are allowed to be added to the table.
In this section we'll review RLS as implemented in PostgreSQL, provide examples and use-cases, and go over how RLS can be used by applications either through the use of roles or through other means such as with server-side variables.