Microservice architecture states for one service has one database. However, the database administrator would like to have one credential to access multiple databases. Let’s take a look at how we can solve this with PostgreSQL roles.

Configuring database user for service

Each service can have credentials to access the database. E.g. service account can use user account_service with password '<SECRET_PASSOWRD>' has access to the account_service_db database.

Create a new user for account service:

1

CREATE USER account_service PASSWORD '<SECRET_PASSOWRD>'; 

Grant access to the database tables and sequences:

1
2
3

GRANT CONNECT ON DATABASE account_service_db TO account_service;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO account_service;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO account_service;

Configuring role to access multiple databases services

An administrator can have credentials to access multiple databases. E.g. user jhon_the_admin with password '<ADMIN_SECRET_PASSOWRD>' has access to the account_service, aaa_service_db and yyy_service.

Create the admin group:

1

CREATE ROLE admin_group;

Link service roles to the admin_group:

1
2
3

GRANT account_service TO admin_group;
GRANT aaa_service TO admin_group;
GRANT yyy_service TO admin_group;

Create the admin user:

1

CREATE USER jhon_the_admin WITH PASSWORD '<ADMIN_SECRET_PASSOWRD>';

Link the admin_group role to the admin user:

1

GRANT admin_group TO jhon_the_admin;