DEV Community: Daniel da Rocha

Postgres RLS and policies: best practices?

Daniel da Rocha — Sun, 08 Dec 2019 05:46:10 +0000

I am currently exploring Postgres's Row Level Security and related policies for my project, and have a question about best practices:

If I have these tables:

box
id
name
user_id

thing
id
big_box_id
name

I have a select policy which checks the user_id agains JWT claims. This let's me only select my own boxes.

Question: What's the best way to prevent users to select things which do not belong to them?

As I see it, there are two options:

Option 1: add a user_id column to thing and have each item also store its user_id to be used on policies (same as in box)

Option 2: on the select policy for thing, add a check on the user_id of the parent box

My intuition says option 2 is cleaner, but I wonder how would that go when things get deeper, let's say my things can also have children, then I have to travel back several levels to the parent box within my select policy?

Any insights are extremely appreciated!!

How to deploy backend apps with Dokku

Daniel da Rocha — Sat, 21 Sep 2019 14:02:14 +0000

I recently moved all my apps from Heroku to a DigitalOcean droplet using Dokku. This centralized things and I saved me a bunch of money. Here is a recipe I follow for each new app to get it up-and-running.

For clarification, I am an amateur. I build apps for fun, and I am learning as I go. My advice might be flawed, but it works for me, and it might help you as well :)

What do I build?

My projects are all built on a Vue-based frontend (either using Vue-CLI, Nuxt, Vuepress, or Gridsome). When I need a database, I set up a Postgres instance with Postgraphile, which gives me a GraphQL endpoint in the backend. Just recently, I started exploring Strapi as an attractive CMS, which also uses Postgres as a database and delivers data to my frontend through a GraphQL endpoint.

Where do I host?

For all my frontend needs, Netlify is more than enough. It is free, easy to use, has excellent interface and usability. It is perfect for Vue-powered projects.

Unfortunately, Netlify won’t work for my backend projects which rely on Postgres. I tried many services but ended up choosing Dokku on DigitalOcean as the best solution all my backend needs.

What else have I tried?

Once you start using Postgres in the backend, there are two options: self-hosted or hosted database services.

For a long time, I thought I had to avoid self-hosting (and self-maintaining) a Postgres database. Sharding? Replication? Backups and restores? Upgrades? It all used to give me chills.

So I spent most of my early development days trying out hosted database services out there.

Heroku

Yes, Heroku has a generous free tier with a free Postgres add-on.

However, free comes to a cost when you realize you’ll cross some of its limits faster than you think. When you decide to fix all issues and upgrades your services, you are already paying much more than free.

AWS RDS

I then moved to AWS RDS, creating a Postgres instance in a container.

I thought I could host all my databases that for minimal cost, as they offered a very generous free tier. However, once the free tier ended, I was paying almost $50 a month for the database needs of my little side projects. This needed to change.

Why Dokku on Digital Ocean?

After a while paying the bill, I realized I was (insert metaphor). Of course, there are advantages of hosted database solutions. However, for the scale of my side projects, I need very little. After all, if something goes wrong and my apps go offline, I can restore them from backups and get them running again.

Therefore, my requirements were simple:

Scheduled DB backups
Easy Git deployments
Low price

That is where Dokku comes into play. It is just like Heroku, but without the pretty UI. You have to host it on your droplet, and it is all command-line-based.

However, it offers it all: you can easily create apps, add domains, set variables, add Postgres databases, set up scheduled backups, and deploy via git.

All in one little Digital Ocean droplet costing me 5USD/month.

What’s my process?

Every time I start a new app, I follow a series of steps which make the whole process very simple. This recipe assumes:

You already have a DO droplet with Dokku installed. If not, follow this tutorial
You already ensured the safety of your droplet, removing root login and creating a new user who can login via SSH. If not, follow this tutorial
You logged into your droplet via SSH

Create the app

You can use Dokku by typing dokku followed by the command and other options. Type dokku help to see a list of all commands:

