How to create a database and user for your new app on PostgreSQL

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:

• ChatGPT
• PostgreSQL docs
  https://www.postgresql.org/docs/current/app-psql.html