Creating user, database and adding access on PostgreSQL

A nice postgres installation graphic by http://digitalocean.com. DO hosts great servers, check them out


NOTE: Right off the bat — this is valid as on Desember 2022, running on Ubuntu 22.04.0, with PostgreSQL 9.x

TL;DR; version

sudo -u postgres psql
postgres=# create database mydb;
postgres=# create user myuser with encrypted password 'mypass';
postgres=# grant all privileges on database mydb to myuser;

One nice thing about PGSQL is it comes with some utility binaries like createuser and createdb. So we will be making use of that.

As the default configuration of Postgres is, a user called postgres is made on and the user postgres has full superadmin access to entire PostgreSQL instance running on your OS.

$ sudo -u postgres psql 

The above command gets you the psql command line interface in full admin mode.

In the following commands, keep in mind the < angular brackets > are to denote variables you have to set yourself. In the actual command, omit the <>

Creating user

  $ sudo -u postgres createuser username
  

Creating Database

   $ sudo -u postgres createdb dbname
    

Giving the user a password

$ sudo -u postgres psql
psql=# alter user username with encrypted password 'password';

Granting privileges on database

  psql=# grant all privileges on database dbname to username ;
  

And yeah, that should be pretty much it !