Image for post
Image for post
Databases

Installation

  • macOS: There are 3 main ways of installing Postgres on macOS — Installation via interactive EDB, installation using postgres.app made specifically for macOS, or installation using homebrew. More details can be found on this link
  • Windows: Postgres can be installed on Windows using the EDB installer. Follow this link to get more information.
  • Linux: Postgres is available as a package that can be installed using the package management systems provided by Linux. This link provides basic information for the installation procedure.

Running the system

  1. The Postgres usually keeps all its data in the path /usr/local/var/postgres. This path must be initiated so that the DB would start storing the databases and relations in this path.
initdb /usr/local/var/postgres -E utf8

2. Run Postgresql on MacOS

brew services start postgres

3. During the `initdb` phase, a database with the name `<user>` should be created. If this is not created, a manual creation would be necessary.

createdb <user>

4. The psql acts like a command-line client to issue commands to your database. This can be started using the command.

psql

Create a new user and grant permissions

  • Create a new user by issuing the following command:
CREATE USER <username>
  • Set a password to this user by the following command:
ALTER USER <username> with encrypted password '<password>';
  • Provide permissions to this user using the command:
ALTER USER <username> with CREATEDB;

The other permissions that can be added to the user include the following:

  1. LOGIN / NOLOGIN: Allow (or not) to login to PostgreSQL
  2. SUPERUSER/NOSUPERUSER — Provide (or not) superuser permissions on the user. A database superuser will bypass other permission checks, except for LOGIN.
  3. CREATEROLE — Allow (or not) the user to create various roles with different permissions on the DB
  4. CREATEDB/NOCREATEDB — Allow (or not) the user to create new Databases.
  5. REPLICATION — Grant (or not) replication permissions
  6. CREATEUSER / NOCREATEUSER: Allow (or not) the ability to create new users.

Create role and alter role permissions

  • Create a new role:
CREATE ROLE <groupname> WITH <role1>, <role2>;
  • The GRANTcommand can be used to provide a role to the user.
GRANT <groupname> TO <username>
  • The REVOKE command can be used to remove a role from the user.
REVOKE <groupname> TO <username>

Play with PSQL

  • List all your users in the system use
\du
Image for post
Image for post
  • List all the databases using the command
\l
Image for post
Image for post
  • To select a database and list the relations in the database
\c <database name>\dt
Image for post
Image for post
  • When a database owner is a different user, the psql command-line interface should be launched with different user permissions. This can be done using
psql -h localhost -p 5432 -U postgres
Image for post
Image for post
  • To understand the schema of any given relation within a database
\d "<relation name>"
Image for post
Image for post

And finally, now that you know the basics of working with Postgres, it is time to create new databases and explore various functionalities on your own. Just write your queries in a .sql file and execute it using the command:

psql -f <filepath> -q <dbname> <username>

That’s all for today, see you in the next article.

Written by

CODER | BLOGGER | ARTIST | GHOST

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store