Neon Roles

Setting up a neon project is kind of like setting up a fresh install of Postgres that's fully managed for you. Within a project, you can setup databases and roles, and within the database you can manage your tables and schemas. It's all Postgres, but Neon makes it easier to manage. Way easier.

Neon isn't 100% Postgres, it's build on top of Postgres. This gives us a few extra features but also comes with some limitations. That being said, when I'm talking about Neon and I use terms like database or role or schema, just picture a normal Postgres database.

When we create a new project in neon, it comes with a database called neondb a default role that owns that database.

We could create a new database with a different name, but the pre-created neondb is fine, so we can just leave that alone. We will, however, create some new roles.

Roles

Whenever we create a new database, it's not a bad idea (some would say it's best practice) to create new roles for our specific needs. For a web app, we'll generally benefit from having two distinct roles:

  • Migration Role: Responsible for running migrations and reshaping the database schema.
  • Application Role: Handles all the application's CRUD operations but restricted from tinkering with the schema.

By isolating responsibilities, we gain several advantages:

  • Security: Compromised or rotated credentials impact only a specific role, minimizing potential damage and work for us.
  • Permission Efficiency: The application role is only endowed with the permissions it genuinely requires, making our system more secure.
  • Administrative Ease: Keeping admin credentials under wraps simplifies managerial tasks, as we can be fairly certain that our admin role remains uncompromised.

Running SQL Code

To create new Postgres roles, you'll need to run SQL code. Choose one of the following options:

Online Console

In the Neon Console, select your project.

In the left sidebar, there is a SQL Editor tab. If you click on that tab, you can run SQL statements directly in the online console.

Postgres Client

In the Neon Console, select your project.

In the main dashboard, there is a Connection Details section. You can use these details to connect to your database using your favorite Postgres client like psql in terminal.

Passwords

Before we create these new roles, we need to be able to generate secure passwords for them. Here are some ways you can generate a secure password:

openssl rand -base64 16 | tr '+/' '-_' | tr -d '='
openssl rand -base64 16 | tr '+/' '-_' | tr -d '='
openssl rand -base64 16 | tr '+/' '-_' | tr -d '='
openssl rand -base64 16 | tr '+/' '-_' | tr -d '='

Or just click this button to generate a password:

This button also changes the passwords in the following SQL code so you can just copy and paste the code blocks.

Migration Role

step 1:

Run this code to generate a new role for the migrations:

-- Create a new role for migrations
CREATE ROLE migration_role WITH LOGIN PASSWORD 'your_password_here';

-- Grant permissions on the "neondb" database
GRANT ALL PRIVILEGES ON DATABASE "neondb" TO migration_role;

-- Grant usage on the "public" schema
GRANT USAGE, CREATE ON SCHEMA public TO migration_role;

-- Become a member of migration_role
GRANT migration_role TO current_user;
-- Create a new role for migrations
CREATE ROLE migration_role WITH LOGIN PASSWORD 'your_password_here';

-- Grant permissions on the "neondb" database
GRANT ALL PRIVILEGES ON DATABASE "neondb" TO migration_role;

-- Grant usage on the "public" schema
GRANT USAGE, CREATE ON SCHEMA public TO migration_role;

-- Become a member of migration_role
GRANT migration_role TO current_user;
-- Create a new role for migrations
CREATE ROLE migration_role WITH LOGIN PASSWORD 'your_password_here';

-- Grant permissions on the "neondb" database
GRANT ALL PRIVILEGES ON DATABASE "neondb" TO migration_role;

-- Grant usage on the "public" schema
GRANT USAGE, CREATE ON SCHEMA public TO migration_role;

-- Become a member of migration_role
GRANT migration_role TO current_user;
-- Create a new role for migrations
CREATE ROLE migration_role WITH LOGIN PASSWORD 'your_password_here';

-- Grant permissions on the "neondb" database
GRANT ALL PRIVILEGES ON DATABASE "neondb" TO migration_role;

-- Grant usage on the "public" schema
GRANT USAGE, CREATE ON SCHEMA public TO migration_role;

-- Become a member of migration_role
GRANT migration_role TO current_user;

Your migration role can connect to the database using the following connection string as long as you replace <PGHOST> with the value from the Connection Details section of the Neon Console:

postgres://migration_role:your_password_here@<PGHOST>/neondb
postgres://migration_role:your_password_here@<PGHOST>/neondb
postgres://migration_role:your_password_here@<PGHOST>/neondb
postgres://migration_role:your_password_here@<PGHOST>/neondb

<PGHost> should be something like ep-rough-sunset-123456.us-west-2.aws.neon.tech

step 2:

Add this to your .env.local file as MIGRATION_DATABASE_URL.

MIGRATION_DATABASE_URL=postgres://migration_role:your_password_here@<PGHOST>/neondb
MIGRATION_DATABASE_URL=postgres://migration_role:your_password_here@<PGHOST>/neondb
MIGRATION_DATABASE_URL=postgres://migration_role:your_password_here@<PGHOST>/neondb
MIGRATION_DATABASE_URL=postgres://migration_role:your_password_here@<PGHOST>/neondb

Application Role

step 3:

Run this code to generate a new role for the application CRUD operations:

-- Create a new role for the application
CREATE ROLE app_role WITH LOGIN PASSWORD 'your_password_here';

-- Grant permissions on the "neondb" database
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_role;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_role;

-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES FOR ROLE migration_role IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_role;

-- Set default privileges for future sequences
ALTER DEFAULT PRIVILEGES FOR ROLE migration_role IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_role;
-- Create a new role for the application
CREATE ROLE app_role WITH LOGIN PASSWORD 'your_password_here';

