Roles Seed

Loading "Managing Roles and Permissions"

👨‍💼 Perhaps in the future we'll want pretty fine-grained roles and permissions, but for now we really only have two roles:

admin - which can do anything to everything
user - which can do anything to their own data

We want to apply these roles and permissions to all new users who are created. That means that our database needs to have these values before we can create any users. Our

prisma/seed.ts

is intended to seed the local development database, but we're going to need this seeding in production as well.

So, what we need is to manually update our migration.sql file we just made for our permissions to create the roles and permissions. That way when we deploy our updates to production, the production database will automatically get these roles and permissions.

The tricky bit is knowing how to write the SQL to create the roles and permissions. If you're a SQL wiz, feel free to write it by hand, but for the rest of us, here's a strategy:

Create a temporary script file that generates a temporary database and seeds it with the data we want:

Feel free to use my script

import { PrismaClient } from '@prisma/client'
import { execaCommand } from 'execa'

const datasourceUrl = 'file:./tmp.ignored.db'
console.time('🗄️ Created database...')
await execaCommand('npx prisma migrate deploy', {
	stdio: 'inherit',
	env: { DATABASE_URL: datasourceUrl },
})
console.timeEnd('🗄️ Created database...')

const prisma = new PrismaClient({ datasourceUrl })

console.time('🔑 Created permissions...')
const entities = ['user', 'note']
const actions = ['create', 'read', 'update', 'delete']
const accesses = ['own', 'any']
for (const entity of entities) {
	for (const action of actions) {
		for (const access of accesses) {
			await prisma.permission.create({ data: { entity, action, access } })
		}
	}
}
console.timeEnd('🔑 Created permissions...')

console.time('👑 Created roles...')
await prisma.role.create({
	data: {
		name: 'admin',
		permissions: {
			connect: await prisma.permission.findMany({
				select: { id: true },
				where: { access: 'any' },
			}),
		},
	},
})
await prisma.role.create({
	data: {
		name: 'user',
		permissions: {
			connect: await prisma.permission.findMany({
				select: { id: true },
				where: { access: 'own' },
			}),
		},
	},
})
console.timeEnd('👑 Created roles...')

console.log('✅ all done')

Run the script with node tmp.ignored.js
Create a "dump" of all the data in the temporary database using sqlite3 (install instructions in exercise 9 of the Data workshop):
```
sqlite3 ./prisma/tmp.ignored.db .dump > tmp.ignored.sql
```
Copy the INSERT statements (only the INSERT statements) from the tmp.ignored.sql file into the permissions migration.sql file in prisma/migrations.

Once you've done that, you can update

prisma/seed.ts

to assign the "user" roles to the generated users and the "admin" and "user" roles to the Kody user.

Once you've finished updating the seed script, you can reset the database and run the seed with:

npx prisma migrate reset --force

But before we wrap this up, you'll want to make sure we create all new users with the user role, otherwise new users won't have the right permissions!

We do that in

app/utils/auth.server.ts

, so get that updated too please.

08. Role-Based Access/02. Roles Seed (💪 problem)

Roles Seed