PostgreSQL vs. SQL Server: Security Model Differences
summary
Compares concrete differences: Postgres uses cluster-level roles with database-scoped grants while SQL Server separates logins and users; demo uses Docker Postgres and Visual Studio Code to create devlogin/readlogin roles, NOLOGIN defaults, and group roles, then shows errors when a role lacks permissions in a second database. Shows using ALTER DEFAULT PRIVILEGES to auto-grant future object rights, notes Postgres v15 removed public CREATE, and provides five deployment options for multi-database setups.
AI-written summary · about
chapters (9)
- 0:00Music & introduction
- 1:49Definitions: authentication vs authorization, login, user, schema, superuser
- 5:03SQL Server roles & Postgres clusters
- 8:14Postgres roles explained
- 10:00Comparing SQL Server and Postgres permissions
- 12:56Essential role attributes and public access
- 16:48Object ownership and default privilege pitfalls
- 19:52Live demo of roles and permission grants
- 26:40Building scalable Postgres security models
related talks
40mPostgreSQL Security for Application Developers
27mFor Your Eyes Only: Roles, Privileges, and Security in PostgreSQL
50mPostgreSQL Security. How Do We Think?
47mUntangling the Web of PostgreSQL Permissions
42mData Security using Roles, Privileges and Row Level Security
07mAll about Postgres security
46mPostgres for the SQL Server DBA
32mEnhancing Security in PostgreSQL