PostgreSQL User Creation and Least Privilege Role Assignment
Introduction In this article, I will guide you on how to create an account and apply role assignment following the Principle of Least Privilege, in order to grant only the exact permissions that the application needs. Detail To create a new user, you use -- CREATE USER {username} WITH PASSWORD '{password}'; CREATE USER backend_user WITH PASSWORD 'backend_password' ; -- CREATE ROLE {username} LOGIN PASSWORD '{password}'; CREATE ROLE backend_user LOGIN PASSWORD 'backend_password' ; Here, using CREATE USER is the old syntax, which supports LOGIN by default and it acts as an alias for CREATE ROLE In newer versions of Postgres, you should use CREATE ROLE because it is more flexible If used with LOGIN , it creates a user Without LOGIN , it creates a group role When newly created, the user will by default have no permissions to SELECT data INSERT UPDATE DELETE CREATE TABLE CREATE FUNCTION DROP TABLE EXECUTE function USAGE on schema Instead, they can o...