Postgres manages database access permissions using the concept of roles. You can use roles to give access to specific people, systems, or applications. You can also use roles to manage a set of permissions that you then grant to more specific roles or users. You can further restrict access to specific data by using Row Level Access (RLS).

​
Users vs roles

In PostgreSQL, roles can function as users or groups of users. Users are roles with login privileges, while groups (also known as role groups) are roles that don’t have login privileges but can be used to manage permissions for multiple users.

​
Creating roles

You can create a role using the create role command:

create role "role_name";

​
Creating users

Roles and users are essentially the same in Postgres, however if you want to use password-logins for a specific role, then you can use WITH LOGIN PASSWORD:

create role "role_name" with login password 'extremely_secure_password';

​
Passwords

Your Postgres database is the core of your Hydra project, so it’s important that every role has a strong, secure password at all times. Here are some tips for creating a secure password:

  • Use a password manager to generate it.
  • Make a long password (12 characters at least).
  • Don’t use any common dictionary words.
  • Use both upper and lower case characters, numbers, and special symbols.

​
Special symbols in passwords

If you use special symbols in your postgres password, you must remember to percent-encode your password later if using the postgres connection string, for example: postgresql://postgres.projectref:p%3Dword@...com:5432/postgres

​
Granting Permissions

Roles can be granted various permissions on database objects using the GRANT command. Permissions include SELECT, INSERT, UPDATE, and DELETE. You can configure access to almost any object inside your database - including tables, views, functions, and triggers.

​
Revoking permissions

Permissions can be revoked using the REVOKE command:

REVOKE permission_type ON object_name FROM role_name;

​
Role hierarchy

Roles can be organized in a hierarchy, where one role can inherit permissions from another. This simplifies permission management, as you can define permissions at a higher level and have them automatically apply to all child roles.

​
Role inheritance

To create a role hierarchy, you first need to create the parent and child roles. The child role will inherit permissions from its parent. Child roles can be added using the INHERIT option when creating the role:

create role "child_role_name" inherit "parent_role_name";

​
Preventing inheritance

In some cases, you might want to prevent a role from having a child relationship (typically superuser roles). You can prevent inheritance relations using NOINHERIT:

alter role "child_role_name" noinherit;

​
Further Reading

User ManagementScaling