I Need Help Setting Up Roles and Passwords in PostgreSQL

Question

I'm using the postgres.app on my Mac Mini Server running version 9.4.0 using El Capitan. I have not done any customization in pg-hba.conf. I do not have a .pgpass file. I believe that .pgpass is used to store passwords where you would not have to enter them when accessing a database instance. I really need more detailed understanding on the Unix terminology being used in pg-hba.conf.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

For my Ruby on Rails projects having roles with passwords with different types of access will be sufficient. From what I can tell everything I want would be considered local.

I submitted the two questions below in Stack Overflow last year and tried to implement them but to no avail. I ended up either locking myself out of PostgesSQL or with no passwords working. At that time I was using the built-in PostgreSQL instance on my Mac Mini Server.

https://stackoverflow.com/questions/22882280/postgresql-9-2-4-desire-to-change-the-localhost-server-password-in-mavericks

https://stackoverflow.com/questions/25167284/rails-4-desire-to-set-postgresql-password-access-database-in-rails

I want to add an encrypted password to my current role. I also want to add two more roles with encrypted passwords to use in my Ruby on Rails applications hosted on my Mac Mini server, one with read only access and the other with read/write access.

I assume I would execute the following commands in the PostgreSQL terminal or in pgAdmin.

CREATE USER readonly WITH ENCRYPTED PASSWORD 'readonlypasswordencryptedtext';
CREATE USER readwrite WITH ENCRYPTED PASSWORD 'readwritepasswordencryptedtext';
GRANT SELECT PRIVILEGES ON ALL TABLES IN public TO readonly, readwrite;
GRANT INSERT, UPDATE, DELETE PRIVILEGES ON TABLE table1, table2, table3, etc. TO readwrite;
ALTER ROLE myuserid WITH ENCRYPTED PASSWORD 'myuseridpasswordencryptedtext';

I would like help on how to change pg-hba.conf and in what order so I don't lock out myuserid AND if there are additional things I need to consider.

I have multiple databases with tables. I don't think my GRANT statements take that into consideration. Would I need to create a schema to include all my databases for the readonly user and the databases to update for the readwrite user?

Answer 1

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