POSTGRESQL-create db

Creating user, database and adding access on PostgreSQL

Arnav Gupta

Mar 14, 2017 · 2 min read

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 March 2017, running on Ubuntu 16.04.2, with PostgreSQL 9.6

Last login: Mon Mar 30 07:39:52 2020 from 192.168.201.104
root@ubuntu:~# su - postgres
postgres@ubuntu:~$ psql
psql (10.9 (Ubuntu 10.9-0ubuntu0.18.04.1))
Type "help" for help.

postgres=# \l
               

postgres=# CREATE DATABASE mydb;

postgres=# CREATE USER general WITH PASSWORD '%Y6G4pr0' ; 

CREATE ROLE

postgres=# ALTER DATABASE trackyol OWNER TO track ;
ALTER DATABASE

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;

Kalau Error kasih tanda kutip di DB nyapostgres=# GRANT ALL privileges on database "m-fusion-dev" to fusion;

---

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 !

---

Doing purely via psql

Your OS might not have the createuser or createdb binaries, or you may, for some reason want to do it purely via psql, then these are the three magic commands —

CREATE DATABASE yourdbname;
CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass';
GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;

---

Obligatory shameless self-plug :