[jira] [Comment Edited] (HAWQ-1332) Can not grant database and schema privileges without table privileges in ranger or ranger plugin service

["Alexander Denissov (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/HAWQ-1332?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15888629#comment-15888629 ] 

Alexander Denissov edited comment on HAWQ-1332 at 2/28/17 6:44 PM:
-------------------------------------------------------------------

[~xsheng] -- I think we are confusing 2 issues here.

1. Privilege to connect to database -- this is CONNECT privilege that must be granted to a database resource. Since due to Ranger bug, it is not possible to define just database resource without defining schema and table, our design convention is that to represent a given database resource, we need to define it with database name, but schema and table must be set to *. Then grant CONNECT privilege to users that should be able to connect to such a resource. All values here must be included. Do not grant any schema / table specific privileges to this resource, if not desired.

2. Excluding specific tables from policies. Not sure whether this works or not, but this should have nothing to do with connecting to database. Define a separate policy with excluded table with table-level privileges and test it out. This policy should not have any CONNECT privileges and database connect access should be managed by policy defined in #1 above.

So, I still maintain that this is not an issue. In summary, any db-level privilege requires schema and table set to * and any schema level privilege requires table set to *.


was (Author: adenissov):
[~xsheng] -- I think we are confusing 2 issues here.

1. Privilege to connect to database -- this is CONNECT privilege that must be granted to a database resource. Since due to Ranger bug, it is not possible to define just database resource without defining schema and table, our design convention is that to represent a given database resource, we need to define it with database name, but shcema and table must be set to *. Then grant CONNECT privilege to users that should be able to connect to such a resource. All values here must be included. Do not grant any schema / table specific privileges to this resource, if not desired.

2. Excluding specific tables from policies. Not sure whether this works or not, but this should have nothing to do with connecting to database. Define a separate policy with excluded table with table-level privileges and test it out. This policy should not have any CONNECT privileges and database connect access should be managed by policy defined in #1 above.

So, I still maintain that this is not an issues. In summary, any db-level privilege requires schema and table set to * and any schema level privilege requires table set to *.

> Can not grant database and schema privileges without table privileges in ranger or ranger plugin service
> --------------------------------------------------------------------------------------------------------
>
>                 Key: HAWQ-1332
>                 URL: https://issues.apache.org/jira/browse/HAWQ-1332
>             Project: Apache HAWQ
>          Issue Type: Bug
>          Components: Security
>            Reporter: Chunling Wang
>            Assignee: Alexander Denissov
>             Fix For: 2.2.0.0-incubating
>
>         Attachments: screenshot-1.png, screenshot-2.png, screenshot-3.png
>
>
> We try to grant database connect and schema usage privileges to a non-super user to connect database. We find that if we set policy with database and schema included, but with table excluded, we can not connect database. But if we include table, we can connect to database. We think there may be bug in Ranger Plugin Service or Ranger. Here are steps to reproduce it.
> 1. create a new user "usertest1" in database:
> {code}
> $ psql postgres
> psql (8.2.15)
> Type "help" for help.
> postgres=# CREATE USER usertest1;
> NOTICE:  resource queue required -- using default resource queue "pg_default"
> CREATE ROLE
> postgres=#
> {code}
> 2. add user "usertest1" in pg_hba.conf
> {code}
> local all     usertest1             trust
> {code}
> 3. set policy with database and schema included, with table excluded
> !screenshot-1.png|width=800,height=400!
> 4. connect database with user "usertest1" but failed with permission denied
> {code}
> $ psql postgres -U usertest1
> psql: FATAL:  permission denied for database "postgres"
> DETAIL:  User does not have CONNECT privilege.
> {code}
> 5. set policy with database, schema and table included
> !screenshot-2.png|width=800,height=400!
> 6. connect database with user "usertest1" and succeed
> {code}
> $ psql postgres -U usertest1
> psql (8.2.15)
> Type "help" for help.
> postgres=#
> {code}
> But if we do not set table as "*", and specify table like "a", we can not access database either.
> !screenshot-3.png|width=800,height=400!



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)