A database holds data that not everyone may see or change: salaries, emails, payments. The DBMS must know who sends each statement, and what they may do.

Roles

In PostgreSQL, every connection acts as a role. A role is a name that owns objects and holds privileges.

CREATE ROLE analyst LOGIN PASSWORD 'change-me';

• A role with LOGIN can connect. It is what other systems call a user. CREATE USER analyst; is the same as CREATE ROLE analyst LOGIN;.
• A role without LOGIN cannot connect. It holds privileges for other roles (Lesson 6).
• DROP ROLE analyst; removes a role.

A new role can connect, but it cannot read any table yet. The next lesson gives it privileges.

Who am I?

current_user returns the role that your statements run as:

SELECT current_user;

In the labs you connect as postgres, a superuser. A superuser passes every privilege check. Use a superuser to set up the database, and never give it to an application.

List the roles

pg_roles is a view of the system catalog with one row for each role:

SELECT rolname, rolcanlogin, rolsuper FROM pg_roles;

The roles whose names start with pg_ come with PostgreSQL.