Phoenix security and initial system table creation

[#1]

There are numerous references on how SYSTEM tables are created the first time that a user logs in to Phoenix. Thus, they would require Create and Write permissions in the HBase default namespace.

1. Does this happen for each user?

2. Does this happen for each time they login?

I ask this because we have users that were encountering "Insufficient permissions" errors. Then granted them 'RWXCA' permissions in HBase. Then everything worked well. After the first login we tried removing permissions (trying to create a read-only user). However, when we removed the 'CW' permissions, they could no longer login and starting getting Insufficient Permissions error.

Error: org.apache.hadoop.hbase.security.AccessDeniedException: Insufficient permissions (user=svc.xyx@FOO.BAR, scope=SYSTEM.CATALOG, family=, action=CREATE)

grant 'xyz', 'RWXCA', '@default' 
-- All good!

grant 'xyz', 'RX', '@default' 
-- No good! Even after first time

3. DO THE USERS ALWAYS HAVE TO HAVE 'CW' access to Hbase default namespace? And if so, what is the best way to control table-level security in Phoenix?

[#2]

1. No, SYSTEM tables are created for all Phoenix users.

2. When a user makes the first connection to Phoenix (instantiates the JDBC driver) it will check and try to create the SYSTEM tables if they don't already exist. For all but the first connection, this will be a no-op. If you have permissions put in place, you will want to launch sqlline (or some application using Phoenix) which has the permission to create these SYSTEM tables. Then, before having unprivileged users access phoenix, make sure they have read permission on the system tables.

3. You can create an HBase Namespace, grant privileges on that namespace to a user, and then instruct the user to create phoenix tables like "MY_NAMESPACE.MY_TABLE".

[#3]

Just to confirm my understanding - permissions to Phoenix tables still need to be controlled directly in HBase with grants to the underlying HBase tables? Would we have to grant for the secondary index tables as well?

In our situation, we will only allow admins to create tables, and then regular users can read or write to them. So we would use a superuser to create the tables and then grant RWX access to the underlying table in Hbase to normal users.

Also, the namespace support is, I understand, available from Phoenix 4.7+

[#4]

Your understanding is correct. Phoenix delegates to HBase permissions. Secondary index tables would require their own grants as well, yes. They are just another HBase table.

Yes, you have the steps correct regarding the superuser creating the tables and performing grants.

The namespace support actually appears to have landed in 4.8, but this was included in HDP-2.5 which was based on Phoenix 4.7 (the feature was backported) https://phoenix.apache.org/namspace_mapping.html

[#5]

@Josh Elser I have followed the above steps,but, have problem when I revoke the write and create permissions for the user post first login.

Env: HDP 2.6

Enabled phoenix.schema.isNamespaceMappingEnabled

Kerberos and Ranger Hbase plugin enabled.

Post Enabling the property:

1) Added the user to default policy which enables all permissions.

2) login using phoenix-sqlline zookeeper-quorum:2181:/hbase-secure got connected without any issue.

3) logged out and removed the WCA from Ranger policy.

Below is the error.

Caused by: org.apache.hadoop.hbase.ipc.RemoteWithExtrasException(org.apache.hadoop.hbase.security.AccessDeniedException): org.apache.hadoop.hbase.security.AccessDeniedException: Insufficient permissions for user ‘user@REALM',action: put, tableName:SYSTEM:CATALOG, family:0, column: _0 at org.apache.ranger.authorization.hbase.RangerAuthorizationCoprocessor.requirePermission(RangerAuthorizationCoprocessor.java:551)

[#6]

The Phoenix client is trying to move the SYSTEM tables to the SYSTEM namespace as a result of you enabling the namespace mapping. You have insufficient permissions to do so.

[#7]

@Josh Elser Can you help me out. Not sure what it means insufficient permissions. are you referring to the SYSTEM namespace ?

I am trying to launch a session using pheonix-sqlline with a under privileged user who has read permission on the ALL the namespaces and tables typically added the public group to the default policy in Ranger.

2. When a user makes the first connection to Phoenix (instantiates the JDBC driver) it will check and try to create the SYSTEM tables if they don't already exist. For all but the first connection, this will be a no-op. If you have permissions put in place, you will want to launch sqlline (or some application using Phoenix) which has the permission to create these SYSTEM tables. Then, before having unprivileged users access phoenix, make sure they have read permission on the system tables.

as per above my understanding is any user needs to have full permissions on the system tables while connecting to sqlline for the first time and then just granting read access on the system tables should help him re-establish the session.

Also can you please point me to document that can provide information around restricting access via Ranger for Phoenix.

[#8]

"as per above my understanding is any user needs to have full permissions on the system tables while connecting to sqlline for the first time and then just granting read access on the system tables should help him re-establish the session." -- correct.

"Also can you please point me to document that can provide information around restricting access via Ranger for Phoenix." -- I'd suggest you ask a new question for help on using Ranger. I am not familiar with the project.