Complete PostgreSQL Setup: Create Databases, Fix Errors & Manage Users Like a Pro

Setting up a PostgreSQL database correctly is essential for application development. In this guide, we will walk you through the process of creating an application database and user, registering the database in pgAdmin, setting up tables with relevant data, resolving permission issues, and troubleshooting common errors.

By following this step-by-step tutorial, you will have a fully functional PostgreSQL setup ready for development.

Step 1: Creating an Application Database and User in PostgreSQL

Before proceeding, ensure you have PostgreSQL installed on your Windows or Mac system. We will use pgAdmin, which comes bundled with PostgreSQL, to create the database and user.

1. Launch pgAdmin

• Open pgAdmin and enter the master password when prompted.
• Expand the Servers section and locate PostgreSQL 17.

2. Create a New Database and User

• Right-click on the Server where you want to create the database.
• You will see the Create option in the dropdown menu.
• Click on Create → Database.
• In the Create — Database window:
• Database Name: Enter "itversity_retail_db".
• Owner: Select the database owner (default is postgres).
• Click the Save button to create the database.

• Open the Query Tool in PostgreSQL 17.

• Run the following SQL commands:

CREATE USER itversity_retail_user WITH ENCRYPTED PASSWORD 'itversity';

GRANT ALL PRIVILEGES ON SCHEMA public TO itversity_retail_user;

💡 Note: Do not run both commands together. First, run the first command and make sure it succeeds. Then, run the second command. If both are executed at the same time, only the first command will run, and the second command might not execute.

• Exit the PostgreSQL 17 server after execution.

Step 2: Register a New Server in pgAdmin

To manage the new database via pgAdmin, follow these steps:

1. Register a New Server

• Click Register → Server.

• Enter a name: Postgres 17 - Localhost - itversity_retail_user.

• Under the Connection tab, enter:
• Hostname: localhost
• Port: 5432 (default PostgreSQL port)
• Database Name: itversity_retail_db
• Username: itversity_retail_user
• Password: itversity
• Click Save to register and connect.

Now, you will see the itversity_retail_db database under the newly created server Postgres 17 - Localhost - itversity_retail_user.

Step 3: Setting Up Application Tables and Data

Now, let’s create tables and load data.

1. Open Query Tool in pgAdmin in the “Postgres 17 - Localhost - itversity_retail_user" Server to Run the Create Script”

• In pgAdmin, open Query Tool within itversity_retail_db in Postgres 17 - Localhost - itversity_retail_user server.
• Upload the create_tables_pgsql.sql file and execute it.

• Verify tables exist by expanding Schemas → Public → Tables.

2. Load Data into Tables

• Open Query Tool again.
• Upload the load_tables_pgsql.sql file and execute the queries.

• Run the following command to verify data in tables:

SELECT * FROM TableName;

If records are present, the setup is successful!

Common Issues Faced by Students and Their Solutions

Issue 1: Unable to Connect to Server — Password Authentication Failed

Error Message:

FATAL: password authentication failed for user 'itversity_retail_user'

Possible Causes:

1. The password entered for the user itversity_retail_user does not match the one stored in PostgreSQL.
2. The user itversity_retail_user may not exist or was created with a different password.

Solution:

To resolve this issue, recreate or update the user by running the following command:

CREATE USER itversity_retail_user WITH ENCRYPTED PASSWORD 'itversity';

After setting this password, ensure you use itversity as the password when connecting.

Additionally, double-check the password entered during the login process. PostgreSQL is case-sensitive, and any mismatch will result in authentication failure.

Issue 2: Permission Denied for Schema Public

Error Message:

ERROR: Permission denied for schema public
Line 8: CREATE TABLE departments()
SQL state: 42501

Solution:

Steps to Resolve the Permission Denied Error

1. Switch to the Superuser Session

To resolve this, log in as the superuser (PostgreSQL 17) and execute the following commands in itversity_retail_db:

Grant schema-level privileges to the user:

GRANT ALL PRIVILEGES ON SCHEMA public TO itversity_retail_user;

(Optional but recommended) If the table already exists, grant specific privileges to the user for that table:

GRANT ALL PRIVILEGES ON TABLE departments TO itversity_retail_user;

2. Verify the Permissions

After granting the required privileges, return to the server (Postgres 17 — Localhost — itversity_retail_user) and try creating the table again.

By following these steps, you can resolve the “permission denied for schema public” error and ensure your user has the proper access to create tables in the database.

Conclusion

Setting up a PostgreSQL database for an application involves creating a database and user, registering it in pgAdmin, resolving permission issues, and loading tables with data. By following this structured approach, you now have a fully functional PostgreSQL environment for development.

Engage With Us!

👉 Follow

for more insightful articles.

🔁 Share this article with your peers and help them master data import techniques.

💬 We welcome your feedback and questions — let’s make this a collaborative learning experience!

✨ Stay informed. Stay ahead.✨