100 Days of DevOps: Day 17

This content originally appeared on DEV Community and was authored by Wycliffe A. Onyango

Navigating PostgreSQL: A Beginner’s Guide to User and Database Management

Starting out with system administration can be challenging, especially when dealing with databases like PostgreSQL. One of the most common tasks for a sysadmin is setting up user accounts and databases for new applications. This article provides a step-by-step walkthrough of how to perform these critical tasks, based on a real-world scenario.

The Task: Setting Up a PostgreSQL Environment

The objective was to prepare a PostgreSQL database for a new application. The key requirements were:

Create a database user named kodekloud_joy with a specific password.
Create a database named kodekloud_db6.
Grant full permissions on the new database to the new user.

The Problem: A Misunderstanding of Commands

Initially, the approach was to use standard Linux commands like passwd. However, this led to a “command not found” error because passwd is for managing system users, not database users. PostgreSQL has its own set of tools for these tasks.

Another common point of confusion was a warning message: could not change directory to "/home/peter": Permission denied. This is not an error but a standard security warning. The postgres user, which is a dedicated account for the database, doesn’t have permissions to access other users’ home directories. This warning can be safely ignored, as it doesn’t prevent you from interacting with the database itself.

The Solution: Using `psql` for Database Administration

The correct way to manage PostgreSQL users and databases is by using the psql command-line tool. This tool allows you to run SQL commands directly against the database server.

Step 1: Creating the Database User

To create the user, you must first switch to the postgres user, which is the default superuser for the database. The sudo -u postgres command accomplishes this, and the -c flag allows you to run a single command.

sudo -u postgres psql -c "CREATE USER kodekloud_joy WITH PASSWORD 'TmPcZjtRQx';"

This command tells PostgreSQL to create a new user role called kodekloud_joy and sets their password.

Step 2: Creating the Database

Next, the database itself needs to be created. This is done with the CREATE DATABASE SQL command.

sudo -u postgres psql -c "CREATE DATABASE kodekloud_db6;"

This command creates an empty database ready for the application to use.

Step 3: Granting Permissions

The final and most crucial step is to give the new user permission to use the database. Without this, the application would not be able to connect and write data.

sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE kodekloud_db6 TO kodekloud_joy;"

This command grants all possible permissions—including CONNECT, CREATE, TEMPORARY, etc.—on the kodekloud_db6 database to the kodekloud_joy user.

By following these three steps, the task was completed successfully without needing to restart the PostgreSQL service, demonstrating the power and efficiency of using the correct, native database tools.