Set up a read-only database account on the terraref server.

[gsrohde]

On the database server for http://terraref.ncsa.illinois.edu/bety:

• [x] Create a "read-only" role (named "viewer").
• [x] Give Rob the password for user "viewer".
• [x] Give user "viewer" SELECT access to all public tables in the bety database.
• Remove trust authentication lines from pg_hba.conf and ensure scripts that rely on this authentication (if any) can still run by providing an alternative (Rob). —Tabled pending perceived need
• [x] Make sure /home/bety/bety/config/database.yml and other sensitive configuration files are not world readable.
• [x] Change database password for user "bety".
• [x] Update password in scripts that used the old password (if any) or provide alternative authentication path (Rob).
• [x] Update password in database.yml config file.

[gsrohde]

I ran these commands:

CREATE ROLE viewer;
GRANT USAGE ON SCHEMA public TO viewer;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO viewer;

As a test I added user jefft:

CREATE USER jefft IN ROLE viewer PASSWORD ******;

The pg_hba.conf file is controlled by puppet and is not supposed to be edited directly (it says). There are some changes that should be made if we don't want all machine users to have full access to the bety database.

If an ident server is running on bety6, we could use Ident authentication for all machine users, possibly using a mapping to map them all to database user viewer so that viewer access could be added or taken away merely by editing the pg_ident.conf file.

[jterstriep]

Should we have a service level account for this rather than manually adding each user to the database? We're using LDAP to authenticate the user to the VM and the service level account would have read-only access so I think it would reasonably secure and easier to administer.

[robkooper]

I agree with this, single account is easier. Only issue is if that password is given to others, but that should not matter since they can not access the database without SSH.

[gsrohde]

@robkooper If there are no scripts that rely on the old bety password, you can close this (see task list at top).

[dlebauer]

I've revoked SELECT access from the users table for purposes of security.

REVOKE SELECT ON TABLE "public"."users" FROM "viewer";

I've also revoked access from the PEcAn modeling tables so that only relevant tables are visible (to reduce the overhead of having to read through a long list of obscure tables. All of these "REVOKE" statements are here: https://gist.github.com/dlebauer/4aae063ac38d1dba9859da1ddfb5d2c5