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:
- 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.
- Enter the password for the user when prompted.
- 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.
- 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.
- 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.
- 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