Create user and database in PostgreSQL

7th April, 2021

During a set up of any new PostgreSQL server, one often immediately needs to create a user and database for immediate use. After that, one has to take care of user privileges as well on the database. Here we provide a list of commands for such cases.

In the first step one has to install PostgreSQL server (the following commands are based on UBUNTU and PostgreSQL-12):

sudo apt-get install wget ca-certificates -y
wget --quiet -O - | sudo apt-key add -
sudo sh -c 'echo "deb `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib -y


Next will be now to create a user and database.

$ sudo su postgres
$ psql
postgres=#SHOW SERVER_ENCODING; # It should be UTF8
postgres=#CREATE USER myusername WITH ENCRYPTED PASSWORD 'mypassword';
postgres=#CREATE DATABASE mydatabase;
postgres=#GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myusername;

# If certain privileges are to be granted
# First USAGE on schema
postgres=#GRANT USAGE ON SCHEMA myschema TO myusername;
# Now specific privileges
postgres=#GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO myusername;


That's it ! Now we have a database with proper user access.