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>
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;
databasename with the desired name for the new database.
- To create a new user, type the following command:
CREATE USER username WITH PASSWORD 'password';
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;
databasename with the name of the database, and
username with the name of the user.
- Exit the
psqlprompt by typing
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;
- PostgreSQL docs