PostgreSQL Roles

How to Create A Role

INFO

For PostgreSQL less than 15, every database that is created has a default schema called public and on that schema a default GRANT of the CREATE privilege. For versions of PostgreSQL greater than or equal to 15 this is no longer the case. The CREATE privilege is revoked by default except for the owner of that database. The USAGE privilege is still granted to public, however.

Roles

Why use Roles at all? Roles allow administrators more granularity in defining privileges for individuals that have access to database objects. Roles come in two flavors: Group Roles and User Roles. What sets the Group Role from the User Role is that User Roles have the LOGIN privilege complete with a password. Group Roles are meant to create an umbrella of Privileges for user roles. It is standard to not assign privileges to User Roles. Instead we create a group role like data engineer or developer and then grant the group role TO a User Role.

CREATE

For databases, allows new schemas and publications to be created within the database, and allows trusted extensions to be installed within the database. For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and have this privilege for the containing schema. For table-spaces, allows tables, indexes, and temporary files to be created within the table-space, and allows databases to be created that have the table-space as their default table-space. Note that revoking this privilege will not alter the existence or location of existing objects.

Creating a Role

Creating a Role in PostgreSQL is simple enough:

-- INHERIT IS A DEFAULT BUT IS INCLUDED HERE FOR CLARITY
CREATE ROLE de_user LOGIN PASSWORD '**************' INHERIT;
CREATE ROLE data_engineer NOLOGIN INHERIT; -- Group Role
CREATE ROLE bronze_rw NOLOGIN INHERIT; -- Group Role

-- Assigning Privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA bronze TO bronze_rw;
GRANT USAGE  ON SCHEMA bronze to bronze_rw;
ALTER DEFAULT PRIVILEGES IN SCHEMA bronze GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO bronze_rw;

-- Grant all privileges of `bronze_rw` to `data_engineer`
GRANT bronze_rw TO data_engineer;
GRANT data_engineer TO de_user;

The roles data_engineer and bronze_rw are Group Roles. After creating a Group Role you will need to grant the Group Role some privileges. This will be determined by your use-case. In this example, a Data Engineer should have read access and write access to all tables in the bronze schema.