Postgresql – Create Role or Create user

postgresqlpostgresql-11roleusers

PostgreSQL version : 11.2
OS platform : RHEL 7.6

I am new to PostgreSQL. I have a CRM application whose data I want to store in my 11.2 PostgreSQL Database.

The Database name will be : crm_db
The schema name will be : crm_app

I want all the schema objects in crm_app schema to be owned by a database user called crm_usr.

Can I just create a DB user using CREATE USER command like below or should I create a ROLE called crm_usr ? What is the recommended way to do this in 11.2 (the latest version at the time of posting this ie.23-Feb-2019)

CREATE user crm_usr identified by tiger235;

CREATE DATABASE crm_db OWNER crm_usr;

Best Answer

Note that in PostgreSQL a user and a role are the same thing. The difference is that a user can log in and a role cannot. So, to answer your question you need to decide if you want crm_usr to be able to login or not.

The approach that I try to take is to have two (or more) roles-- one for owning (i.e. xyz_owner) the objects and one or more (xyz_user) for performing the DML (selects, inserts, updates, etc.) on those objects. xyz_user gets granted to the actual users of the database and xyz_owner doesn't get granted to anyone.

Related Question