Rainbow Coding

Introducing Create user in PostgreSQL

Here are common ways to do development in PostgreSQL by creating a user (role).

PostgreSQL uses roles to allow access to databases and other actions.

First we need to be a superuser to create users and databases. In usual case postgres user has superuser rights by default. To connect database as supseruser locally run the following:

sudo -u postgres psql

The easiest way to create user for development and get started is to create superuser with same name as your username.

CREATE ROLE myusername LOGIN SUPERUSER;

This will allow you to use the server locally without password, and with superuser permissions you can create databases and alter tables. You should be now able to do following.

psql postgres

With password

If you do want to set password (and you probably should), you can achieve it by doing following:

CREATE ROLE myusername LOGIN SUPERUSER PASSWORD 'password';

Creating databases

To create databases from command line you can run following command in shell

createdb mydatabasename

or in psql

CREATE DATABASE mydatabasename

Multiple users

If you develop multiple applications or have multiple databases, it might be good idea to create a user per database, instead of using superuser. You can do this with

CREATE ROLE myusername LOGIN PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE my_database TO myusername;
© RainbowCoding 2010-2021