Skip to content

Commit 7b276eb

Browse files
rickbergfalkrickbergfalk
authored
Add Query ACL (and migrations framework) (sqlpad#554)
* Install sequelize, umzug, and sqlite * Initial migration implementation * Add QueryAcl model * Fix sequelize/migration test integration * honor dbInMemory for sqlite/sequelize * Break initdb and migration into separate methods * Migrate existing queries to have a query_acl record * Let lib/db manage sequelize too * Fix tests * Remove unused queries.findByFilter * Import datatypes from sequelize * Consider QueryAcl in routes * Check permission for update * Move users into TestUtils class * Add tests and fix API * Add unique constraint on query_id & user_id * Add share query button to UI * Add failing cases for query deletion * Fix query delete API * Disable query delete button if user cannot delete * consistent sequelizeDb reference
1 parent c202ea3 commit 7b276eb

30 files changed

Lines changed: 1334 additions & 165 deletions

‎client/src/common/IconButton.module.css‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -46,6 +46,12 @@
4646
background-color: #eee;
4747
}
4848

49+
.btn[disabled]:hover {
50+
border: none;
51+
color: rgba(0, 0, 0, 0.25);
52+
background-color: #eee;
53+
}
54+
4955
.danger:hover,
5056
.danger:focus {
5157
color: var(--secondary-color);

‎client/src/queries/QueryListDrawer.js‎

Lines changed: 16 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,10 @@ import getDecoratedQueries from './getDecoratedQueries';
1717
import styles from './QueryList.module.css';
1818
import QueryPreview from './QueryPreview';
1919

20+
const SHARED = 'SHARED';
21+
const MY_QUERIES = 'MY_QUERIES';
22+
const ALL = 'ALL';
23+
2024
function getSortedFilteredQueries(
2125
currentUser,
2226
queries,
@@ -28,12 +32,12 @@ function getSortedFilteredQueries(
2832
) {
2933
let filteredQueries = getDecoratedQueries(queries, connections);
3034

31-
if (creatorSearch !== 'ALL') {
35+
if (creatorSearch !== ALL) {
3236
filteredQueries = filteredQueries.filter(query => {
33-
if (creatorSearch === 'MY_QUERIES') {
37+
if (creatorSearch === MY_QUERIES) {
3438
return query.createdBy === currentUser.email;
3539
}
36-
if (creatorSearch === 'TEAMS') {
40+
if (creatorSearch === SHARED) {
3741
return query.createdBy !== currentUser.email;
3842
}
3943
throw new Error(`Unknown creator search value ${creatorSearch}`);
@@ -98,7 +102,7 @@ function QueryListDrawer({
98102
}) {
99103
const [preview, setPreview] = useState(null);
100104
const [searches, setSearches] = useState([]);
101-
const [creatorSearch, setCreatorSearch] = useState('MY_QUERIES');
105+
const [creatorSearch, setCreatorSearch] = useState(MY_QUERIES);
102106
const [sort, setSort] = useState('SAVE_DATE');
103107
const [connectionId, setConnectionId] = useState('');
104108
const [dimensions, setDimensions] = useState({
@@ -133,6 +137,9 @@ function QueryListDrawer({
133137
const chartUrl = `/query-chart/${query._id}`;
134138
const queryUrl = `/queries/${query._id}`;
135139

140+
const canDelete =
141+
currentUser.role === 'admin' || currentUser.email === query.createdBy;
142+
136143
const hasChart =
137144
query && query.chartConfiguration && query.chartConfiguration.chartType;
138145

@@ -168,11 +175,13 @@ function QueryListDrawer({
168175
>
169176
chart <OpenInNewIcon size={16} />
170177
</Link>
178+
<div style={{ width: 4 }} />
171179
<DeleteConfirmButton
172180
icon
173181
key="del"
174182
confirmMessage={`Delete ${query.name}`}
175183
onConfirm={e => deleteQuery(query._id)}
184+
disabled={!canDelete}
176185
>
177186
Delete
178187
</DeleteConfirmButton>
@@ -198,9 +207,9 @@ function QueryListDrawer({
198207
value={creatorSearch}
199208
onChange={e => setCreatorSearch(e.target.value)}
200209
>
201-
<option value="MY_QUERIES">My queries</option>
202-
<option value="TEAMS">Team's</option>
203-
<option value="ALL">All</option>
210+
<option value={MY_QUERIES}>My queries</option>
211+
<option value={SHARED}>Shared with me</option>
212+
<option value={ALL}>All</option>
204213
</Select>
205214
<Select
206215
style={{ marginRight: 8 }}

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,7 @@ import ToolbarNewQueryButton from './ToolbarNewQueryButton';
99
import ToolbarQueryNameInput from './ToolbarQueryNameInput';
1010
import ToolbarRunButton from './ToolbarRunButton';
1111
import ToolbarSaveButton from './ToolbarSaveButton';
12+
import ToolbarShareQueryButton from './ToolbarShareQueryButton';
1213
import ToolbarSpacer from './ToolbarSpacer';
1314
import ToolbarTagsButton from './ToolbarTagsButton';
1415
import ToolbarToggleSchemaButton from './ToolbarToggleSchemaButton';
@@ -41,6 +42,7 @@ function Toolbar() {
4142
<ToolbarTagsButton />
4243
<ToolbarCloneButton />
4344
<ToolbarFormatQueryButton />
45+
<ToolbarShareQueryButton />
4446
<ToolbarSaveButton />
4547

4648
<ToolbarSpacer />

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

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -11,16 +11,19 @@ import QueryHistoryModal from '../../queryHistory/QueryHistoryModal';
1111
import UserList from '../../users/UserList';
1212
import fetchJson from '../../utilities/fetch-json.js';
1313
import AboutModal from './AboutModal';
14+
import { clearQueries } from '../../stores/queries';
1415

1516
function mapStateToProps(state) {
1617
return {
1718
currentUser: state.currentUser
1819
};
1920
}
2021

21-
const ConnectedToolbarMenu = connect(mapStateToProps)(React.memo(ToolbarMenu));
22+
const ConnectedToolbarMenu = connect(mapStateToProps, store => ({
23+
clearQueries
24+
}))(React.memo(ToolbarMenu));
2225

23-
function ToolbarMenu({ currentUser }) {
26+
function ToolbarMenu({ currentUser, clearQueries }) {
2427
const [showUsers, setShowUsers] = useState(false);
2528
const [redirectToSignIn, setRedirectToSignIn] = useState(false);
2629
const [showQueryHistory, setShowQueryHistory] = useState(false);
@@ -75,6 +78,7 @@ function ToolbarMenu({ currentUser }) {
7578
<MenuItem
7679
onSelect={async () => {
7780
await fetchJson('GET', '/api/signout');
81+
clearQueries();
7882
setRedirectToSignIn(true);
7983
}}
8084
>

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

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,37 @@
1+
import SharedIcon from 'mdi-react/AccountMultipleIcon';
2+
import PrivateIcon from 'mdi-react/AccountIcon';
3+
import React from 'react';
4+
import { connect } from 'unistore/react';
5+
import IconButton from '../../common/IconButton';
6+
import { setQueryState } from '../../stores/queries';
7+
8+
function mapStateToProps(state) {
9+
const acl = state.query.acl || [];
10+
return {
11+
shared: acl.length > 0
12+
};
13+
}
14+
15+
const ConnectedToolbarShareQueryButton = connect(mapStateToProps, store => ({
16+
setQueryState
17+
}))(React.memo(ToolbarShareQueryButton));
18+
19+
function ToolbarShareQueryButton({ shared, setQueryState }) {
20+
function handleClick() {
21+
setQueryState(
22+
'acl',
23+
shared ? [] : [{ userId: '__EVERYONE__', write: true }]
24+
);
25+
}
26+
27+
return (
28+
<IconButton
29+
tooltip={shared ? 'Query is shared' : 'Query is private'}
30+
onClick={handleClick}
31+
>
32+
{shared ? <SharedIcon /> : <PrivateIcon />}
33+
</IconButton>
34+
);
35+
}
36+
37+
export default ConnectedToolbarShareQueryButton;

‎client/src/stores/queries.js‎

Lines changed: 13 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -73,6 +73,10 @@ export const loadQueries = store => async state => {
7373
}
7474
};
7575

76+
export const clearQueries = () => {
77+
return { queries: [] };
78+
};
79+
7680
export const deleteQuery = store => async (state, queryId) => {
7781
const { queries } = state;
7882
const filteredQueries = queries.filter(q => {
@@ -247,17 +251,18 @@ export const handleQuerySelectionChange = (state, selectedText) => {
247251
};
248252

249253
export default {
250-
initialState,
254+
clearQueries,
255+
deleteQuery,
251256
formatQuery,
257+
handleChartConfigurationFieldsChange,
258+
handleChartTypeChange,
259+
handleCloneClick,
260+
handleQuerySelectionChange,
261+
initialState,
252262
loadQueries,
253-
deleteQuery,
254263
loadQuery,
264+
resetNewQuery,
255265
runQuery,
256266
saveQuery,
257-
handleCloneClick,
258-
resetNewQuery,
259-
setQueryState,
260-
handleChartConfigurationFieldsChange,
261-
handleChartTypeChange,
262-
handleQuerySelectionChange
267+
setQueryState
263268
};

‎server/lib/db.js‎

Lines changed: 7 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,7 @@ const appLog = require('./appLog');
55
const ensureAdmin = require('./ensureAdmin');
66
const consts = require('./consts');
77
const Models = require('../models');
8+
const SequelizeDb = require('../sequelizeDb');
89

910
const TEN_MINUTES = 1000 * 60 * 10;
1011
const FIVE_MINUTES = 1000 * 60 * 5;
@@ -28,8 +29,8 @@ async function getDb(instanceAlias = 'default') {
2829
throw new Error('db instance must be created first');
2930
}
3031
// nedb will already be a promise -- this just makes it explicit
31-
const { nedb, models } = await instancePromise;
32-
return { nedb, models };
32+
const { nedb, models, sequelizeDb } = await instancePromise;
33+
return { nedb, models, sequelizeDb };
3334
}
3435

3536
/**
@@ -114,8 +115,10 @@ async function initNedb(config) {
114115
nedb[dbname].nedb.persistence.setAutocompactionInterval(TEN_MINUTES);
115116
});
116117

118+
const sequelizeDb = new SequelizeDb(config);
119+
117120
// Schedule cleanups
118-
const models = new Models(nedb, config);
121+
const models = new Models(nedb, sequelizeDb, config);
119122
setInterval(async () => {
120123
await models.resultCache.removeExpired();
121124
await models.queryHistory.removeOldEntries();
@@ -124,7 +127,7 @@ async function initNedb(config) {
124127
// Ensure admin is set as specified if provided
125128
await ensureAdmin(nedb, admin, adminPassword);
126129

127-
return { nedb, models };
130+
return { nedb, models, sequelizeDb };
128131
}
129132

130133
/**

‎server/lib/migrate.js‎

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,26 @@
1+
const path = require('path');
2+
const Umzug = require('umzug');
3+
4+
function runMigrations(config, appLog, nedb, sequelizeInstance) {
5+
appLog.info('Running migrations');
6+
const umzug = new Umzug({
7+
storage: 'sequelize',
8+
storageOptions: {
9+
sequelize: sequelizeInstance,
10+
tableName: 'schema_version'
11+
},
12+
logging: message => {
13+
appLog.info(message);
14+
},
15+
migrations: {
16+
params: [sequelizeInstance.queryInterface, config, appLog, nedb],
17+
path: path.join(__dirname, '../migrations'),
18+
// The pattern that determines whether or not a file is a migration.
19+
pattern: /^\d+[\w-]+\.js$/
20+
}
21+
});
22+
23+
return umzug.up();
24+
}
25+
26+
module.exports = runMigrations;

‎server/migrations/04-00100-query-acl-schema.js‎

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,47 @@
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.createTable('query_acl', {
12+
id: {
13+
type: Sequelize.INTEGER,
14+
primaryKey: true,
15+
autoIncrement: true
16+
},
17+
query_id: {
18+
type: Sequelize.STRING,
19+
allowNull: false
20+
},
21+
user_id: {
22+
type: Sequelize.STRING,
23+
allowNull: false
24+
},
25+
write: {
26+
type: Sequelize.BOOLEAN,
27+
allowNull: false,
28+
defaultValue: false
29+
},
30+
created_at: {
31+
type: Sequelize.DATE,
32+
allowNull: false
33+
},
34+
updated_at: {
35+
type: Sequelize.DATE
36+
}
37+
});
38+
39+
await queryInterface.addConstraint('query_acl', ['query_id', 'user_id'], {
40+
type: 'unique',
41+
name: 'query_acl_query_id_user_id_key'
42+
});
43+
}
44+
45+
module.exports = {
46+
up
47+
};

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

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
const consts = require('../lib/consts');
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+
const queries = await nedb.queries.find({});
12+
13+
if (queries.length) {
14+
const records = queries.map(query => {
15+
return {
16+
query_id: query._id,
17+
user_id: consts.EVERYONE_ID,
18+
write: true,
19+
created_at: new Date(),
20+
updated_at: new Date()
21+
};
22+
});
23+
24+
await queryInterface.bulkInsert('query_acl', records);
25+
}
26+
}
27+
28+
module.exports = {
29+
up
30+
};

0 commit comments

Comments
 (0)