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.
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?
Best Answer
Basic Role and User Management
By default, PostgreSQL stores encrypted passwords:
for some testing:
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:
In my example, I would like u_one to have read-only access. This user does not yet:
I'm going to attach u_one to read-only:
And now u_one can select from table tt.
Now I'll create a u_two who is readwrite:
you probably meant to allow u_two select privileges as well:
When you attempt to insert into tt using u_two:
You need to add insert, update, and/or delete privileges to the users who are going to modify this sequence:
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:
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