What is the difference between user and role in Postgres?

In PostgreSQL, the core difference between a user and a role is that a user is simply a role that possesses the LOGIN attribute, allowing it to connect to the database. All users are roles, but not all roles are users.

PostgreSQL's security model is built around the concept of roles. A role is a high-level entity that can represent either a database user or a group of database users.

Understanding PostgreSQL Roles

A role is a fundamental security identity in PostgreSQL. Roles can:

• Own database objects: Such as tables, databases, functions, and sequences.
• Be granted privileges: Permissions like SELECT, INSERT, UPDATE, DELETE on specific objects.
• Act as a container for other roles: This allows for efficient management of permissions by grouping related roles together. For instance, you can grant a set of permissions to a "developers" role, and then grant the "developers" role to individual users, who will then inherit those permissions.

Think of a role as a general account that can hold privileges and ownerships.

Understanding PostgreSQL Users

A user in PostgreSQL is a specific type of role. It's a role that has been granted the LOGIN attribute. This attribute is crucial because it allows the role to authenticate and establish a connection to the PostgreSQL server.

Without the LOGIN attribute, a role cannot directly connect to the database. Such roles are typically used purely for grouping other roles or for holding a set of shared permissions that can then be granted to login-enabled roles.

For example, a role named app_read_only might be created to encapsulate SELECT privileges on specific tables. Individual users could then be granted membership in app_read_only to gain those permissions, without app_read_only itself being able to log in.

Key Distinctions and Similarities

While closely related, the distinction is important for managing database security and access.

Feature	Role (General)	User (Specific Type of Role)
Login Ability	No, unless explicitly granted LOGIN attribute.	Yes, by definition, possesses the LOGIN attribute.
Purpose	Can be a group, hold permissions, own objects.	Primarily for logging in and interacting with the database.
Creation	CREATE ROLE role_name; (defaults to NOLOGIN)	CREATE USER user_name; (shorthand for CREATE ROLE ... LOGIN;)
Membership	Can be a member of other roles or have other roles as members.	Can be a member of other roles to inherit permissions.

Practical Implications and Examples

PostgreSQL's unified role system simplifies administration by treating users and groups as the same type of entity.

Creating Roles and Users

• Creating a role that cannot log in (useful for grouping permissions):

  CREATE ROLE data_analysts;
  GRANT SELECT ON all_sales_data TO data_analysts;

  This data_analysts role can now be granted to actual users.

• Creating a user (a role with LOGIN ability):

  CREATE USER jane_doe WITH PASSWORD 'secure_password';

  This is equivalent to:

  CREATE ROLE jane_doe WITH LOGIN PASSWORD 'secure_password';

  Jane can now connect to the database.

• Granting a role to a user (group membership):

  GRANT data_analysts TO jane_doe;

  Now, jane_doe automatically inherits all privileges granted to data_analysts.

For more details on creating and managing roles, refer to the official PostgreSQL documentation on CREATE ROLE.

Common Role Attributes

Roles (and thus users) can be created with various attributes that define their capabilities:

• LOGIN / NOLOGIN: Determines if the role can log in. CREATE USER implicitly sets LOGIN. CREATE ROLE implicitly sets NOLOGIN.
• SUPERUSER / NOSUPERUSER: Grants or revokes all database privileges. Superusers bypass all permission checks.
• CREATEDB / NOCREATEDB: Allows or disallows the role to create new databases.
• CREATEROLE / NOCREATEROLE: Allows or disallows the role to create, modify, or drop other roles.
• INHERIT / NOINHERIT: Determines if a role automatically inherits privileges from roles it is a member of. INHERIT is the default.
• REPLICATION / NOREPLICATION: Allows the role to initiate streaming replication or take backups.
• BYPASSRLS / NOBYPASSRLS: Allows the role to bypass Row Level Security policies.

It's worth noting that some cloud providers, like Google Cloud SQL, create all roles with the LOGIN attribute by default. In such environments, the terms "role" and "user" are often used interchangeably because all roles created through their interfaces inherently have the ability to log in.