sqlpad · rickbergfalk · Feb 28, 2020 · Feb 28, 2020 · Feb 28, 2020 · Feb 28, 2020
diff --git a/README.md b/README.md
@@ -534,18 +534,32 @@ Queries are created or replaced matching on query id. At this time the query ACL
 
 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.
 
-Example seed query JSON file:
+Example seed query JSON file (comments only added for doc purposes):
 
-```json
+```js
 {
   "id": "seed-query-1",
   "name": "Seed query 1",
   "connectionId": "seed-connection-1",
   "queryText": "SELECT * FROM seed_table",
   "createdBy": "admin@sqlpad.com",
   "acl": [
+    // an ACL entry with write=false allows that user to read
+    // (and execute if they have connection permission)
+    // write=true allows user to save query
+    {
+      "userId": "some-userId-in-sqlpad",
+      "write": false
+    },
+    // ACL entry can also be specified with a users email address.
+    // The user does not need to exist in SQLPad at this point
+    {
+      "userEmail": "someone@sqlpad.com",
+      "write": true
+    },
+    // Alternatively a special __EVERYONE__ group may be used to share the query with all SQLPad users
     {
-      "userId": "__EVERYONE__",
+      "groupId": "__EVERYONE__",
       "write": true
     }
   ]

diff --git a/client/src/queries/QueryListDrawer.js b/client/src/queries/QueryListDrawer.js
@@ -137,9 +137,6 @@ function QueryListDrawer({
     const chartUrl = `/query-chart/${query._id}`;
     const queryUrl = `/queries/${query._id}`;
 
-    const canDelete =
-      currentUser.role === 'admin' || currentUser.email === query.createdBy;
-
     const hasChart =
       query && query.chartConfiguration && query.chartConfiguration.chartType;
 
@@ -181,7 +178,7 @@ function QueryListDrawer({
             key="del"
             confirmMessage={`Delete ${query.name}`}
             onConfirm={e => deleteQuery(query._id)}
-            disabled={!canDelete}
+            disabled={!query.canDelete}
           >
             Delete
           </DeleteConfirmButton>

diff --git a/client/src/queryEditor/toolbar/ToolbarShareQueryButton.js b/client/src/queryEditor/toolbar/ToolbarShareQueryButton.js
@@ -20,7 +20,7 @@ function ToolbarShareQueryButton({ shared, setQueryState }) {
   function handleClick() {
     setQueryState(
       'acl',
-      shared ? [] : [{ userId: '__EVERYONE__', write: true }]
+      shared ? [] : [{ groupId: '__EVERYONE__', write: true }]
     );
   }
 

diff --git a/client/src/stores/queries.js b/client/src/stores/queries.js
@@ -17,7 +17,10 @@ export const NEW_QUERY = {
   chartConfiguration: {
     chartType: '',
     fields: {} // key value for chart
-  }
+  },
+  canRead: true,
+  canWrite: true,
+  canDelete: true
 };
 
 export const initialState = {

diff --git a/server/lib/decorateQueryUserAccess.js b/server/lib/decorateQueryUserAccess.js
@@ -0,0 +1,46 @@
+const consts = require('./consts');
+
+// Not sure where to put utilities like these
+
+/**
+ * Returns a decorated query object with canRead, canWrite, and canDelete properties
+ * @param {object} query
+ * @param {object} user
+ */
+function decorateQueryUserAccess(query, user) {
+  const { ...clone } = query;
+  clone.canRead = false;
+  clone.canWrite = false;
+  clone.canDelete = false;
+
+  if (user.role === 'admin' || user.email === clone.createdBy) {
+    clone.canRead = true;
+    clone.canWrite = true;
+    clone.canDelete = true;
+  } else if (clone.acl.length) {
+    const writeAcl = clone.acl
+      // filter acl records that match for this user
+      .filter(
+        acl =>
+          acl.groupId === consts.EVERYONE_ID ||
+          acl.userId === user._id ||
+          acl.userEmail === user.email
+      )
+      // and return first one that has write
+      .find(a => a.write === true);
+    clone.canWrite = Boolean(writeAcl);
+
+    // A record in ACL allows read permissions
+    const canRead = query.acl.find(
+      acl =>
+        acl.groupId === consts.EVERYONE_ID ||
+        acl.userId === user._id ||
+        acl.userEmail === user.email
+    );
+    clone.canRead = Boolean(canRead);
+  }
+
+  return clone;
+}
+
+module.exports = decorateQueryUserAccess;
diff --git a/server/migrations/04-00110-query-acl-data.js b/server/migrations/04-00110-query-acl-data.js
@@ -1,5 +1,3 @@
-const consts = require('../lib/consts');
-
 /**
  * @param {import('sequelize').QueryInterface} queryInterface
  * @param {import('../lib/config')} config
@@ -14,7 +12,7 @@ async function up(queryInterface, config, appLog, nedb) {
     const records = queries.map(query => {
       return {
         query_id: query._id,
-        user_id: consts.EVERYONE_ID,
+        user_id: '__EVERYONE__', // value in consts.EVERYONE_ID at time of migration
         write: true,
         created_at: new Date(),
         updated_at: new Date()

diff --git a/server/migrations/04-00120-query-acl-remove-old-constraint.js b/server/migrations/04-00120-query-acl-remove-old-constraint.js
@@ -0,0 +1,18 @@
+/**
+ * @param {import('sequelize').QueryInterface} queryInterface
+ * @param {import('../lib/config')} config
+ * @param {import('../lib/logger')} appLog
+ * @param {object} nedb - collection of nedb objects created in /lib/db.js
+ */
+// eslint-disable-next-line no-unused-vars
+async function up(queryInterface, config, appLog, nedb) {
+  // Remove unique constraint on query_id_user_id (it'll be added again switched around later)
+  await queryInterface.removeConstraint(
+    'query_acl',
+    'query_acl_query_id_user_id_key'
+  );
+}
+
+module.exports = {
+  up
+};
diff --git a/server/migrations/04-00121-query-acl-add-user-email.js b/server/migrations/04-00121-query-acl-add-user-email.js
@@ -0,0 +1,18 @@
+const Sequelize = require('sequelize');
+
+/**
+ * @param {import('sequelize').QueryInterface} queryInterface
+ * @param {import('../lib/config')} config
+ * @param {import('../lib/logger')} appLog
+ * @param {object} nedb - collection of nedb objects created in /lib/db.js
+ */
+// eslint-disable-next-line no-unused-vars
+async function up(queryInterface, config, appLog, nedb) {
+  await queryInterface.addColumn('query_acl', 'user_email', {
+    type: Sequelize.STRING
+  });
+}
+
+module.exports = {
+  up
+};
diff --git a/server/migrations/04-00122-query-acl-add-group-id.js b/server/migrations/04-00122-query-acl-add-group-id.js
@@ -0,0 +1,18 @@
+const Sequelize = require('sequelize');
+
+/**
+ * @param {import('sequelize').QueryInterface} queryInterface
+ * @param {import('../lib/config')} config
+ * @param {import('../lib/logger')} appLog
+ * @param {object} nedb - collection of nedb objects created in /lib/db.js
+ */
+// eslint-disable-next-line no-unused-vars
+async function up(queryInterface, config, appLog, nedb) {
+  await queryInterface.addColumn('query_acl', 'group_id', {
+    type: Sequelize.STRING
+  });
+}
+
+module.exports = {
+  up
+};
diff --git a/server/migrations/04-00123-query-acl-remove-user-id-not-null.js b/server/migrations/04-00123-query-acl-remove-user-id-not-null.js
@@ -0,0 +1,20 @@
+const Sequelize = require('sequelize');
+
+/**
+ * @param {import('sequelize').QueryInterface} queryInterface
+ * @param {import('../lib/config')} config
+ * @param {import('../lib/logger')} appLog
+ * @param {object} nedb - collection of nedb objects created in /lib/db.js
+ */
+// eslint-disable-next-line no-unused-vars
+async function up(queryInterface, config, appLog, nedb) {
+  // remove not-null constraint for user_id
+  await queryInterface.changeColumn('query_acl', 'user_id', {
+    type: Sequelize.STRING,
+    allowNull: true
+  });
+}
+
+module.exports = {
+  up
+};
diff --git a/server/migrations/04-00124-query-acl-add-constraint-user-email-query.js b/server/migrations/04-00124-query-acl-add-constraint-user-email-query.js
@@ -0,0 +1,18 @@
+/**
+ * @param {import('sequelize').QueryInterface} queryInterface
+ * @param {import('../lib/config')} config
+ * @param {import('../lib/logger')} appLog
+ * @param {object} nedb - collection of nedb objects created in /lib/db.js
+ */
+// eslint-disable-next-line no-unused-vars
+async function up(queryInterface, config, appLog, nedb) {
+  // Add unique constraint for (user_email, query_id) and (group_id, query_id)
+  await queryInterface.addConstraint('query_acl', ['user_email', 'query_id'], {
+    type: 'unique',
+    name: 'query_acl_user_email_query_id_key'
+  });
+}
+
+module.exports = {
+  up
+};
diff --git a/server/migrations/04-00125-query-acl-add-constraint-group-query.js b/server/migrations/04-00125-query-acl-add-constraint-group-query.js
@@ -0,0 +1,17 @@
+/**
+ * @param {import('sequelize').QueryInterface} queryInterface
+ * @param {import('../lib/config')} config
+ * @param {import('../lib/logger')} appLog
+ * @param {object} nedb - collection of nedb objects created in /lib/db.js
+ */
+// eslint-disable-next-line no-unused-vars
+async function up(queryInterface, config, appLog, nedb) {
+  await queryInterface.addConstraint('query_acl', ['group_id', 'query_id'], {
+    type: 'unique',
+    name: 'query_acl_group_id_query_id_key'
+  });
+}
+
+module.exports = {
+  up
+};
diff --git a/server/migrations/04-00126-query-acl-add-constraint-user-id-query.js b/server/migrations/04-00126-query-acl-add-constraint-user-id-query.js
@@ -0,0 +1,18 @@
+/**
+ * @param {import('sequelize').QueryInterface} queryInterface
+ * @param {import('../lib/config')} config
+ * @param {import('../lib/logger')} appLog
+ * @param {object} nedb - collection of nedb objects created in /lib/db.js
+ */
+// eslint-disable-next-line no-unused-vars
+async function up(queryInterface, config, appLog, nedb) {
+  // Swap unique constraint around for (query_id, user_id) for index strategy, then add query_id index
+  await queryInterface.addConstraint('query_acl', ['user_id', 'query_id'], {
+    type: 'unique',
+    name: 'query_acl_user_id_query_id_key'
+  });
+}
+
+module.exports = {
+  up
+};
diff --git a/server/migrations/04-00127-query-acl-add-index-query-id.js b/server/migrations/04-00127-query-acl-add-index-query-id.js
@@ -0,0 +1,17 @@
+/**
+ * @param {import('sequelize').QueryInterface} queryInterface
+ * @param {import('../lib/config')} config
+ * @param {import('../lib/logger')} appLog
+ * @param {object} nedb - collection of nedb objects created in /lib/db.js
+ */
+// eslint-disable-next-line no-unused-vars
+async function up(queryInterface, config, appLog, nedb) {
+  await queryInterface.addIndex('query_acl', {
+    fields: ['query_id'],
+    name: 'query_acl_query_id'
+  });
+}
+
+module.exports = {
+  up
+};
diff --git a/server/migrations/04-00128-query-acl-move-everyone-id-to-group.js b/server/migrations/04-00128-query-acl-move-everyone-id-to-group.js
@@ -0,0 +1,24 @@
+/**
+ * @param {import('sequelize').QueryInterface} queryInterface
+ * @param {import('../lib/config')} config
+ * @param {import('../lib/logger')} appLog
+ * @param {object} nedb - collection of nedb objects created in /lib/db.js
+ */
+// eslint-disable-next-line no-unused-vars
+async function up(queryInterface, config, appLog, nedb) {
+  // For any acl entries created in 04-00110, move the "__EVERYONE__" value to groupId
+  await queryInterface.bulkUpdate(
+    'query_acl',
+    {
+      user_id: null,
+      group_id: '__EVERYONE__' // value in consts.EVERYONE_ID at time of migration
+    },
+    {
+      user_id: '__EVERYONE__' // value in consts.EVERYONE_ID at time of migration
+    }
+  );
+}
+
+module.exports = {
+  up
+};