Postgres Pro
Downloads
Home   >   mailing lists

Thread: Securing records using linux grou permissions

Securing records using linux grou permissions

From
David Gauthier
Date:
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?  
 

Re: Securing records using linux grou permissions

From
Michael Lewis
Date:
It sounds like you want row level security-

But, you will need to define separate roles on the database and ensure that the users and connecting with separate roles. The db can't magically know about the permissions on the OS side.

Re: Securing records using linux grou permissions

From
David Gauthier
Date:
Ok, thanks. 
I was hoping there was a way to integrate the user/permissions/groups in linux with the PG permissions functionality. 

On Tue, Oct 15, 2019 at 12:32 PM Michael Lewis <mlewis@entrata.com> wrote:
It sounds like you want row level security-

But, you will need to define separate roles on the database and ensure that the users and connecting with separate roles. The db can't magically know about the permissions on the OS side.

Re: Securing records using linux grou permissions

From
Ron
Date:
It can be done, but you'd need much tighter integration with the OS, which would probably lock you in to only one platform ("Unix", not just Linux).

On 10/15/19 12:10 PM, David Gauthier wrote:
Ok, thanks. 
I was hoping there was a way to integrate the user/permissions/groups in linux with the PG permissions functionality. 

On Tue, Oct 15, 2019 at 12:32 PM Michael Lewis <mlewis@entrata.com> wrote:
It sounds like you want row level security-

But, you will need to define separate roles on the database and ensure that the users and connecting with separate roles. The db can't magically know about the permissions on the OS side.

--
Angular momentum makes the world go 'round.

Re: Securing records using linux grou permissions

From
Luca Ferrari
Date:
On Tue, Oct 15, 2019 at 6:07 PM David Gauthier <davegauthierpg@gmail.com> wrote:
> Users are going to be working with data through perl/DBI scripts which currently connect using a generic role with
hardcodedpassword in the connect string.  Access will be select/insert/update/delete  We need to tighten up security as
describedabove.
 

I would apply row level security, as already pointed out.
Then, in my Perl scripts, I will force a SET ROLE depending on the
operating system group/user. In such case, you can have still a
"generic" user to use as connection/login, then change the set of
permissions on the fly as connected. Of course, row level security
must be applied against current_role and not session_user.
I would not say this is a robust approach, but can do what you want
(assuming you don't have to change thousands of Perl scripts).

Hope it helps.
Luca

Re: Securing records using linux grou permissions

From
"Peter J. Holzer"
Date:
On 2019-10-15 13:10:13 -0400, David Gauthier wrote:
> I was hoping there was a way to integrate the user/permissions/groups in linux
> with the PG permissions functionality. 

You can at least map the OS users to DB roles by using the peer or ident
authentication schemes. This way the users won't have to enter their
passwords again. But I think this works only if the client and the
server are on the same host. And you still have to maintain the groups,
although that should be easy to automate.

        hp

--
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | hjp@hjp.at         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
Attachment