Handling Users and roles to Secure PostgreSQL single RDS instance for multitenancy database

I run into a scenario where I needed to deploy a multitenant application in a single RDS instance.

The application work find as has a master database that manage all connection string and attribute for each Tenant.

Then a tenant decided to use this application but wanted to have his own instance of the application source code. The database will remain in the same instance in RDS.

I won’t talk about the different way you can architect or design your solution, but I will target on the way you can handle your database security.

Let me say, depending on your business, some client would not even like the fact that they share the same instance with their competitors.

The database chosen is Aurora PostgreSQL, due to the flexibility and price proposed by AWS.

I start looking at how limit Access to each database. If still not clear, using one user to access all your databases create a security risk, in this scenario.

Yes, I first uses IAM authentication with I explained in my previous post and I still believe this is the safest way for anyone.

However my customer wanted to connect from time to time with a third party tool to his own database: reason I came up with this scenario.

Postgresql is good but sometime confusing when it come to users and roles.

Permissions must be granted at the database, schema, and schema object level. 

This is 3 steps scenario how you will go around it.

• Step1: Create a role

Assuming the databases are  dbA, dbB,…

You need to run:

Create role role_dbA;
Create role role_dbB;

• Step2: Assign Permission

We need to grant necessary permission to each role. Depending on your need you may or may not need all the below:

GRANT CONNECT ON DATABASE "dbA" TO role_dbA;
GRANT CONNECT ON DATABASE "dbA" TO role_dbB;
...
GRANT USAGE ON SCHEMA public TO role_dbA;
GRANT ALL ON ALL TABLES IN SCHEMA "public" TO role_dbA;

You can also use different schemas per customer but i found that that was not necessary the cleaner way when it come to maintenance

…

Hopefully, this article will help you understand better and solve your question to how to limit user access in PostgreSQL.