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:
- Is this server/db setup appropriate for what im trying to achieve? If it is, why am i not getting expected privileges?
- 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?
- Am I missing something obvious / or do i have misunderstanding of the tools?
UPDATE: I just found this 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.
