armand1m · armand1m · Aug 29, 2020 · Aug 27, 2020 · Aug 28, 2020 · Aug 29, 2020
diff --git a/.dockerignore b/.dockerignore
@@ -9,3 +9,4 @@ Dockerfile
 cloudbuild.yaml
 cloudbuild-pr.yaml
 ./kubernetes
+.env*
diff --git a/README.md b/README.md
@@ -2,7 +2,7 @@
 
 This application is deployed at https://go.armand1m.dev
 
-This is my personal version of Go Links built with [Next.js](https://nextjs.org/) and [GraphQL](http://graphql.org/).
+This is my personal version of Go Links powered by [Next.js](https://nextjs.org/) , [GraphQL](http://graphql.org/) (through PostGraphile) and Auth0.
 
 <div style="max-width: 700px">
   <img src="./.github/redirect.gif?raw=true">
@@ -23,12 +23,13 @@ This is my personal version of Go Links built with [Next.js](https://nextjs.org/
 - [x] Delete Links
 - [ ] Edit Links
 - [x] Redirect Links
-- [x] Link Usage Count
-- [ ] Auth
-- [ ] Security
+- [x] Auth
+  - [x] Powered by Auth0
+- [x] Security
+  - [x] Row Level Security using Auth0 Roles and Permissions
 - [ ] Link Description
 - [ ] Link Suggestion on 404
-- [ ] Link Usage Metrics
+- [x] Link Usage Metrics
 - [ ] Link Ownership
 
 ## Deploying
@@ -78,7 +79,7 @@ kubectl apply -f ./kubernetes/istio/destination-rule.yaml
 
 armand1m/golinks is a Next.js app using GraphQL.
 
-The database must be a [PostgreSQL 12.x](http://postgresql.org/) database as the GraphQL API is generated using [Postgraphile](https://www.graphile.org/postgraphile/). 
+The database must be a [Postgres 12.x](http://postgresql.org/) database as the GraphQL API is generated using [Postgraphile](https://www.graphile.org/postgraphile/) and leverages features like Row Level Security only available from Postgres 9.6+.
 
 PostGraphile is then used as a NPM module and served through Next.js routes itself, so you don't have to worry about CORS, and the api is initialized together with the Next.js application.
 
@@ -91,7 +92,7 @@ GraphQL Type definitions are generated on application startup during development
 Start the database:
 
 ```sh
-docker-compose up db -d
+docker-compose up -d db
 ```
 
 Prepare the project and run in development mode:

diff --git a/components/LinkTable/index.tsx b/components/LinkTable/index.tsx
@@ -1,17 +1,26 @@
-import { Table, Text, Link as FannyLink, Button } from 'bumbag';
+import { Box, Table, Text, Link as FannyLink, Button } from 'bumbag';
 
 import { GetAllLinksQuery } from '../../lib/queries/getAllLinks.graphql';
 
 interface Props {
   data: GetAllLinksQuery;
+  isDeleteEnabled: boolean;
   onDelete: (linkId: string) => void | Promise<void>;
 }
 
-export const LinkTable: React.FC<Props> = ({ data, onDelete }) => {
+export const LinkTable: React.FC<Props> = ({
+  data,
+  isDeleteEnabled,
+  onDelete,
+}) => {
   const links = data?.links;
 
   if (links?.nodes.length === 0) {
-    return <Text>There are no links yet. Create one!</Text>;
+    return (
+      <Box>
+        <Text>There are no golinks available.</Text>
+      </Box>
+    );
   }
 
   return (
@@ -21,7 +30,9 @@ export const LinkTable: React.FC<Props> = ({ data, onDelete }) => {
           <Table.HeadCell>Alias</Table.HeadCell>
           <Table.HeadCell>Destination</Table.HeadCell>
           <Table.HeadCell textAlign="right">Usage</Table.HeadCell>
-          <Table.HeadCell textAlign="right">Actions</Table.HeadCell>
+          {isDeleteEnabled && (
+            <Table.HeadCell textAlign="right">Actions</Table.HeadCell>
+          )}
         </Table.Row>
       </Table.Head>
       <Table.Body>
@@ -44,15 +55,17 @@ export const LinkTable: React.FC<Props> = ({ data, onDelete }) => {
                   </FannyLink>
                 </Table.Cell>
                 <Table.Cell textAlign="right">
-                  {link.usage}
-                </Table.Cell>
-                <Table.Cell textAlign="right">
-                  <Button
-                    palette="danger"
-                    onClick={() => onDelete(link.id)}>
-                    Delete
-                  </Button>
+                  {link.linkUsageMetrics.totalCount}
                 </Table.Cell>
+                {isDeleteEnabled && (
+                  <Table.Cell textAlign="right">
+                    <Button
+                      palette="danger"
+                      onClick={() => onDelete(link.id)}>
+                      Delete
+                    </Button>
+                  </Table.Cell>
+                )}
               </Table.Row>
             );
           })}

diff --git a/database/1-links-table.sql b/database/1-links-table.sql
diff --git a/database/1-tables.sql b/database/1-tables.sql
@@ -0,0 +1,29 @@
+CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
+
+DROP TABLE IF EXISTS public.links CASCADE;
+CREATE TABLE public.links (
+	id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
+	alias VARCHAR ( 50 ) UNIQUE NOT NULL,
+	url VARCHAR ( 255 ) NOT NULL, 
+	created_at TIMESTAMP NOT NULL DEFAULT NOW()
+);
+
+comment on table public.links is 'A link alias posted by an user.';
+comment on column public.links.id is 'The id for a link alias.';
+comment on column public.links.alias is 'The alias for an url. It must be unique.';
+comment on column public.links.url is 'The link alias url.';
+comment on column public.links.created_at is 'The time this link alias was created.';
+
+DROP TABLE IF EXISTS public.link_usage_metrics CASCADE;
+CREATE TABLE public.link_usage_metrics (
+	id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
+  link_id uuid NOT NULL REFERENCES public.links ON DELETE CASCADE,
+	accessed_at TIMESTAMP NOT NULL DEFAULT NOW()
+);
+
+CREATE INDEX ON public.link_usage_metrics (link_id);
+
+comment on table public.link_usage_metrics is 'A link usage metric posted by the application when a link is accessed.';
+comment on column public.link_usage_metrics.id is 'The id for this metric record.';
+comment on column public.link_usage_metrics.link_id is 'The id of the link being accessed.';
+comment on column public.link_usage_metrics.accessed_at is 'The time this link was accessed.';
diff --git a/database/2-jwt-token-type.sql b/database/2-jwt-token-type.sql
diff --git a/database/2-roles.sql b/database/2-roles.sql
@@ -0,0 +1,5 @@
+CREATE ROLE postgraphile;
+
+-- Not used yet
+CREATE ROLE viewer;
+CREATE ROLE editor;
diff --git a/database/3-functions.sql b/database/3-functions.sql
@@ -0,0 +1,13 @@
+create function get_current_permissions() returns json as $$
+  select nullif(current_setting('jwt.claims.permissions', true), '[]')::json;
+$$ language sql stable SECURITY DEFINER; 
+
+create function has_permission(permission text) returns boolean as $$
+  with claims as (
+    select 
+      get_current_permissions() as permissions
+  )
+  select count(claims.permissions) > 0
+  from claims
+  where claims.permissions::jsonb ? permission; 
+$$ language sql stable SECURITY DEFINER;
diff --git a/database/3-users-table.sql b/database/3-users-table.sql
diff --git a/database/4-authenticate-function.sql b/database/4-authenticate-function.sql
diff --git a/database/4-policies.sql b/database/4-policies.sql
@@ -0,0 +1,54 @@
+CREATE POLICY read_links
+  ON public.links
+  FOR SELECT
+  USING (
+    has_permission('read:golinks')
+  );
+
+CREATE POLICY update_links
+  ON public.links
+  FOR UPDATE
+  USING (
+    has_permission('update:golinks')
+  )
+  WITH CHECK (
+    has_permission('update:golinks')
+  );
+
+CREATE POLICY create_links
+  ON public.links
+  FOR INSERT
+  WITH CHECK (
+    has_permission('create:golinks')
+  );
+
+CREATE POLICY delete_links
+  ON public.links
+  FOR DELETE
+  USING (
+    has_permission('delete:golinks')
+  );
+
+ALTER TABLE public.links ENABLE ROW LEVEL SECURITY;
+ALTER TABLE public.links FORCE ROW LEVEL SECURITY;
+
+GRANT ALL ON public.links TO postgraphile;
+
+CREATE POLICY read_link_metric
+  ON public.link_usage_metrics
+  FOR SELECT
+  USING (
+    has_permission('read:golinks')
+  );
+
+CREATE POLICY create_link_metric
+  ON public.link_usage_metrics
+  FOR INSERT
+  WITH CHECK (
+    has_permission('read:golinks')
+  );
+
+ALTER TABLE public.link_usage_metrics ENABLE ROW LEVEL SECURITY;
+ALTER TABLE public.link_usage_metrics FORCE ROW LEVEL SECURITY;
+
+GRANT ALL ON public.link_usage_metrics TO postgraphile;
diff --git a/docker-compose.yml b/docker-compose.yml
@@ -9,10 +9,8 @@ services:
       - 3000:3000/tcp
     networks:
       - overlay
-    environment:
-      DATABASE_CONNECTION_STRING: postgres://dev:dev@db:5432/golinks
-      DATABASE_SCHEMA: public
-      NODE_ENV: production
+    env_file:
+      - .env.local
     depends_on:
       - db
 
@@ -26,10 +24,10 @@ services:
       - overlay
     volumes:
       - db-data:/var/lib/postgresql/data
-      - ./database/1-links-table.sql:/docker-entrypoint-initdb.d/1-links-table.sql
-      - ./database/2-jwt-token-type.sql:/docker-entrypoint-initdb.d/2-jwt-token-type.sql
-      - ./database/3-users-table.sql:/docker-entrypoint-initdb.d/3-users-table.sql
-      - ./database/4-authenticate-function.sql:/docker-entrypoint-initdb.d/4-authenticate-function.sql
+      - ./database/1-tables.sql:/docker-entrypoint-initdb.d/1-tables.sql
+      - ./database/2-roles.sql:/docker-entrypoint-initdb.d/2-roles.sql
+      - ./database/3-functions.sql:/docker-entrypoint-initdb.d/3-functions.sql
+      - ./database/4-policies.sql:/docker-entrypoint-initdb.d/4-policies.sql
     ports:
       - 5432:5432/tcp