Adding users and databases in PostgreSQL

Now, we can create a new user and database. For this, we are going to use Ubuntu/MacOS X as a 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 the psql shell, first, change to the postgres user. On Ubuntu, for universal installation, you can enter the psql shell using the following command:

sudo su postgres

Now, it turns us into a user called postgres. Then, launch the psql shell using the following command:

psql

In the case of PostgreSQL running in a Docker container, use this command to launch the psql shell directly:

docker exec -i -t postgres-local-1 psql -U postgres

Once you are in the psql shell, type the \? help command in there, and you will see the output of all available commands, as shown in the following screenshot:

 

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

\du - List roles

A role is the access permission given to a user. With the \du command, you can see that the default user is postgres, with the following roles attached:

postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

We need a new user to work with PostgreSQL. To add a new user, just type this SQL command in the psql shell:

CREATE ROLE git-user with LOGIN PASSWORD 'YOUR_PASSWORD'; # Caution: Choose strong password here

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

ALTER USER gituser CREATEDB, CREATEROLE;

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

DROP ROLE git-user;

Don't try to change the password for the default postgres user. It is intended to be a superuser account, and should not be kept as a normal user. Instead, create a new role and give the required permissions for it. Use strong passwords.

If you don't use a command-line interface (CLI), you can install a graphical user interface (GUI) client such as pgAdmin 4 for accessing the database.

You can find more details about installing pgAdmin 4 as a Docker application here: https://hub.docker.com/r/dpage/pgadmin4/.

Now we know how to create a role, let's see a few more create, read, update, and delete (CRUD) SQL commands that are common in most relational databases. Take a look at the following table:

Action SQL command
Create a database 
CREATE DATABASE mydb;
Create a table 
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric
);
Insert into table 
INSERT INTO products VALUES (1, 'Rice', 5.99);
Update table 
UPDATE products SET price = 10 WHERE price = 5.99;
Delete from table 
DELETE FROM products WHERE price = 5.99;

 

These basic commands can be supported by many advanced SQL operators, such as LIMIT, ORDER BY, and GROUP BY. SQL has many other concepts, such as joining relations over tables.

You can find more details about SQL queries that are supported by PostgreSQL here: https://www.postgresql.org/docs/10/sql.html.

In the next section, we'll see how Go programs communicate with the PostgreSQL server. We'll try to leverage a database driver called pq. Using that driver package, we'll see an example of how to insert a web URL into PostgreSQL.