Understanding PostgreSQL: Setting Up Roles, Permissions, and Databases

So I decided to go away from NoSQL databases and write raw SQL to strengthen my grip on backend technologies.

Setting up Postgres was a bit daunting and frustrating but after a bit of research I am reminded that everything looks difficult in the beginning until given enough time.

So let’s dive in.

Step 1. - Install Postgres (For mac users)

 brew install postgresql

after installation we will need to start up the postgres service by running.

brew services start postgresql

If you want to later stop the postgres service you can run the command below

brew services stop postgresql

Note: If your service is not connected you can’t connect to postgres locally.

Step 2: Postgres Command interface (psql)

Like most NoSQL databases Postgres has it’s own shell where you can run SQL commands.

Running psql --help provides additional details about the available options for connecting to psql:

• -h or --host=HOSTNAME: Specifies the database server host or socket directory. By default, it uses the local socket.

• -p or --port=PORT: Defines the database server port. The default is 5432.

• -U or --username=USERNAME: Indicates the database username. The default is your current system username.

• -w or --no-password: Disables the password prompt.

• -W or --password: Forces a password prompt, which typically occurs automatically.

When you first installed postgres with homebrew it came with a super user, which has a lot of rights and priviledges which we may not want to gamble upon. So a best practice methodology is to always create a database user with which we can access our application with.

Lets go into the postgres terminal by running

psql postgres

You will notice that we have now entered into a new connection. We’re now inside psql in the postgres database. The prompt ends with a # to denote that we’re logged in as the superuser, or root:

postgres=#

Let’s check the base user or current user details (connection info)

postgres=# \conninfo

## Note postgres=# means we are in the psql terminal the actual command
## to paste is \conninfo

Running the above command on my psql terminal shows

Let me also run the \du command

Note: All psql commands start with a \

• \q: Exit psql connection

• \c: Connect to a new database

• \dt: List all tables

• \du: List all roles

• \list: List databases

Creating a postgres DB user

Postgres works with users so I can create a user, give them certain access and privileges.

So I want to create a user called shop_admin with a password of password123

CREATE ROLE shop_admin WITH LOGIN PASSWORD 'password123';

Now I have created a database user/role called shop_admin, I want shop_admin to be able to do something in postgres which is mainly creation of a db.

For me to be able to do that I need to tell postgres, “hey give shop_admin the ability to create a database”

ALTER ROLE shop_admin CREATEDB;

Let’s do the /du command again to see what shop_admin can do now.

Sow we can now say that our user/role shop_admin has the ability to Create DB.

psql postgres gives us some sort of super powers and gets us in as a super admin, now we’ve been able to achieve what we want which is creating of a db we need to leave the psql terminal and link the shop_admin with postgres.

We leave the psql terminal by typing \q

postgres=# \q

So now we’ve left the psql terminal and back in our PCs terminal let’s tell postgres to connect our shop_admin to postgres.

psql -d postgres -U shop_admin

Immediately we run the above you’ll notice that our terminal prefix changes from what it previously used to be postgres=# to postgres=> which means we are no longer logged in as a super user but a user under the shop_admin privileges.

Now we can create our database, I am naming mine shop_db because I am building a simple e-commerce application.

CREATE DATABASE shop_db;

Let’s check to see if this db was created by running the list command. \list

Below we can see the shop_db and the owner is the shop_admin.

Finally we need to connect the shop_admin to the shop_db by doing.

\c shop_db

The terminal now shows shop_db → as it’s suffix and shows we are connected.

Now that we have created a role/user and a db to get into the shop_db next time we just simply type the command below in our pcs terminal to get into the shop_db shell.

psql -U shop_admin -d shop_db

You can see I now have access to my shop_db once again.
From here I can now create tables and write my SQL commands.

I hope you have been able to learn something, you don’t need to cram everything just reference this article and you’ll have the job done.

Thank you for reading my article.