Databases

Installation

Postgres is an open-source RDBMS that can be hosted independently on a machine, or hosted via a database service using EDB. PostgreSQL can be installed in different ways depending on the operating system that is being used.

  • 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

This section will discuss how to run PostgreSQL on Mac, and how to initiate the DB via the command line.

initdb /usr/local/var/postgres -E utf8
brew services start postgres
createdb <user>
psql

Create a new user and grant permissions

By default, all the databases under Postgres will be created under the main user. This main user contains superuser permissions. Normally you would want to create a database and give permissions to access DB at various levels.

CREATE USER <username>
ALTER USER <username> with encrypted password '<password>';
ALTER USER <username> with CREATEDB;
  1. SUPERUSER/NOSUPERUSER — Provide (or not) superuser permissions on the user. A database superuser will bypass other permission checks, except for LOGIN.
  2. CREATEROLE — Allow (or not) the user to create various roles with different permissions on the DB
  3. CREATEDB/NOCREATEDB — Allow (or not) the user to create new Databases.
  4. REPLICATION — Grant (or not) replication permissions
  5. CREATEUSER / NOCREATEUSER: Allow (or not) the ability to create new users.

Create role and alter role permissions

Sometimes we require a group of permissions to be given to several users. Instead of adding individual permissions to each user, a role can be created. Any new user that is created can be assigned a particular role.

CREATE ROLE <groupname> WITH <role1>, <role2>;
GRANT <groupname> TO <username>
REVOKE <groupname> TO <username>

Play with PSQL

Command-line tools are especially helpful when you are trying to visualize your database. You’d want to check what databases are present, what type of relations are present in your database. Note that all these commands should be used in PSQL

\du
\l
\c <database name>\dt
psql -h localhost -p 5432 -U postgres
\d "<relation name>"
psql -f <filepath> -q <dbname> <username>

CODER | BLOGGER | ARTIST | GHOST