Installation in Ubuntu 14.04+

  sudo apt-get update
  sudo apt-get install postgresql postgresql-contrib

Concept of Roles

PostgreSQL manages database access permissions using the concept of roles. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up. Roles can own database objects (for example, tables) and can assign privileges on those objects to other roles to control who has access to which objects. Furthermore, it is possible to grant membership in a role to another role, thus allowing the member role use of privileges assigned to the role it is a member of.

The concept of roles subsumes the concepts of “users” and “groups”. In PostgreSQL versions before 8.1, users and groups were distinct kinds of entities, but now there are only roles. Any role can act as a user, a group, or both. During the Installation you will not be asked any creds.

See this for info

A role is an entity that can function as a user and/or as a group. A role WITH LOGIN can be used as a user, i.e. you can log in with it. Any role can function as a group, including roles that you can also log in as. So “user” and “group” are essentially terms that indicate the intended usage of a role, there’s no real distinction between them. Even in the PostgreSQL flavour of SQL the two are used more or less as synonyms. For example, the documentation on CREATE USER says:
CREATE USER is now an alias for CREATE ROLE. Granting all … see the manual for GRANT. You probably actually want to grant rights to ALL TABLES IN SCHEMA public rather than all tables in the database.
Granting some rights … same thing, but instead of GRANT ALL use GRANT SELECT, INSERT for example. Again, see the manual.
A role is a user, and/or a group. You can only grant to roles, because roles are all that there is.

Setup

When Installation completes you may want to see if any user is created. Actually, a user-role called postgres is created in your system and also added in users list.

  $ sudo -u postgres psql
  # psql (9.6.3)
  # Type "help" for help.
  
  postgres=# \du
  #                                 List of roles
  #  Role name |                         Attributes                         | Member of 
  # -----------+------------------------------------------------------------+-----------
  #  postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

  

If you are a Rails dev you would try like

  $ rails db:create

the you will see errors like

  FATAL:  role "john" does not exist
  Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "pool"=>5, "timeout"=>5000, "database"=>"arx_dev"}
  rails aborted!
  ActiveRecord::NoDatabaseError: FATAL:  role "john" does not exist

It means, if your machine’s current user is john then it will create a user called john.

  $ sudo -u postgres createuser john -s

still there will be error like

  peer authentication failed for user john

now you need to modify the file /etc/postgresql/9.6/main/pg_hba.conf

  $ sudo nano /etc/postgresql/9.6/main/pg_hba.conf
  
  # The file opens up in `nano` texteditor or you can use texteditor of your choice
  # $ sudo atom /etc/postgresql/9.6/main/pg_hba.conf  # for atom-editor users
  # $ sudo subl /etc/postgresql/9.6/main/pg_hba.conf  # for Sublime editor users
  

now you need to modify that particular line (i.e. 90 in general or see around 90)

# "local" is for Unix domain socket connections only
local   all             all                                     peer

to

# "local" is for Unix domain socket connections only
local   all             all                                     trust

It means you are asking pg to trust all the request coming via UNIX Sockets of your local machine. It works because requests coming from other machines will use TCP rather than unix sockets.


Tags

pg  postgresql  Configure  Rails  database.yml  installation  user  roles  cerate user