Sample Header Ad - 728x90

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