Hi:
psql (9.6.7, server 11.3) on linux
What are the possibilities regarding restricting user access to records given this scenario.
I have a DB with tables that are organized in a hierarchical way. For example, a "projects" table is the parent of >1 recs in a "domains" table (PK/FK setup), which is in turn the parent of >1 rec in the "tasks" table. Etc... .
In linux-world, we have a body of users, each belonging to one or more linux group. Each group has a counterpart in the projects table (project "A" is controlled by linux group "agrp", etc...). User should be able to work with records in the DB if they are in that data's group but have no access to data in groups for which they do not belong.
Users are going to be working with data through perl/DBI scripts which currently connect using a generic role with hardcoded password in the connect string. Access will be select/insert/update/delete We need to tighten up security as described above.
What we don't want is users having to enter passwords in at a prompt. They already did that when they logged in and the linux groups they were assigned identify what data they can access. And we don't want to segregate the data into tables based on project.
Is there a way to do this?