~ dokku help
Usage: dokku [--quiet|--trace|--rm-container|--rm|--force] COMMAND <app> [command-specific-options]

Primary help options, type “dokku COMMAND:help” for more details, or dokku help --all to see all commands.

Commands:

    apps                     Manage Dokku apps
    buildpacks               Manages buildpack settings for an app
    certs                    Manage Dokku apps SSL (TLS) certs
    checks                   Manage zero-downtime settings
    config                   Manages global and app-specific config vars
    docker-options           Pass options to Docker the various stages of an app
    domains                  Manage vhost domains used by the Dokku proxy
    enter                    Connect to a specific app container
    events                   Show the last events (-t follows)
    git                      Manages the git integration for an app
    help                     Print the list of commands
    logs                     Output app logs
    network                  Manages network settings for an app
    nginx                    Interact with Dokku’s Nginx proxy
    proxy                    Manage the proxy used by dokku on a per app

As you can see, you can also type dokku COMMAND:help for further help on specific commands. As we want to create an app, let’s check how to use the apps command:

~ dokku apps:help
Usage: dokku apps[:COMMAND]
Manage Dokku apps

Example:
$ dokku apps:list
=====> My Apps
example
example2

Additional commands:
    apps                              [DEPRECATED] Alias for apps:list
    apps:clone <old-app> <new-app>    Clones an app
    apps:create <app>                 Create a new app
    apps:destroy <app>                Permanently destroy an app
    apps:exists <app>                 Checks if an app exists
    apps:list                         List your apps
    apps:lock <app>                   Locks an app for deployment
    apps:locked <app>                 Checks if an app is locked for deployment
    apps:rename <old-app> <new-app>   Rename an app
    apps:report [<app>] [<flag>]      Display report about an app
    apps:unlock <app>                 Unlocks an app for deployment

Easy to figure out what to do next, right?

~ dokku apps:create appname
-----> Creating appname... done

You can, of course, use any name you want. It is the same as creating a new app in Heroku. It also gives you a remote git repository which you can add to your local app:

git remote add dokku dokku@<droplet-ip>:appname

Add the droplet IP in the command above before adding. I am assuming you already use git to deploy apps, so I will not get much into detail on that. If not, check this tutorial.

Add a domain

I always like to have my backend hosted on a subdomain of my apps. For example, if my app is at awarded.to, I want its API endpoint to be at api.awarded.to.

You do not need to do this (you can always access your app using the droplet’s IP address), but it makes it all clean and friendly. It also helps when you have several apps in the same droplet. Moreover, it is straightforward to do:

First, you need to modify your domain’s DNS settings. You’ll need to add an A record named after your subdomain and pointing to the droplet’s IP address. On Netlify, it looks like this:

The instructions for your registrar might differ. Just search its docs for "add DNS subdomain" and you should find quick answers.

Now back on Dokku, you’ll add the subdomain to your app. For that, type:

~ dokku domains:add appname domain

The domain should be without the http protocol (just api.awarded.to instead of https://api.awarded.to).

To keep it all safe and secure, you should also enforce https on your endpoint. This article walks you through the steps necessary to ensure encryption when accessing your backend endpoint: Effortlessly add HTTPS to Dokku, with Let’s Encrypt

Add Postgres

As in Heroku, adding a Postgres service to your app is pretty simple. First, you create a new database:

~ dokku postgres:create dbname

# Then link it to your app
~ dokku postgres:link appname dbname

That’s it. It creates an environment variable called DATABASE_URL with your database address. If your app is configured to access the database via this environment variable, you are good to go. Otherwise, you can set other variables as necessary, based on this value.

You should also set up automatic backups on your database. I usually set up a cron job to create a database dump every day, and upload it to an AWS S3 bucket.

For that, you’ll first need both an S3 bucket and an IAM user with the appropriate permissions. Create a bucket on S3 and a user on AWS IAM with the following policy:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Action": [
                "s3:ListAllMyBuckets",
                "s3:GetBucketLocation"
            ],
            "Effect": "Allow",
            "Resource": [
                "arn:aws:s3:::*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": "s3:*",
            "Resource": [
                "arn:aws:s3:::lutasanticapital/*",
                "arn:aws:s3:::lutasanticapital"
            ]
        }
    ]
}

