I Need Help Setting Up Roles and Passwords in PostgreSQL
Basic Role and User Management

By default, PostgreSQL stores encrypted passwords:

create user u_one with password 'password';

select * 
from pg_authid
where rolname = 'u_one';

-[ RECORD 1 ]--+------------------------------------
rolname        | u_one
rolsuper       | f
rolinherit     | t
rolcreaterole  | f
rolcreatedb    | f
rolcatupdate   | f
rolcanlogin    | t
rolreplication | f
rolconnlimit   | -1
rolpassword    | md54e83b97ec0bf59917d9d2689a961e390
rolvaliduntil  | 


for some testing:

create table tt (
    a serial primary key,
    v varchar
);

insert into tt (v)
values ('one'), ('two');


the goal is to create one user who has read-only access and one user who has all privileges. It is better to create roles that have the privileges defined and then attach users to each of those roles:

create role readonly;
create role readwrite;

grant select on all tables in schema public to readonly;
grant insert, update, delete on table tt to readwrite;


In my example, I would like u_one to have read-only access. This user does not yet:

select * 
from tt;
ERROR:  permission denied for relation tt


I'm going to attach u_one to read-only:

grant readonly to u_one;


And now u_one can select from table tt.

Now I'll create a u_two who is readwrite:

create user u_two with password 'password';
grant readwrite to u_two;


you probably meant to allow u_two select privileges as well:

grant readonly to u_two;


When you attempt to insert into tt using u_two:

insert into tt (v) values ('three');
ERROR:  permission denied for sequence tt_a_seq


You need to add insert, update, and/or delete privileges to the users who are going to modify this sequence:

grant usage on tt_a_seq to readwrite;


Notice how I only added this to the role, and not the user: u_two is  now able to insert a new record.

Using this role / user strategy will give you considerable flexibility by reducing duplicate permissions and allowing you to quickly create and set up new users.

The HBA config file:

By default, all users will be able to login from localhost, and as long as that is true, you won't have to make changes to pg_hba.conf. You will need to make changes if you want a user to be able to access the database from another ip-address (remote computer).

If you are going to grant remote access:

DATABASE: host --
USER: the username
ADDRESS: the ip address the user is connecting from 
(I doubt this will work with roles since roles do not have passwords,     
 though I've never tried either)
(if you want the user to be able to connect from any IP, then use
0.0.0.0/0)
METHOD: md5


PostgreSQL docs on creating roles and user syntax.
http://www.postgresql.org/docs/current/static/sql-grant.html

Granting privileges to sequences:
https://stackoverflow.com/questions/9325017/error-permission-denied-for-sequence-cities-id-seq-using-postgres

PostgreSQL docs on hba:
http://www.postgresql.org/docs/9.4/static/auth-pg-hba-conf.html