PostgreSQL vs. SQL Server: Security Model Differences

Taiob Ali compares PostgreSQL and SQL Server security models, focusing on how each platform handles authentication vs. authorization, roles, and permissions, and what those differences mean for building secure, maintainable database access patterns.

Overview

This session compares how SQL Server and PostgreSQL approach database security, with emphasis on practical differences that matter when moving between the two systems.

What the talk covers

• Definitions and core concepts used throughout the comparison:
  • Authentication vs. authorization
  • Login, user, schema, superuser
• SQL Server roles and how they relate to server/database scope
• PostgreSQL roles and how PostgreSQL uses a unified role-based model for authentication and authorization
• Permissions comparison between SQL Server and PostgreSQL
• Role attributes and the implications of public access
• Object ownership and common pitfalls around default privileges
• A live demo showing roles and permission grants
• Guidance on building scalable PostgreSQL security models

Examples of differences highlighted

• SQL Server distinguishes between logins and users, while PostgreSQL uses a unified role concept.
• SQL Server includes predefined server and database roles (for example, sysadmin), while PostgreSQL includes default roles such as pg_read_all_data.
• Both systems support custom roles, but PostgreSQL emphasizes permission inheritance and role hierarchies.

Video chapters

• 00:00 – Music & introduction
• 01:49 – Definitions: authentication vs authorization, login, user, schema, superuser
• 05:03 – SQL Server roles & Postgres clusters
• 08:14 – Postgres roles explained
• 10:00 – Comparing SQL Server and Postgres permissions
• 12:56 – Essential role attributes and public access
• 16:48 – Object ownership and default privilege pitfalls
• 19:52 – Live demo of roles and permission grants
• 26:40 – Building scalable Postgres security models

Links

• POSETTE conference site: https://posetteconf.com
• POSETTE talks playlist: https://aka.ms/posette-playlist