Securing records using linux grou permissions
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?
It sounds like you want row level security-
https://www.postgresql.org/docs/9.6/ddl-rowsecurity.html
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.
Show quoted text
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:
Show quoted text
It sounds like you want row level security-
https://www.postgresql.org/docs/9.6/ddl-rowsecurity.htmlBut, 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.
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
<mailto:mlewis@entrata.com>> wrote:It sounds like you want row level security-
https://www.postgresql.org/docs/9.6/ddl-rowsecurity.htmlBut, 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.
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 hardcoded password in the connect string. Access will be select/insert/update/delete We need to tighten up security as described above.
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
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/>