2.6. Allowing Read-Only Access to the History Database

Procedure 2.1. Allowing Read-Only Access to the History Database

1. Create the user to be granted read-only access to the history database:

   # psql -U postgres -c "CREATE ROLE [user name] WITH LOGIN ENCRYPTED PASSWORD '[password]';" -d ovirt_engine_history

2. Grant the newly created user permission to connect to the history database:

   # psql -U postgres -c "GRANT CONNECT ON DATABASE ovirt_engine_history TO [user name];"

3. Grant the newly created user usage of the public schema:

   # psql -U postgres -c "GRANT USAGE ON SCHEMA public TO [user name];" ovirt_engine_history

4. Generate the rest of the permissions that will be granted to the newly created user and save them to a file:

   # psql -U postgres -c "SELECT 'GRANT SELECT ON ' || relname || ' TO [user name];' FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE nspname = 'public' AND relkind IN ('r', 'v');" --pset=tuples_only=on  ovirt_engine_history > grant.sql

5. Use the file you created in the previous step to grant permissions to the newly created user:

   # psql -U postgres -f grant.sql ovirt_engine_history

6. Remove the file you used to grant permissions to the newly created user:

   # rm grant.sql

7. Add the following lines for the newly created user to the /var/lib/pgsql/data/pg_hba.conf file:

   # TYPE  DATABASE                USER           ADDRESS                 METHOD
   host    ovirt_engine_history    [user name]    0.0.0.0/0               md5
   host    ovirt_engine_history    [user name]    ::0/0                   md5
   

8. Reload the PostgreSQL service:

   # systemctl reload postgresql
   

9. You can test the read-only user's access permissions:

   # psql -U [user name] ovirt_engine_history -h localhost
   Password for user [user name]: 
   psql (9.2.23)
   Type "help" for help.
   
   ovirt_engine_history=>

10. To exit the ovirt_engine_history database, enter \q.