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;


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.

| improve this answer | |

In Postgres CREATE USER x is simply syntactic sugar for CREATE ROLE x LOGIN. The commands you want are something like

   CREATE USER crm_usr PASSWORD 'tiger235';
   CREATE DATABASE crd_db OWNER crm_user;
| improve this answer | |

In PostgreSQL, it is recommended to have a user name and schema name same.

Becauase when you login with user X, it searches in schema X for objects directly, otherwise if you want to see schema Y objects with schema X user, you have to set search_path explicitly.

so, before creating the schema or user with your requirement, make sure you understand this concept.

And coming back to your question, user is someone who owns something, role is something which is a combination of users put together or simply a role depending on how it is configured.

| improve this answer | |

Thank You gsiems, Gauis, Naresh. I too liked the idea of having a separate object owner which doesn't log in and then grant DML privileges on the its objects to an app_user. Plus, I will follow Naresh's advice on keeping schema name and schema owner's user name same.

I have 2 questions on this.

Question1. Does the below configuration,naming conventions follow best practices ? Any suggestions, however trivial, are welcome.

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

-- Create the database and schema which will be owned by crm_master

CREATE user crm_master login password 'tiger235' createdb ;

---- Now, Connect as crm_master user and create the database and schema

CREATE DATABASE crm_db OWNER crm_master;

--- Creating CRM_MASTER schema (same name as its owner user crm_master)

create schema crm_master authorization crm_master;

---Now create all objects (tables, views functions, ...etc)

create table crm_master.emp (empid integar, first_name text);

--- Create the app user

CREATE user crm_user login password '1sland#' ;

--- Grant SELECT + DML privileges on all objects owned by crm_master to crm_user

Question2. From the application, when I want to refer a schema object, do I always have to prefix schema name before the object name ?

Eg: select first_name from crm_master.emp ;
Is there a way I could avoid prefixing schema name before object names ?

| improve this answer | |
  • You can do something like SET search_path = crm_master, pg_catalog; when you connect or even ALTER DATABASE crm_db SET search_path = crm_master, pg_catalog; so that the path is always set. The fine documentation (postgresql.org/docs/11/ddl-schemas.html#DDL-SCHEMAS-PATH) has more details. – gsiems Feb 26 '19 at 2:05

My 2 cents for your Question2.
Assuming your application is written in Java.
Look for currentSchema parameter. https://jdbc.postgresql.org/documentation/head/connect.html

If its a script called from psql you can use SET SCHEMA

$ psql -U wms -d wms_dev
Password for user wms: 
psql (11.2)
Type "help" for help.

wms_dev=> show search_path
wms_dev-> ;
 "$user", public
(1 row)

wms_dev=> set schema 'wms';
wms_dev=> show search_path;
(1 row)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.