Multiple rows delete: how to write a delete query

Feature Discussion Deletion
22

That’s interesting, thanks for testing at your end Piyush. We are using “admin” account in our case and it has read-write role (I believe it is super set to write role).

==>tls_aql
Seed:        
User:         admin
Config File:  /etc/aerospike/astools.conf /home/aerospike/.aerospike/astools.conf
Enter Password:
Aerospike Query Client
Version 3.23.0
C Client Version 4.6.9
Copyright 2012-2019 Aerospike. All rights reserved.
aql>
aql> show users
+---------+-----------------------------------------------------+
| user    | roles                                               |
+---------+-----------------------------------------------------+
| "admin" | "read-write, read-write-udf, sys-admin, user-admin" |
| "test"  | "read-write"                                        |
| "test2" | "user-admin"                                        |
+---------+-----------------------------------------------------+
3 rows in set (0.001 secs)

Sequence of events that were performed,

  • Initially Inserted a record in set - testset
  • Truncated the testset
  • Re -inserted the the same record in same set.
  • Restored namespace from a backup using asrestore

Do you think any of above is preventing us to delete the record? and any relation?

23

Granted user “write” role, still throwing an exception

aql> delete from ovn_test.testset where edigest="v2wdE+fNEMW9Ai0n598XDAvM1uE="
Error: (22) AEROSPIKE_ERR_FAIL_FORBIDDEN

aql> show users
+---------+------------------------------------------------------------+
| user    | roles                                                      |
+---------+------------------------------------------------------------+
| "admin" | "read-write, read-write-udf, sys-admin, user-admin, write" |
| "test"  | "read-write"                                               |
| "test2" | "user-admin"                                               |
+---------+------------------------------------------------------------+
3 rows in set (0.001 secs)
24

Try logging in as user “test” and then running this exercise instead of trying to do as “admin”. I tested with security enabled and was able to do with a user different from admin.

[training@ip-172-31-47-105 bin]$ aql -Upg -Ppg
Seed:         127.0.0.1
User:         pg
Config File:  /etc/aerospike/astools.conf /home/training/.aerospike/astools.conf
Aerospike Query Client
Version 3.29.0
C Client Version 4.6.17
Copyright 2012-2020 Aerospike. All rights reserved.
aql> insert into ovn_test.testset (PK, bin1, bin2) values (1,2,3)
OK, 1 record affected.

aql> set record_print_metadata true
RECORD_PRINT_METADATA = true
aql> select * from ovn_test
+------+------+--------------------------------+-----------+-------+-------+
| bin1 | bin2 | {edigest}                      | {set}     | {ttl} | {gen} |
+------+------+--------------------------------+-----------+-------+-------+
| 2    | 3    | "9ZEkmG6WrRdbN0yUh5RbvK1Te3Q=" | "testset" | -1    | 1     |
+------+------+--------------------------------+-----------+-------+-------+
1 row in set (0.129 secs)

OK

aql> delete from ovn_test.testset where edigest="9ZEkmG6WrRdbN0yUh5RbvK1Te3Q="
OK, 1 record affected.

aql> select * from ovn_test
0 rows in set (0.112 secs)

OK

aql>
25

Unable to delete with app user (Other than admin) too

aql> delete from xxxx.yyyy where edigest=“Et4wiC2A+JBWEnPbu9dniMPbe84=” Error: (22) AEROSPIKE_ERR_FAIL_FORBIDDEN