DEV Community

Cover image for Create database and user with psql script for Postgres
Kyle Foo
Kyle Foo

Posted on • Edited on

6

Create database and user with psql script for Postgres

Postgres is always a popular choice for many applications. It offers many features, hence it was used for my React on Rails App. When it comes to upgrading, I prefer to re-create my db on new server, then depends on rake task db:setup_or_migrate to perform db setup later. Data migration is not the scope of this post.

After installing/upgrading postgres via Postgres.app you can launch psql with the command on terminal:

psql
Enter fullscreen mode Exit fullscreen mode

screenshot

Once you are in the psql console,

To Create User/Role:

CREATE ROLE kyle WITH LOGIN;
Enter fullscreen mode Exit fullscreen mode

OR

CREATE USER kyle;
Enter fullscreen mode Exit fullscreen mode

the difference is that CREATE USER automatically gives the role login privilege.

Then, enter \du command to print the list of roles to check your newly created role - kyle.

Once created, to set up password:

\password kyle
Enter fullscreen mode Exit fullscreen mode

you will be prompted to insert password

To grant permission for creating database:

ALTER USER kyle CREATEDB;
Enter fullscreen mode Exit fullscreen mode

Also, enter \du to check your newly assigned role attributes (permission).

To grant permission to user as superuser:

ALTER USER kyle SUPERUSER;
Enter fullscreen mode Exit fullscreen mode

To create database:

CREATE DATABASE kylefoo;
Enter fullscreen mode Exit fullscreen mode

Then, enter \l command to print the list of database to check your newly created database.
db list

To give ownership of database to user:

ALTER DATABASE kylefoo OWNER TO kyle;
Enter fullscreen mode Exit fullscreen mode

You are done with creating database kylefoo with owner kyle assigned as owner. You can use the credentials in the database.yml on your rails application. Cheers!

Next, to cover usage of pg_dump and restore for data migration.

Postgres on Neon - Get the Free Plan

No credit card required. The database you love, on a serverless platform designed to help you build faster.

Get Postgres on Neon

Top comments (0)

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free