NihalJain commented on PR #17295:
URL: https://github.com/apache/pinot/pull/17295#issuecomment-3597916618
Manual testing:
1) Tested REST API create/update/get operations with RLS filters
- POST
```
curl -X 'POST' \
'http://localhost:9001/users' \
-H 'accept: application/json' \
-H 'Authorization: Basic YWRtaW46dmVyeXNlY3JldA==' \
-H 'Content-Type: application/json' \
-d '{
"username": "userRLS_Simple",
"password": "secretSimple",
"component": "BROKER",
"role": "USER",
"tables": ["airlineStats"],
"permissions": ["READ"],
"rlsFilters": {"airlineStats": ["AirlineID='\''19805'\''"]}
}'
```
```
{
"status": "User userRLS_Simple_BROKER has been successfully added!"
}
```
- GET
```
curl -X 'GET' \
'http://localhost:9001/users/userRLS_Simple?component=BROKER' \
-H 'accept: application/json' \
-H 'Authorization: Basic YWRtaW46dmVyeXNlY3JldA=='
```
```
{
"userRLS_Simple_BROKER": {
"username": "userRLS_Simple",
"password":
"$2a$10$lA8P0gGKpG66Ype3wTmrp.2Bel8jHbQNglf8g/Cai7DHUbY/LZnzS",
"component": "BROKER",
"role": "USER",
"tables": [
"airlineStats"
],
"permissions": [
"READ"
],
"rlsFilters": {
"airlineStats": [
"AirlineID='19805'"
]
},
"usernameWithComponent": "userRLS_Simple_BROKER"
}
}```
- DELETE
```
curl -X 'DELETE' \
'http://localhost:9001/users/userRLS_Simple?component=BROKER' \
-H 'accept: application/json' \
-H 'Authorization: Basic YWRtaW46dmVyeXNlY3JldA=='
```
```
{
"status": "User: userRLS_Simple_BROKER has been successfully deleted"
}
```
2) Tested queries work as expected with rls for the ZK-based authentication
```
▸ Row-Level Security (RLS) Testing
Testing: RLS filter application on airlineStats:
DEBUG: curl -s -w \n%{http_code} -u admin:verysecret -X POST -H
Content-Type: application/json -d {"sql":"SELECT COUNT(*) FROM airlineStats
LIMIT 1"} http://localhost:8099/query/sql
DEBUG: Response body:
{"resultTable":{"dataSchema":{"columnNames":["count(*)"],"columnDataTypes":["LONG"]},"rows":[[289]]},"numRowsResultSet":1,"partialResult":false,"exceptions":[],"numGroupsLimitReached":false,"numGroupsWarningLimitReached":false,"timeUsedMs":2,"requestId":"97156851000000116","clientRequestId":null,"brokerId":"Broker_localhost_8099","numDocsScanned":1,"totalDocs":289,"numEntriesScannedInFilter":0,"numEntriesScannedPostFilter":1,"numServersQueried":1,"numServersResponded":1,"numSegmentsQueried":1,"numSegmentsProcessed":1,"numSegmentsMatched":1,"numConsumingSegmentsQueried":0,"numConsumingSegmentsProcessed":0,"numConsumingSegmentsMatched":0,"minConsumingFreshnessTimeMs":0,"numSegmentsPrunedByBroker":0,"numSegmentsPrunedByServer":0,"numSegmentsPrunedInvalid":0,"numSegmentsPrunedByLimit":0,"numSegmentsPrunedByValue":0,"brokerReduceTimeMs":0,"offlineThreadCpuTimeNs":0,"realtimeThreadCpuTimeNs":0,"offlineSystemActivitiesCpuTimeNs":0,"realtimeSystemActivitiesCpuTimeNs"
:0,"offlineResponseSerializationCpuTimeNs":0,"realtimeResponseSerializationCpuTimeNs":0,"offlineTotalCpuTimeNs":0,"realtimeTotalCpuTimeNs":0,"explainPlanNumEmptyFilterSegments":0,"explainPlanNumMatchAllFilterSegments":0,"traceInfo":{},"tablesQueried":["airlineStats"],"offlineThreadMemAllocatedBytes":0,"realtimeThreadMemAllocatedBytes":0,"offlineResponseSerMemAllocatedBytes":0,"realtimeResponseSerMemAllocatedBytes":0,"offlineTotalMemAllocatedBytes":0,"realtimeTotalMemAllocatedBytes":0,"pools":[-1],"rlsFiltersApplied":false,"groupsTrimmed":false}
DEBUG: Response status: 200
✓ Admin sees all rows (no RLS, count=289) → 200 OK, count=289
DEBUG: curl -s -w \n%{http_code} -u userRLS:secretRLS -X POST -H
Content-Type: application/json -d {"sql":"SELECT COUNT(*) FROM airlineStats
LIMIT 1"} http://localhost:8099/query/sql
DEBUG: Response body:
{"resultTable":{"dataSchema":{"columnNames":["count(*)"],"columnDataTypes":["LONG"]},"rows":[[45]]},"numRowsResultSet":1,"partialResult":false,"exceptions":[],"numGroupsLimitReached":false,"numGroupsWarningLimitReached":false,"timeUsedMs":3,"requestId":"97156851000000117","clientRequestId":null,"brokerId":"Broker_localhost_8099","numDocsScanned":45,"totalDocs":289,"numEntriesScannedInFilter":289,"numEntriesScannedPostFilter":0,"numServersQueried":1,"numServersResponded":1,"numSegmentsQueried":1,"numSegmentsProcessed":1,"numSegmentsMatched":1,"numConsumingSegmentsQueried":0,"numConsumingSegmentsProcessed":0,"numConsumingSegmentsMatched":0,"minConsumingFreshnessTimeMs":0,"numSegmentsPrunedByBroker":0,"numSegmentsPrunedByServer":0,"numSegmentsPrunedInvalid":0,"numSegmentsPrunedByLimit":0,"numSegmentsPrunedByValue":0,"brokerReduceTimeMs":0,"offlineThreadCpuTimeNs":0,"realtimeThreadCpuTimeNs":0,"offlineSystemActivitiesCpuTimeNs":0,"realtimeSystemActivitiesCpuTimeN
s":0,"offlineResponseSerializationCpuTimeNs":0,"realtimeResponseSerializationCpuTimeNs":0,"offlineTotalCpuTimeNs":0,"realtimeTotalCpuTimeNs":0,"explainPlanNumEmptyFilterSegments":0,"explainPlanNumMatchAllFilterSegments":0,"traceInfo":{},"tablesQueried":["airlineStats"],"offlineThreadMemAllocatedBytes":0,"realtimeThreadMemAllocatedBytes":0,"offlineResponseSerMemAllocatedBytes":0,"realtimeResponseSerMemAllocatedBytes":0,"offlineTotalMemAllocatedBytes":0,"realtimeTotalMemAllocatedBytes":0,"pools":[-1],"rlsFiltersApplied":true,"groupsTrimmed":false}
DEBUG: Response status: 200
✓ UserRLS sees only TX rows (RLS applied, count=45) → 200 OK, count=45
DEBUG: curl -s -w \n%{http_code} -u userRLS:secretRLS -X POST -H
Content-Type: application/json -d {"sql":"SELECT COUNT(*) FROM airlineStats
WHERE OriginState='TX' LIMIT 1"} http://localhost:8099/query/sql
DEBUG: Response body:
{"resultTable":{"dataSchema":{"columnNames":["count(*)"],"columnDataTypes":["LONG"]},"rows":[[45]]},"numRowsResultSet":1,"partialResult":false,"exceptions":[],"numGroupsLimitReached":false,"numGroupsWarningLimitReached":false,"timeUsedMs":3,"requestId":"97156851000000118","clientRequestId":null,"brokerId":"Broker_localhost_8099","numDocsScanned":45,"totalDocs":289,"numEntriesScannedInFilter":334,"numEntriesScannedPostFilter":0,"numServersQueried":1,"numServersResponded":1,"numSegmentsQueried":1,"numSegmentsProcessed":1,"numSegmentsMatched":1,"numConsumingSegmentsQueried":0,"numConsumingSegmentsProcessed":0,"numConsumingSegmentsMatched":0,"minConsumingFreshnessTimeMs":0,"numSegmentsPrunedByBroker":0,"numSegmentsPrunedByServer":0,"numSegmentsPrunedInvalid":0,"numSegmentsPrunedByLimit":0,"numSegmentsPrunedByValue":0,"brokerReduceTimeMs":0,"offlineThreadCpuTimeNs":0,"realtimeThreadCpuTimeNs":0,"offlineSystemActivitiesCpuTimeNs":0,"realtimeSystemActivitiesCpuTimeN
s":0,"offlineResponseSerializationCpuTimeNs":0,"realtimeResponseSerializationCpuTimeNs":0,"offlineTotalCpuTimeNs":0,"realtimeTotalCpuTimeNs":0,"explainPlanNumEmptyFilterSegments":0,"explainPlanNumMatchAllFilterSegments":0,"traceInfo":{},"tablesQueried":["airlineStats"],"offlineThreadMemAllocatedBytes":0,"realtimeThreadMemAllocatedBytes":0,"offlineResponseSerMemAllocatedBytes":0,"realtimeResponseSerMemAllocatedBytes":0,"offlineTotalMemAllocatedBytes":0,"realtimeTotalMemAllocatedBytes":0,"pools":[-1],"rlsFiltersApplied":true,"groupsTrimmed":false}
DEBUG: Response status: 200
✓ UserRLS explicit TX query (count=45) → 200 OK, count=45
DEBUG: curl -s -w \n%{http_code} -u userRLS:secretRLS -X POST -H
Content-Type: application/json -d {"sql":"SELECT COUNT(*) FROM airlineStats
WHERE OriginState='CA' LIMIT 1"} http://localhost:8099/query/sql
DEBUG: Response body:
{"resultTable":{"dataSchema":{"columnNames":["count(*)"],"columnDataTypes":["LONG"]},"rows":[[0]]},"numRowsResultSet":1,"partialResult":false,"exceptions":[],"numGroupsLimitReached":false,"numGroupsWarningLimitReached":false,"timeUsedMs":3,"requestId":"97156851000000119","clientRequestId":null,"brokerId":"Broker_localhost_8099","numDocsScanned":0,"totalDocs":289,"numEntriesScannedInFilter":313,"numEntriesScannedPostFilter":0,"numServersQueried":1,"numServersResponded":1,"numSegmentsQueried":1,"numSegmentsProcessed":1,"numSegmentsMatched":0,"numConsumingSegmentsQueried":0,"numConsumingSegmentsProcessed":0,"numConsumingSegmentsMatched":0,"minConsumingFreshnessTimeMs":0,"numSegmentsPrunedByBroker":0,"numSegmentsPrunedByServer":0,"numSegmentsPrunedInvalid":0,"numSegmentsPrunedByLimit":0,"numSegmentsPrunedByValue":0,"brokerReduceTimeMs":0,"offlineThreadCpuTimeNs":0,"realtimeThreadCpuTimeNs":0,"offlineSystemActivitiesCpuTimeNs":0,"realtimeSystemActivitiesCpuTimeNs"
:0,"offlineResponseSerializationCpuTimeNs":0,"realtimeResponseSerializationCpuTimeNs":0,"offlineTotalCpuTimeNs":0,"realtimeTotalCpuTimeNs":0,"explainPlanNumEmptyFilterSegments":0,"explainPlanNumMatchAllFilterSegments":0,"traceInfo":{},"tablesQueried":["airlineStats"],"offlineThreadMemAllocatedBytes":0,"realtimeThreadMemAllocatedBytes":0,"offlineResponseSerMemAllocatedBytes":0,"realtimeResponseSerMemAllocatedBytes":0,"offlineTotalMemAllocatedBytes":0,"realtimeTotalMemAllocatedBytes":0,"pools":[-1],"rlsFiltersApplied":true,"groupsTrimmed":false}
DEBUG: Response status: 200
✓ UserRLS CA query returns 0 (RLS blocks CA, count=0) → 200 OK, count=0
Testing: RLS Simple filter (AirlineID='19805'):
DEBUG: curl -s -w \n%{http_code} -u userRLS_Simple:secretSimple -X POST -H
Content-Type: application/json -d {"sql":"SELECT COUNT(*) FROM airlineStats
LIMIT 1"} http://localhost:8099/query/sql
DEBUG: Response body:
{"resultTable":{"dataSchema":{"columnNames":["count(*)"],"columnDataTypes":["LONG"]},"rows":[[40]]},"numRowsResultSet":1,"partialResult":false,"exceptions":[],"numGroupsLimitReached":false,"numGroupsWarningLimitReached":false,"timeUsedMs":3,"requestId":"97156851000000120","clientRequestId":null,"brokerId":"Broker_localhost_8099","numDocsScanned":1,"totalDocs":289,"numEntriesScannedInFilter":0,"numEntriesScannedPostFilter":1,"numServersQueried":1,"numServersResponded":1,"numSegmentsQueried":1,"numSegmentsProcessed":1,"numSegmentsMatched":1,"numConsumingSegmentsQueried":0,"numConsumingSegmentsProcessed":0,"numConsumingSegmentsMatched":0,"minConsumingFreshnessTimeMs":0,"numSegmentsPrunedByBroker":0,"numSegmentsPrunedByServer":0,"numSegmentsPrunedInvalid":0,"numSegmentsPrunedByLimit":0,"numSegmentsPrunedByValue":0,"brokerReduceTimeMs":0,"offlineThreadCpuTimeNs":0,"realtimeThreadCpuTimeNs":0,"offlineSystemActivitiesCpuTimeNs":0,"realtimeSystemActivitiesCpuTimeNs":
0,"offlineResponseSerializationCpuTimeNs":0,"realtimeResponseSerializationCpuTimeNs":0,"offlineTotalCpuTimeNs":0,"realtimeTotalCpuTimeNs":0,"explainPlanNumEmptyFilterSegments":0,"explainPlanNumMatchAllFilterSegments":0,"traceInfo":{},"tablesQueried":["airlineStats"],"offlineThreadMemAllocatedBytes":0,"realtimeThreadMemAllocatedBytes":0,"offlineResponseSerMemAllocatedBytes":0,"realtimeResponseSerMemAllocatedBytes":0,"offlineTotalMemAllocatedBytes":0,"realtimeTotalMemAllocatedBytes":0,"pools":[-1],"rlsFiltersApplied":true,"groupsTrimmed":false}
DEBUG: Response status: 200
✓ UserRLS_Simple sees filtered rows (RLS applied, count=40) → 200 OK,
count=40
Testing: RLS AND filter (OriginState='TX' AND AirlineID='19805'):
DEBUG: curl -s -w \n%{http_code} -u userRLS_And:secretAnd -X POST -H
Content-Type: application/json -d {"sql":"SELECT COUNT(*) FROM airlineStats
LIMIT 1"} http://localhost:8099/query/sql
DEBUG: Response body:
{"resultTable":{"dataSchema":{"columnNames":["count(*)"],"columnDataTypes":["LONG"]},"rows":[[14]]},"numRowsResultSet":1,"partialResult":false,"exceptions":[],"numGroupsLimitReached":false,"numGroupsWarningLimitReached":false,"timeUsedMs":3,"requestId":"97156851000000121","clientRequestId":null,"brokerId":"Broker_localhost_8099","numDocsScanned":14,"totalDocs":289,"numEntriesScannedInFilter":304,"numEntriesScannedPostFilter":0,"numServersQueried":1,"numServersResponded":1,"numSegmentsQueried":1,"numSegmentsProcessed":1,"numSegmentsMatched":1,"numConsumingSegmentsQueried":0,"numConsumingSegmentsProcessed":0,"numConsumingSegmentsMatched":0,"minConsumingFreshnessTimeMs":0,"numSegmentsPrunedByBroker":0,"numSegmentsPrunedByServer":0,"numSegmentsPrunedInvalid":0,"numSegmentsPrunedByLimit":0,"numSegmentsPrunedByValue":0,"brokerReduceTimeMs":0,"offlineThreadCpuTimeNs":0,"realtimeThreadCpuTimeNs":0,"offlineSystemActivitiesCpuTimeNs":0,"realtimeSystemActivitiesCpuTimeN
s":0,"offlineResponseSerializationCpuTimeNs":0,"realtimeResponseSerializationCpuTimeNs":0,"offlineTotalCpuTimeNs":0,"realtimeTotalCpuTimeNs":0,"explainPlanNumEmptyFilterSegments":0,"explainPlanNumMatchAllFilterSegments":0,"traceInfo":{},"tablesQueried":["airlineStats"],"offlineThreadMemAllocatedBytes":0,"realtimeThreadMemAllocatedBytes":0,"offlineResponseSerMemAllocatedBytes":0,"realtimeResponseSerMemAllocatedBytes":0,"offlineTotalMemAllocatedBytes":0,"realtimeTotalMemAllocatedBytes":0,"pools":[-1],"rlsFiltersApplied":true,"groupsTrimmed":false}
DEBUG: Response status: 200
✓ UserRLS_And sees filtered rows (RLS applied, count=14) → 200 OK,
count=14
Testing: RLS OR filter (OriginState='TX' OR OriginState='CA'):
DEBUG: curl -s -w \n%{http_code} -u userRLS_Or:secretOr -X POST -H
Content-Type: application/json -d {"sql":"SELECT COUNT(*) FROM airlineStats
LIMIT 1"} http://localhost:8099/query/sql
DEBUG: Response body:
{"resultTable":{"dataSchema":{"columnNames":["count(*)"],"columnDataTypes":["LONG"]},"rows":[[84]]},"numRowsResultSet":1,"partialResult":false,"exceptions":[],"numGroupsLimitReached":false,"numGroupsWarningLimitReached":false,"timeUsedMs":6,"requestId":"97156851000000122","clientRequestId":null,"brokerId":"Broker_localhost_8099","numDocsScanned":84,"totalDocs":289,"numEntriesScannedInFilter":578,"numEntriesScannedPostFilter":0,"numServersQueried":1,"numServersResponded":1,"numSegmentsQueried":1,"numSegmentsProcessed":1,"numSegmentsMatched":1,"numConsumingSegmentsQueried":0,"numConsumingSegmentsProcessed":0,"numConsumingSegmentsMatched":0,"minConsumingFreshnessTimeMs":0,"numSegmentsPrunedByBroker":0,"numSegmentsPrunedByServer":0,"numSegmentsPrunedInvalid":0,"numSegmentsPrunedByLimit":0,"numSegmentsPrunedByValue":0,"brokerReduceTimeMs":0,"offlineThreadCpuTimeNs":0,"realtimeThreadCpuTimeNs":0,"offlineSystemActivitiesCpuTimeNs":0,"realtimeSystemActivitiesCpuTimeN
s":0,"offlineResponseSerializationCpuTimeNs":0,"realtimeResponseSerializationCpuTimeNs":0,"offlineTotalCpuTimeNs":0,"realtimeTotalCpuTimeNs":0,"explainPlanNumEmptyFilterSegments":0,"explainPlanNumMatchAllFilterSegments":0,"traceInfo":{},"tablesQueried":["airlineStats"],"offlineThreadMemAllocatedBytes":0,"realtimeThreadMemAllocatedBytes":0,"offlineResponseSerMemAllocatedBytes":0,"realtimeResponseSerMemAllocatedBytes":0,"offlineTotalMemAllocatedBytes":0,"realtimeTotalMemAllocatedBytes":0,"pools":[-1],"rlsFiltersApplied":true,"groupsTrimmed":false}
DEBUG: Response status: 200
✓ UserRLS_Or sees filtered rows (RLS applied, count=84) → 200 OK,
count=84
```
PS: This was done with a bash script which I plan to contribute later with
another PR
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