Copy its credentials (Access Key ID and Secret access key), which you’ll use to configure the backups on Dokku:

# setup s3 backup authentication
dokku postgres:backup-auth dbname AWS_ACCESS_KEY_ID AWS_SECRET_ACCESS_KEY

# backup the `appname` service to the `BUCKET_NAME` bucket on AWS
dokku postgres:backup appname BUCKET_NAME

# schedule a backup
# CRON_SCHEDULE is a crontab expression, eg. "0 3 * * *" for each day at 3am
dokku postgres:backup-schedule appname CRON_SCHEDULE BUCKET_NAME

Deploy and enjoy

That is pretty much it. If your app is not overly complicated, it should be working right after the first git push to the dokku remote:

~ git push dokku master

Keep an eye on the log to see if the app deployed correctly and to correct any error that may arise.

Your app should then be live on your chosen domain, just waiting for your frontend to connect!

Help me to improve it

Let me know if you have any questions.

As I wrote earlier, I am an amateur. Please, if you see mistakes or opportunities for improvement, let me know! I am always happy to learn how to make things better.

GraphQL pagination

Daniel da Rocha — Thu, 07 Feb 2019 17:41:44 +0000

I've been playing a lot with GraphQL and Apollo lately, but I am usually unsure about best practices when it comes to querying the server and getting results back.

Now, say I have a list of locations which I want to display in a paginated list:

Let's say I decide to use Apollo's pagination capabilities and get 10 locations at a time:

If I want to filter by city using a dropdown with all cities in the DB, the paginated results will return me only the cities within those 10 locations. Should I then make a separate query for that (eg. allCities {...} )?
Then if I select a city as a filter, I should make yet another new query filtered by the city, right?

Or should I just ditch Apollo's pagination and query all locations, and do all the pagination and filtering in the client? In that case, how much is too much data for an initial load?

Or am I missing something?

Please help!

Thanks!!
Greetings from Berlin...

How do you rank your ranked content?

Daniel da Rocha — Tue, 29 Jan 2019 14:21:48 +0000

If you have a website where content is displayed based on user voting/popularity, how do you rank your content and why?

I am aware of some algorithms, such as Hackernews's and Reddit's, but I am interested in reasons why you chose the right one for your content.

Docker from development to deployment

Daniel da Rocha — Thu, 03 Jan 2019 06:59:34 +0000

Hi!

I've been trying to solve this for days now so I might finally just ask for help:

In my development docker-compose configuration, I have 4 containers:

front: Vue app started using npm run serve on port 80
back: Koa2 server for my API (Postgraphile for GraphQL)
database: Postgres container
front-balancer: Nginx server, sends / to front, /graphql to back

It works fine locally (although I probably should skip Nginx for development...)

Now I want to deploy it, and that's where my headaches began. My initial understanding is that I should not use npm run serve for front on production, but instead build my Vue app and serve the static files with Nginx.
But does that mean I do not need my front container in production and can just mount the built files to my Nginx container?

Or how would you approach this?

Then comes the actual deployment. I am still at a loss as to where (DO, Heroku, AWS??) and how to deploy it (CD workflow). But that might come as a later question once I figure out the step above!

Thanks in advance!

Static site with markdown pages, but with bits of dynamic content in it?

Daniel da Rocha — Fri, 26 Oct 2018 12:42:47 +0000

Hello!

I have long thought about converting my Wordpress blog into a static site with all the posts as markdown files, using either Hugo, or Jekyll, or Gridsome, etc.

However, I have a custom shortcodes in each of my posts which grab data from a database and convert it to elements in the post.

This:

Becomes this:

I tried doing some research about how to make this situation work in a static site, but I in a bit of a loss as to where to start.

