Before you download PostgreSQL you must make two crucial decisions. First, decide whether to compile and install PostgreSQL from source or to install from prebuilt binaries. To install from source code you must compile the source with a C compiler (at least C89-compliant, in most cases people use GCC) to the binary format of your computer. Details on compilation options are available in the PostgreSQL documentation ^[1]. Second, you must know for which operating system you need the software. PostgreSQL supports most UNIX-based systems (including macOS) as well as Windows - both in 32- and 64-bit versions.

Download

After you have made the above decisions you can download the source code and/or the binaries from the official postgreSQL download page and its subpages. For some operating systems you will find a graphical installer which leads you through the subsequent installation steps. For the same or other operating systems the DB tool pgAdmin will also be downloaded as part of the PostgreSQL DBMS download. The pgAdmin tool helps you doing your daily work thru its graphical interface.

There are different versions available: the actual release, old releases and the upcoming release.

Installation

Installation steps vary depending on the chosen operating system. In the simplest case the above mentioned graphical installer hides the details. The PostgreSQL installation guide wiki and documentation leads you thru all necessary steps for your operating system.

If you install from source code, details are explained for Unix systems and Windows.

After a successful installation you will have

• The PostgreSQL binaries on your disc.
• A first database cluster called 'main' on your disc. The database cluster consists of an empty database called 'postgres' (plus two template databases) and an user/role called 'postgres' as well.
• A set of Unix programs or a Windows service running on your computer. These programs/service handle the database cluster as an unit.

sudo apt-get install postgresql

$ /etc/init.d/postgresql start

net start postgresql-x64-9.5
pause
net stop postgresql-x64-9.5

Creating a user

The following command creates a new user with superuser privileges:

$ createuser -U postgres -s <some username>

Creating a new user is a database operation, so it can only be done by an existing database user. You need to specify which database user to use (otherwise it will default to using a database user with the same name as your current UNIX user, which is unlikely to be correct). Default installs typically have a user called postgres that can be used for this.

To set a password for the newly created user, log in to the database using an account with superuser privileges, and issue the commands:

Note: '=#' is the psql command-prompt.

$ psql
postgres=# ALTER USER <username> WITH ENCRYPTED PASSWORD 'secret';

First steps

You can create your tables, views, functions etc. in this database 'postgres' and work with the user 'postgres'. But this approach is not recommended. The user 'postgres' has very high privileges by default and the database 'postgres' is sometimes used by tools and third party programs as a container for temporary data. You are encouraged to define your own database, one user who acts as the owner of the database and some application users.

As a first step start psql with user 'postgres' and create your own users. Please notice, that 'users' respective 'roles' are global objects which are known by all databases within the cluster, not only within a certain database. But users/roles have specific rights within each database.

$ psql
postgres=#
postgres=# -- the future owner of the new database shall be 'finance_master' with DDL and DML rights
postgres=# CREATE ROLE finance_master;
CREATE ROLE
postgres=# ALTER  ROLE finance_master WITH NOSUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION ENCRYPTED PASSWORD 'xxx';
ALTER ROLE
postgres=# -- one user for read/write and one for read-only access (no DDL rights)
postgres=# CREATE ROLE rw_user;
CREATE ROLE
postgres=# ALTER  ROLE rw_user WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION ENCRYPTED PASSWORD 'xxx';
ALTER ROLE
postgres=# CREATE ROLE ro_user;
CREATE ROLE
postgres=# ALTER  ROLE ro_user WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION ENCRYPTED PASSWORD 'xxx';
ALTER ROLE
postgres=#

Next, create a new database 'finance_db'. You can do this as user 'postgres' or as the previously created 'finance_master'.

postgres=#
postgres=# CREATE DATABASE finance_db
postgres=#    WITH OWNER = finance_master
postgres=#    ENCODING   = 'UTF8'
postgres=#    LC_COLLATE = 'en_US.UTF-8'
postgres=#    LC_CTYPE   = 'en_US.UTF-8';
CREATE DATABASE
postgres=# 

As the last step you have to delegate the intended rights to the users/roles. This is a little tricky because PostgreSQL uses an elaborated role system where every role inherits rights from the implicit 'public' role.

postgres=#
postgres=# \connect finance_db
finance_db=# -- revoke schema creation from role 'public' because all roles inherit their rights from 'public'
finance_db=# REVOKE CREATE ON DATABASE finance_db FROM public;
REVOKE
finance_db=# -- same: revoke table creation
finance_db=# REVOKE CREATE ON SCHEMA public FROM public;
REVOKE
finance_db=# -- grant only DML rights to 'rw_user', no DDL rights like 'CREATE TABLE'
finance_db=# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES    IN SCHEMA public TO rw_user;
GRANT
finance_db=# GRANT USAGE                          ON ALL SEQUENCES IN SCHEMA public TO rw_user;
GRANT
finance_db=# -- grant read rights to the read-only user
finance_db=# GRANT SELECT                         ON ALL TABLES    IN SCHEMA public TO ro_user;
GRANT
postgres=#

References