Now, we should know how to create a new user and database. For this, we are going to use Ubuntu/Mac as the general example. We do this in a shell called the psql shell. We can see all available commands in psql using the ?  command. In order to enter into psql, first change to the postgres user. On Ubuntu, you can do that using the following command:

sudo su postgres

Now, it turns us into a user called postgres. Then, launch the psql shell using the psql command. If you type ? in there, you see the output of all available commands:

 

To list all available users and their privileges, you will find a command in the Informational section of shell help, that is:

du - List roles

A role is an access permission given to a user. The default role in that list is postgres:

postgres=# du
                                   
                 List of roles 
 Role name |      Attributes               | Member of 
-----------+------------------------------------------------------------+-----------
 postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

The preceding command lists roles (users) with their attributes (what a role is allowed to do) and other options. For adding a new user, we just type this psql command:

CREATE ROLE naren with LOGIN PASSWORD 'passme123';

This creates a new user with the name naren and the password passme123. Now, give the permission to the user to create databases and further roles, using the following command:

ALTER USER naren CREATEDB, CREATEROLE;

In order to delete a user, just use the DROP command in the same context:

DROP ROLE naren;

Now we know how to create a role. Let us see a few more CRUD commands, which are really SQL commands that we see in other relational databases too. Take a look at the following table:

CREATE DATABASE mydb;

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric
);

INSERT INTO products VALUES (1, 'Rice', 5.99);

UPDATE products SET price = 10 WHERE price = 5.99;

DELETE FROM products WHERE price = 5.99;

Now, let us see from Go how we can talk to PostgreSQL and try to do the preceding operations using a simple example.