Would it still work as a static site with bits of dynamic content being called at render time?
Or would the dynamic content be called at build time only (which would be totally ok as well)?

So I thought I'd ask you guys: anyone with similar experiences or tips about this?

Thanks!
Daniel

Do you manage your own Postgres or use a DBaaS?

Daniel da Rocha — Wed, 19 Sep 2018 14:36:41 +0000

I read here and there some people saying how they want to use a hosted DB service for they Postgres database instead of hosting it on their own VPS.

I did some research and found interesting solutions such as Heroku Postgres, Google Cloud SQL, Amazon RDS, ElephantSQL, Compose, etc.

However, I quickly understood that anything above the free/dev tier could have costs escalate quite quickly.

So, what to do? is it that hard/problematic to host my own Postgres?
Do you use DBaaS? If so, what do you recommend?

Thanks!!
Greetings from Berlin!

Explain Middleware Like I'm Five

Daniel da Rocha — Wed, 05 Sep 2018 14:57:20 +0000

I'm trying to wrap my head around servers and how to set up one with all the stuff I wanna use on my app.

Middlewares keep popping up... I guess it is something that gets loaded by the server in the middle of sth?? Please help!

Wanna play with GraphQL on an existing database

Daniel da Rocha — Fri, 24 Aug 2018 11:07:40 +0000

I spend some time earlier this year building a REST-API for a personal app project (Vue in the frontend, Python + Flask + Postgres for the backend API). It is pretty complex and works fine. My PostgreSQL database if full of data now and all tables are working well together.

But lately, I've been having the itch to try out GraphQL... just for fun. What would you guys recommend? My app is using Vue.

PostGraphile? seems easy to start with, but will it be sustainable in the long run? Apollo? seems like the "standard" pair to Vue?

Anyone ever did this, converting a REST API to a GraphQL API?

Will it really be fun? 🤔

Thanks!
best,
Daniel

Vue+Flask on DigitalOcean: how to proceed?

Daniel da Rocha — Tue, 19 Jun 2018 06:14:02 +0000

Hi!

Following my earlier post, I am now trying to have my app running on a single Digital Ocean droplet.

But how should I approach it?

Is it correct to have my Vue app being served to the outside world and the Flask API being served locally in the droplet, and have my Vue app access it via localhost:port?

Or do I need two server blocks, one for the Vue app and one for the Flask API?

(the API is for internal use only, I do not intend to have a public endpoint to it)

Thanks for the help!
Daniel

Ready to deploy! Vue + Flask, but where to deploy it to?

Daniel da Rocha — Mon, 21 May 2018 23:27:30 +0000

Hi all!

After some weeks toiling with my app, I would love to finally deploy it to a production server. The thing is built as follows:

Backend: Python Flask + Postgresql as an API endpoint
Frontend: Vue.js SPA with axios calls to my API to populate data

So where would you put something like this? I am thinking, Heroku or Digital Ocean? Or not? Any advice on what should I watch out for?

As much as the idea of a static site is appealing (using nuxt.js or VuePress), it would not work for me, as I have a lot of relational data that might be displayed by the user in unpredictable ways. Also, there will be a user account system on v2.0, so I guess this disqualifies the site for Netlify, right?

Thanks a lot!
Best,
Daniel

vue/vuex - confused about when to load state

Daniel da Rocha — Tue, 24 Apr 2018 04:09:53 +0000

Hi!

I have been learning Vue recently, and I love it. However, I have a nagging question.

I have all my data being called from the server via API calls (axios + Flask-RESTful).

I could load all my store modules with all the data from the server at once, and then filter it as needed in my views (one "big" API call). Or I could load only the filtered data as needed (multiple API calls, filling the stores with specific data each time).

What would you do?

My first reaction was to load from the server as needed in the views, but then I get trouble on reload when the data is not loading and I have to go back and forth in my router links to reload the store...

Any tips are appreciated!!
Greetings from Beijing
Daniel