Using PostgreSQL? Let’s create the database and give privilege to a user on it.

Overview

One of my favorite DB is mySQL, which easy for me to create a user with access to only one database. What about postgreSQL? Here I’ll show you how to do the same thing. With…. psql

Requisite

• Terminal knowledge 😁
• PostgreSQL installed

How To

Quick Start

Here I’m using PostgreSQL run using docker. If you using standalone postgresql it should be ok.

Using direct posgres command to create database & user

• To create database sudo -u postgres createdb <dbname>.
• To create user sudo -u postgres createuser <username>.

Using pure command from psql

• Let’s get to postgresql command line interface as postgres. The command is sudo -u postgres psql.
• [optional] If you run postgresql using docker, it might be no postgres user by default. Create new linux user first with sudo adduser postgres. Then log into it using sudo su - postgres next you can try again sudo -u posgres psql.
• To create database we can use CREATE DATABASE yourdbname;. Remember, don’t forget ; character.
• To create user use CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass';.
• Then give your user a privilege to access the database, use GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;.
• Below is extra command for PostgreSQL 15 upward as this source:

  PostgreSQL 15 also revokes the CREATE permission from all users except a database owner from the public (or default) schema.

• Run command and connect the database as postgres with \c yourdbname postgres.
  # You are now connected to database "yourdbname" as user "postgres"..
• Grant public scheme to youruser. GRANT ALL ON SCHEMA public TO youruser;.

Now with this you can give postgreSQL user to access, create, update anything in a database but not the other database. Hope this article can help your work more faster. 😉
As always, see you again next post. 👋