[
https://issues.apache.org/jira/browse/HAWQ-1332?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15888629#comment-15888629
]
Alexander Denissov commented on HAWQ-1332:
------------------------------------------
[~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)