-- Grant permissions on the "neondb" database
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_role;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_role;

-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES FOR ROLE migration_role IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_role;

-- Set default privileges for future sequences
ALTER DEFAULT PRIVILEGES FOR ROLE migration_role IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_role;
-- Create a new role for the application
CREATE ROLE app_role WITH LOGIN PASSWORD 'your_password_here';

-- Grant permissions on the "neondb" database
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_role;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_role;

-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES FOR ROLE migration_role IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_role;

-- Set default privileges for future sequences
ALTER DEFAULT PRIVILEGES FOR ROLE migration_role IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_role;
-- Create a new role for the application
CREATE ROLE app_role WITH LOGIN PASSWORD 'your_password_here';

-- Grant permissions on the "neondb" database
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_role;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_role;

-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES FOR ROLE migration_role IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_role;

-- Set default privileges for future sequences
ALTER DEFAULT PRIVILEGES FOR ROLE migration_role IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_role;

Your application role can connect to the database using the following connection string as long as you replace <PGHOST> with the value from the Connection Details section of the Neon Console:

postgres://app_role:your_password_here@<PGHOST>/neondb
postgres://app_role:your_password_here@<PGHOST>/neondb
postgres://app_role:your_password_here@<PGHOST>/neondb
postgres://app_role:your_password_here@<PGHOST>/neondb
step 4:

Add this to your .env.local file as DATABASE_URL.

DATABASE_URL=postgres://app_role:your_password_here@<PGHOST>/neondb
DATABASE_URL=postgres://app_role:your_password_here@<PGHOST>/neondb
DATABASE_URL=postgres://app_role:your_password_here@<PGHOST>/neondb
DATABASE_URL=postgres://app_role:your_password_here@<PGHOST>/neondb

URLs

step 4:

Add ?sslmode=verify-full to the end of the MIGRATION_DATABASE_URL connection string.

MIGRATION_DATABASE_URL=postgres://migration_role:your_password_here@<PGHOST>/neondb?sslmode=verify-full
DATABASE_URL=postgres://app_role:your_password_here@<PGHOST>/neondb
MIGRATION_DATABASE_URL=postgres://migration_role:your_password_here@<PGHOST>/neondb?sslmode=verify-full
DATABASE_URL=postgres://app_role:your_password_here@<PGHOST>/neondb
MIGRATION_DATABASE_URL=postgres://migration_role:your_password_here@<PGHOST>/neondb?sslmode=verify-full
DATABASE_URL=postgres://app_role:your_password_here@<PGHOST>/neondb
MIGRATION_DATABASE_URL=postgres://migration_role:your_password_here@<PGHOST>/neondb?sslmode=verify-full
DATABASE_URL=postgres://app_role:your_password_here@<PGHOST>/neondb

Neon requires SSL connections to it's database, so we need to add ?sslmode=verify-full to the end of the connection string, but why only the migration URL?

Our next.js application is going to Neon Serverless Driver to connect to the database. This driver does not encrypt the postgres connection, it tunnels unencrypted Postgres connections over secure websockets. So the DATABASE_URL doesn't need to specify SSL, because the connection itself doesn't use SSL, the connection is tunneled over Secure Websockets.

When we're performing migrations, we might connect to the database from a standard Postgres driver like posgres.js, in that case, we need the ssl flag to be set to verify-full so that the connection is encrypted.

Creating a new Migration Role

If you've already performed a migration and need to create a new role to perform migrations, first delete the migration_role, then create a new one using the same [migration code][#migration-role] code as above. Then grant permission to the drizzle schema and transfer ownership of all tables to the new role.

GRANT USAGE, CREATE ON SCHEMA public TO migration_role2;

DO $$ 
DECLARE 
   table_name text;
BEGIN 
   FOR table_name IN (SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public') 
   LOOP
      EXECUTE 'ALTER TABLE ' || table_name || ' OWNER TO migration_user2;';
   END LOOP;
END $$;

-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES FOR ROLE migration_role2 IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_role;

-- Set default privileges for future sequences
ALTER DEFAULT PRIVILEGES FOR ROLE migration_role2 IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_role;
GRANT USAGE, CREATE ON SCHEMA public TO migration_role2;

DO $$ 
DECLARE 
   table_name text;
BEGIN 
   FOR table_name IN (SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public') 
   LOOP
      EXECUTE 'ALTER TABLE ' || table_name || ' OWNER TO migration_user2;';
   END LOOP;
END $$;

-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES FOR ROLE migration_role2 IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_role;

-- Set default privileges for future sequences
ALTER DEFAULT PRIVILEGES FOR ROLE migration_role2 IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_role;
GRANT USAGE, CREATE ON SCHEMA public TO migration_role2;

DO $$ 
DECLARE 
   table_name text;
BEGIN 
   FOR table_name IN (SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public') 
   LOOP
      EXECUTE 'ALTER TABLE ' || table_name || ' OWNER TO migration_user2;';
   END LOOP;
END $$;

-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES FOR ROLE migration_role2 IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_role;

-- Set default privileges for future sequences
ALTER DEFAULT PRIVILEGES FOR ROLE migration_role2 IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_role;
GRANT USAGE, CREATE ON SCHEMA public TO migration_role2;

DO $$ 
DECLARE 
   table_name text;
BEGIN 
   FOR table_name IN (SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public') 
   LOOP
      EXECUTE 'ALTER TABLE ' || table_name || ' OWNER TO migration_user2;';
   END LOOP;
END $$;

-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES FOR ROLE migration_role2 IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_role;

-- Set default privileges for future sequences
ALTER DEFAULT PRIVILEGES FOR ROLE migration_role2 IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_role;