Creating a database and user for a single application is a common task for developers and database administrators in small and medium environments. In this tutorial, we will walk through the steps to create a new database and user on a Postgres server using the command line.

Here are the steps you can follow to create a database and user on Postgres:

  1. Connect to the Postgres server by typing the following command:
# Connect to your PostgreSQL server using the `psql` CLI
psql -h <hostname> -p <port> -U <username>
Replace `hostname` with the name of the server where the Postgres service is running, the `port` if you have a custom one, and `username` with the name of a user who has permission to create databases and users.
  1. Enter the password for the user when prompted.
  2. Once connected, you can create a new database by typing the following command:
CREATE DATABASE databasename;
Replace `databasename` with the desired name for the new database.
  1. To create a new user, type the following command:
CREATE USER username WITH PASSWORD 'password';
Replace `username` with the desired name for the new user, and `password` with the desired password for the user.
  1. To grant the new user full privileges on the new database, type the following command:
GRANT ALL PRIVILEGES ON DATABASE databasename TO username;
Replace `databasename` with the name of the database, and `username` with the name of the user.
  1. Exit the psql prompt by typing \q.

You should now have a new database and user on the Postgres server, and the user should have full privileges on the database.

If you want to have the full SQL in a single block, here’s an example that creates the joplin the database then creates a user with the same name with full access to that database:

CREATE DATABASE joplin;
CREATE USER joplin WITH PASSWORD 'myPasswordHere';
GRANT ALL PRIVILEGES ON DATABASE joplin TO joplin;

Reference: