In this guide, we are going to see how to manage users and database permissions in postgres, i.e. Creating postgres database, creating

postgres user, granting user access to postgres databases, updating user permissions and deleting user and database when no longer needed.

PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance.

Prerequisites

Postgres server installed in a server.
Access to the database server access

Table of Content

Connecting to the Postgres server
Creating a database and a user
Creating a superuser in postgres
Basic Postgres commands

1. Connecting to the postgres server

What we need to do is connect to the db with a user with privileges that can run the postgres commands to create the database, user and grant privileges to that user. For the default installation, an OS postgres user will be created with access to the postgres database and you can login as that user by running any of these commands:

sudo -u postgres psql

If you do not have access to the server, you can login using pgsql command. This is the format:

psql 'postgres://<username>:<password>@<host>:<port>/<db>?sslmode=disable'

Example:

psql 'postgres://root:passwordhere@192.160.1.20:5432/postgres?sslmode=disable'

2. Creating a Database and user

To create a database

create database dbname;

To create a user with password:

create user dbuser with encrypted password 'dbpassword';

To grant the user full access to the database:

grant all privileges on database dbname to dbuser;

Postgres also comes with the utilities createuser and createdb that can be used to achieve the same function.

Create db and user:

sudo -u postgres createuser <username>
sudo -u postgres createdb <dbname>

To assign the user a password and grant access to the database, you will need to login to postgres:

$ sudo -u postgres psql
postgres=# alter user <username> with encrypted password '<password>';
psql=# grant all privileges on database <dbname> to <username> ;

This is the format for updating the user password

alter user <username> with encrypted password '<password>';

3. Creating a superuser

Create the user with the create user command then assign user the

CREATEDB, CREATEROLE and SUPERUSER commands.

create user root with encrypted password 'rootpassword';
ALTER USER root CREATEDB;
ALTER USER root CREATEROLE;
ALTER USER root SUPERUSER;

4. Basic Postgres commands

Connecting to the database remotely:

# Usage
psql --set=sslmode=disable -h <host> --port <port> -d <dbname> -U <dbuser>
# example connecting as kip citizix to host 10.2.11.2 port 5432 database citizix
psql --set=sslmode=disable -h 10.2.11.2 --port 5432 -d citizix -U kip

# This will also work
psql "sslmode=disable host=<host> dbname=<dbname> port=<port> user=playpen_dev_user password=<password>"
# example connecting as kip citizix to host 10.2.11.2 port 5432 database citizix and password secret
psql "sslmode=disable host=10.2.11.2 dbname=citizix port=5432 user=kip password=secret"

Basic commands:

-- Switch to database dbname
\c dbname;

-- list databases
\l

-- list users
\du

-- list tables
\dt

-- Describe tablename
\d+ tablename