2

My application is a multi-tenant one. I am creating a single database per tenant/user. So far, I was creating all the databases with the following command from c# code.

CREATE DATABASE @userDBName WITH OWNER = @dbOwner ENCODING = 'UTF8';

This way I see that all databases have got the same owner, and that owner has got access to all tables within all databases.

Now, according to the new security requirement, what we want to implement is that every database should be accessible by only a single user, and user of one database should not have access to the other database.

As, I am dealing with the concepts of roles/users in Postgres for the first time, I am unable to find luck in whatever I have done so far.

I created users, and tried granting them access on a single database

CREATE USER test WITH ENCRYPTED PASSWORD '123456';
GRANT ALL PRIVILEGES ON DATABASE userdb1 TO test;

Now, when in my connection string in c# I give the user Id='test' and Password as above, I get permission denied error.

Also, from PgAdmin, when I go to the database, and try to change the owner to the new user I have created above, I get this error

ERROR:  must be member of role test

What exactly needs to be done? I have explored all possible solutions on the internet, and tried almost everything, but nothing is helping out much.

puneet
  • 492
  • 1
  • 8
  • 25

2 Answers2

1

That's pretty simple.

Connect to the database as superuser, then run (assuming newowner is the role that should own the database):

REASSIGN OWNED BY oldowner TO newowner;
REVOKE CONNECT, TEMPORARY ON DATABASE mydb FROM PUBLIC;

The error you get is because you are not a superuser.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • "REASSIGN OWNED BY oldowner TO newowner;" would do it across all databases for the oldowner. I can't do that before I test it works successfully. Also, Do i need to run this command from the Postgres Database? – puneet Feb 06 '19 at 11:12
  • It will only affect objects in the database you are connected to. You can run it from the query tool if you really like pgAdmin. – Laurenz Albe Feb 06 '19 at 11:22
  • I am using Azure's PostgreSql as a Service. It doesn't allow you to create a super user yourself. It has its own super user (azure_superuser), password of which is not known – puneet Feb 06 '19 at 11:34
  • Then you have to do it object for object. Or get better hosting. – Laurenz Albe Feb 06 '19 at 11:36
0

While not directly related to your question of how to create new databases. You may want to re-consider the approach of one database per customer, there are some issues that can arise as you scale to thousands of tenants - https://www.citusdata.com/blog/2018/06/28/scaling-from-one-to-one-hundred-thousand-tenants/

CraigKerstiens
  • 5,906
  • 1
  • 25
  • 28