Postgres setup for multiple projects
1
vote
0
answers
494
views
I have a Postgres db server setup on my raspberry pi. With this I am working on two distinct projects:
- Project1
- Project2
For these projects I have different users and the users of each should not be able to see the data of the other project. What i did to enable this was create two databases on the server:
- project1_db
- project2_db
So my setup on pgadmin4 looks like:
=> Servers
=====> Projects
==========>project1_db
==========>project2_db
I also created two users:
- project1_user
- project2_user
I made each of these users the owners of their respective db. By doing this i expected when logged in as project1_user to not be able to see project2_db info (basically be told when clicking the db in pgadmin4 that i dont have permissions). However, I was still able to click in and then dig into schema/public/tables. I noticed when looking at privileges for the db in pgAdmin4 that PUBLIC kept being added even after I deleted it - and my understanding is that this makes it viewable by all.
So my questions are:
1. Is this server/db setup appropriate for what im trying to achieve? If it is, why am i not getting expected privileges?
2. Is my user setup appropriate / what is the correct user roles setup to use to achieve my goal? And how does the PUBLIC role tie into this?
3. Am I missing something obvious / or do i have misunderstanding of the tools?
UPDATE: I just found this https://dba.stackexchange.com/questions/108975/postgresql-who-or-what-is-the-public-role on the public role. Which i guess answers the question on public.
I also realized that after I was making updates I was only refreshing my databases not the whole server so the changes werent being pushed down. After I removed public access and refreshed the server the permissions worked as expected. I am now getting my expected behavior.
Asked by matthewmturner
(121 rep)
Mar 18, 2020, 01:03 AM
Last activity: Mar 18, 2020, 07:49 AM
Last activity: Mar 18, 2020, 07:49 AM