DEV Community

karthikeyan
karthikeyan

Posted on

Install and Configure PostgreSQL With GoLang

  1. Install PostgreSQL: If you haven't already installed PostgreSQL, you can do so using the following command:


   sudo apt-get update
   sudo apt-get install git
   sudo apt-get install golang-go
   sudo apt-get install postgresql postgresql-contrib


Enter fullscreen mode Exit fullscreen mode
  1. Start PostgreSQL: Start the PostgreSQL service:


   sudo service postgresql start


Enter fullscreen mode Exit fullscreen mode
  1. Create a PostgreSQL User and Database: You'll need to create a PostgreSQL user and a database for your Go application. You can do this using the psql command-line utility:


   sudo -u postgres psql


Enter fullscreen mode Exit fullscreen mode

Then, within the psql shell, create a new user and a database:



   CREATE USER myuser WITH PASSWORD 'mypassword';
   CREATE DATABASE mydb;
   GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;


Enter fullscreen mode Exit fullscreen mode

Replace myuser and mypassword with your desired username and password, and mydb with your desired database name.

Locate the pg_hba.conf file. It's usually found in the PostgreSQL data directory, often in /etc/postgresql/{version}/main/pg_hba.conf.

Changing the pg_hba.conf line:



local   all             all                                     peer


Enter fullscreen mode Exit fullscreen mode

to



local   all             all                                     all
local   testing         <Data_base>                                 md5


Enter fullscreen mode Exit fullscreen mode


sudo service postgresql restart


Enter fullscreen mode Exit fullscreen mode
  1. ** Switch to the PostgreSQL User**

You can switch to the PostgreSQL user and then use psql. The PostgreSQL user is typically named "postgres." Run the following command:



sudo -u postgres psql -d mydb


Enter fullscreen mode Exit fullscreen mode

This command switches to the "postgres" system user and then runs psql to connect to the specified database. You will be prompted for the password of the "postgres" PostgreSQL user.

  1. You can execute the SQL code to create the "list" table:


   CREATE TABLE list (
       id INT PRIMARY KEY,
       name VARCHAR (255)
   );
   INSERT INTO list VALUES (1, 'Sam');
   INSERT INTO list VALUES (2, 'Som');
   INSERT INTO list VALUES (3, 'Ram');
   INSERT INTO list VALUES (4, 'Bam');


Enter fullscreen mode Exit fullscreen mode

This SQL code defines a table named "list" with two columns, "id" and "name." The "id" column is set as the primary key, and the "name" column is of type VARCHAR with a maximum length of 255 characters.

  1. Exit the psql session by typing:


   \q


Enter fullscreen mode Exit fullscreen mode

This will return you to your regular command prompt.

  1. Create a Go Module:

If you are starting a new project, you can create a Go module by navigating to your project directory and running:



   go mod init myproject


Enter fullscreen mode Exit fullscreen mode

If you're working on an existing project, and it doesn't have a Go module yet, you can also initialize one in the project's root directory.

  1. Install the PostgreSQL Driver for Go: You'll need a Go driver to connect to PostgreSQL. One popular option is pq. You can install it using go get:


   go get github.com/lib/pq


Enter fullscreen mode Exit fullscreen mode
  1. Write a Go Program (main.go): Here's a simple example of a Go program that connects to the PostgreSQL database and performs a basic query:


   package main

   import (
       "database/sql"
       "fmt"
       _ "github.com/lib/pq"
   )

   func main() {
       // Connect to the PostgreSQL database
       connStr := "user=myuser dbname=mydb password=mypassword sslmode=disable"
       db, err := sql.Open("postgres", connStr)
       if err != nil {
           panic(err)
       }
       defer db.Close()

       // Perform a sample query
       rows, err := db.Query("SELECT * FROM list")
       if err != nil {
           panic(err)
       }
       defer rows.Close()

       // Iterate through the results
       for rows.Next() {
           var id int
           var name string
           if err := rows.Scan(&id, &name); err != nil {
               panic(err)
           }
           fmt.Printf("ID: %d, Name: %s\n", id, name)
       }
   }


Enter fullscreen mode Exit fullscreen mode

Replace myuser, mydb, and the query with your own credentials and SQL statement.

  1. Run the Go Program: You can compile and run your Go program like this:


   go build -o main.go
   ./main


Enter fullscreen mode Exit fullscreen mode
  1. Fixing The permission denied for table list problem Screenshot from 2023-09-19 18-47-27 Here are the steps to grant the necessary permissions:

12.1. Connect to PostgreSQL: Connect to the PostgreSQL database with a superuser or a user who has the necessary privileges to grant permissions. You can use the psql command as you did before:



   sudo -u postgres psql -d mydb


Enter fullscreen mode Exit fullscreen mode

Replace "mydb" with the name of your database.

12.2. Grant SELECT Permission: In the psql shell, grant the SELECT permission on the "list" table to your PostgreSQL user. Replace <username> with the actual username your Go application is using:



   GRANT SELECT ON TABLE list TO myuser;


Enter fullscreen mode Exit fullscreen mode

This grants the SELECT permission on the "list" table to the specified user, allowing them to retrieve data from the table.

12.3. Exit psql: After granting the permissions, you can exit the psql shell:



   \q


Enter fullscreen mode Exit fullscreen mode

12.4. Rebuild and Run Your Go Application: After granting the necessary permissions, rebuild and run your Go application:



   go build -o main && ./main


Enter fullscreen mode Exit fullscreen mode

Your application should now be able to access the "list" table without encountering the "permission denied" error.

Video Tutorial: https://www.youtube.com/watch?v=BTeSJ4VGU1o&t=350s&ab_channel=KKBUGHUNTER
GitHub: https://github.com/KKBUGHUNTER/GO_Postgresql_CRUD_Web_App/tree/main

Thank you...

Top comments (0)