Skip to content

Commit 321162d

Browse files
author
Rick Bergfalk
authored
Query ACL enhancements: Add userEmail and groupId (sqlpad#558)
* Add user_email and group_id columns * Implement extended query acl API * Decorate user permissions on query object * Always return decorated queries * Update query acl groupId * Use can<permission> determined by server api * Update seed data examples for updated ACL * Add expanded ACL tests * Fix decorateQueryUserAccess * Update README comment
1 parent fde0746 commit 321162d

23 files changed

Lines changed: 553 additions & 140 deletions

‎README.md‎

Lines changed: 17 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -534,18 +534,32 @@ Queries are created or replaced matching on query id. At this time the query ACL
534534

535535
At this point SQLPad does not enforce referential integrity, so queries may be created with a `createdBy` containing an email address for a user that does not exist.
536536

537-
Example seed query JSON file:
537+
Example seed query JSON file (comments only added for doc purposes):
538538

539-
```json
539+
```js
540540
{
541541
"id": "seed-query-1",
542542
"name": "Seed query 1",
543543
"connectionId": "seed-connection-1",
544544
"queryText": "SELECT * FROM seed_table",
545545
"createdBy": "[email protected]",
546546
"acl": [
547+
// an ACL entry with write=false allows that user to read
548+
// (and execute if they have connection permission)
549+
// write=true allows user to save query
550+
{
551+
"userId": "some-userId-in-sqlpad",
552+
"write": false
553+
},
554+
// ACL entry can also be specified with a users email address.
555+
// The user does not need to exist in SQLPad at this point
556+
{
557+
"userEmail": "[email protected]",
558+
"write": true
559+
},
560+
// Alternatively a special __EVERYONE__ group may be used to share the query with all SQLPad users
547561
{
548-
"userId": "__EVERYONE__",
562+
"groupId": "__EVERYONE__",
549563
"write": true
550564
}
551565
]

‎client/src/queries/QueryListDrawer.js‎

Lines changed: 1 addition & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -137,9 +137,6 @@ function QueryListDrawer({
137137
const chartUrl = `/query-chart/${query._id}`;
138138
const queryUrl = `/queries/${query._id}`;
139139

140-
const canDelete =
141-
currentUser.role === 'admin' || currentUser.email === query.createdBy;
142-
143140
const hasChart =
144141
query && query.chartConfiguration && query.chartConfiguration.chartType;
145142

@@ -181,7 +178,7 @@ function QueryListDrawer({
181178
key="del"
182179
confirmMessage={`Delete ${query.name}`}
183180
onConfirm={e => deleteQuery(query._id)}
184-
disabled={!canDelete}
181+
disabled={!query.canDelete}
185182
>
186183
Delete
187184
</DeleteConfirmButton>

‎client/src/queryEditor/toolbar/ToolbarShareQueryButton.js‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -20,7 +20,7 @@ function ToolbarShareQueryButton({ shared, setQueryState }) {
2020
function handleClick() {
2121
setQueryState(
2222
'acl',
23-
shared ? [] : [{ userId: '__EVERYONE__', write: true }]
23+
shared ? [] : [{ groupId: '__EVERYONE__', write: true }]
2424
);
2525
}
2626

‎client/src/stores/queries.js‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -17,7 +17,10 @@ export const NEW_QUERY = {
1717
chartConfiguration: {
1818
chartType: '',
1919
fields: {} // key value for chart
20-
}
20+
},
21+
canRead: true,
22+
canWrite: true,
23+
canDelete: true
2124
};
2225

2326
export const initialState = {

‎server/lib/decorateQueryUserAccess.js‎

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,46 @@
1+
const consts = require('./consts');
2+
3+
// Not sure where to put utilities like these
4+
5+
/**
6+
* Returns a decorated query object with canRead, canWrite, and canDelete properties
7+
* @param {object} query
8+
* @param {object} user
9+
*/
10+
function decorateQueryUserAccess(query, user) {
11+
const { ...clone } = query;
12+
clone.canRead = false;
13+
clone.canWrite = false;
14+
clone.canDelete = false;
15+
16+
if (user.role === 'admin' || user.email === clone.createdBy) {
17+
clone.canRead = true;
18+
clone.canWrite = true;
19+
clone.canDelete = true;
20+
} else if (clone.acl.length) {
21+
const writeAcl = clone.acl
22+
// filter acl records that match for this user
23+
.filter(
24+
acl =>
25+
acl.groupId === consts.EVERYONE_ID ||
26+
acl.userId === user._id ||
27+
acl.userEmail === user.email
28+
)
29+
// and return first one that has write
30+
.find(a => a.write === true);
31+
clone.canWrite = Boolean(writeAcl);
32+
33+
// A record in ACL allows read permissions
34+
const canRead = query.acl.find(
35+
acl =>
36+
acl.groupId === consts.EVERYONE_ID ||
37+
acl.userId === user._id ||
38+
acl.userEmail === user.email
39+
);
40+
clone.canRead = Boolean(canRead);
41+
}
42+
43+
return clone;
44+
}
45+
46+
module.exports = decorateQueryUserAccess;

‎server/migrations/04-00110-query-acl-data.js‎

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,3 @@
1-
const consts = require('../lib/consts');
2-
31
/**
42
* @param {import('sequelize').QueryInterface} queryInterface
53
* @param {import('../lib/config')} config
@@ -14,7 +12,7 @@ async function up(queryInterface, config, appLog, nedb) {
1412
const records = queries.map(query => {
1513
return {
1614
query_id: query._id,
17-
user_id: consts.EVERYONE_ID,
15+
user_id: '__EVERYONE__', // value in consts.EVERYONE_ID at time of migration
1816
write: true,
1917
created_at: new Date(),
2018
updated_at: new Date()

‎server/migrations/04-00120-query-acl-remove-old-constraint.js‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
/**
2+
* @param {import('sequelize').QueryInterface} queryInterface
3+
* @param {import('../lib/config')} config
4+
* @param {import('../lib/logger')} appLog
5+
* @param {object} nedb - collection of nedb objects created in /lib/db.js
6+
*/
7+
// eslint-disable-next-line no-unused-vars
8+
async function up(queryInterface, config, appLog, nedb) {
9+
// Remove unique constraint on query_id_user_id (it'll be added again switched around later)
10+
await queryInterface.removeConstraint(
11+
'query_acl',
12+
'query_acl_query_id_user_id_key'
13+
);
14+
}
15+
16+
module.exports = {
17+
up
18+
};

‎server/migrations/04-00121-query-acl-add-user-email.js‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
const Sequelize = require('sequelize');
2+
3+
/**
4+
* @param {import('sequelize').QueryInterface} queryInterface
5+
* @param {import('../lib/config')} config
6+
* @param {import('../lib/logger')} appLog
7+
* @param {object} nedb - collection of nedb objects created in /lib/db.js
8+
*/
9+
// eslint-disable-next-line no-unused-vars
10+
async function up(queryInterface, config, appLog, nedb) {
11+
await queryInterface.addColumn('query_acl', 'user_email', {
12+
type: Sequelize.STRING
13+
});
14+
}
15+
16+
module.exports = {
17+
up
18+
};

‎server/migrations/04-00122-query-acl-add-group-id.js‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
const Sequelize = require('sequelize');
2+
3+
/**
4+
* @param {import('sequelize').QueryInterface} queryInterface
5+
* @param {import('../lib/config')} config
6+
* @param {import('../lib/logger')} appLog
7+
* @param {object} nedb - collection of nedb objects created in /lib/db.js
8+
*/
9+
// eslint-disable-next-line no-unused-vars
10+
async function up(queryInterface, config, appLog, nedb) {
11+
await queryInterface.addColumn('query_acl', 'group_id', {
12+
type: Sequelize.STRING
13+
});
14+
}
15+
16+
module.exports = {
17+
up
18+
};

‎server/migrations/04-00123-query-acl-remove-user-id-not-null.js‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
const Sequelize = require('sequelize');
2+
3+
/**
4+
* @param {import('sequelize').QueryInterface} queryInterface
5+
* @param {import('../lib/config')} config
6+
* @param {import('../lib/logger')} appLog
7+
* @param {object} nedb - collection of nedb objects created in /lib/db.js
8+
*/
9+
// eslint-disable-next-line no-unused-vars
10+
async function up(queryInterface, config, appLog, nedb) {
11+
// remove not-null constraint for user_id
12+
await queryInterface.changeColumn('query_acl', 'user_id', {
13+
type: Sequelize.STRING,
14+
allowNull: true
15+
});
16+
}
17+
18+
module.exports = {
19+
up
20+
};

0 commit comments

Comments
 (0)