3.6. Allowing Read-Only Access to the History Database

To allow access to the history database without allowing edits, you must create a read-only PostgreSQL user that can log in to and read from the ovirt_engine_history database. This procedure must be executed on the system on which the history database is installed.

Allowing Read-Only Access to the History Database

Log in as root to the postgres user shell:
```
su - postgres
```
```
# su - postgres
```
Copy to Clipboard Toggle word wrap

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

psql -U postgres -c "CREATE ROLE username WITH LOGIN ENCRYPTED PASSWORD 'password';" -d ovirt_engine_history

# psql -U postgres -c "CREATE ROLE username WITH LOGIN ENCRYPTED PASSWORD 'password';" -d ovirt_engine_history

Copy to Clipboard

Toggle word wrap

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

psql -U postgres -c "GRANT CONNECT ON DATABASE ovirt_engine_history TO username;"

# psql -U postgres -c "GRANT CONNECT ON DATABASE ovirt_engine_history TO username;"

Copy to Clipboard

Toggle word wrap

Grant the newly created user usage of the public schema:

psql -U postgres -c "GRANT USAGE ON SCHEMA public TO username;" ovirt_engine_history

# psql -U postgres -c "GRANT USAGE ON SCHEMA public TO username;" ovirt_engine_history

Copy to Clipboard

Toggle word wrap

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 username;' 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

# psql -U postgres -c "SELECT 'GRANT SELECT ON ' || relname || ' TO username;' 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

Copy to Clipboard

Toggle word wrap

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
```
```
# psql -U postgres -f grant.sql ovirt_engine_history
```
Copy to Clipboard Toggle word wrap
Remove the file you used to grant permissions to the newly created user:
```
rm grant.sql
```
```
# rm grant.sql
```
Copy to Clipboard Toggle word wrap
Exit the postgres user shell by pressing Ctrl+d.

Add the following lines for the newly created user to /var/lib/pgsql/data/pg_hba.conf preceding the line beginning local all all:

TYPE  DATABASE                USER           ADDRESS                 METHOD

# TYPE  DATABASE                USER           ADDRESS                 METHOD
host    ovirt_engine_history    username    0.0.0.0/0               md5
host    ovirt_engine_history    username    ::0/0                   md5
local   all             all                                     peer

Copy to Clipboard

Toggle word wrap

Reload the PostgreSQL service:
```
systemctl reload postgresql
```
```
# systemctl reload postgresql
```
Copy to Clipboard Toggle word wrap

To test the read-only user’s access permissions:

su - postgres -c 'psql -U username ovirt_engine_history -h localhost'

# su - postgres -c 'psql -U username ovirt_engine_history -h localhost'
Password for user username:
psql (9.2.23)
Type "help" for help.

ovirt_engine_history=>

Copy to Clipboard

Toggle word wrap

To exit the ovirt_engine_history database, enter \q.

The read-only user’s SELECT statements against tables and views in the ovirt_engine_history database succeed, while modifications fail.

3.6. Allowing Read-Only Access to the History Database

Learn

Try, buy, & sell

Communities

About Red Hat Documentation

Making open source more inclusive

About Red Hat

Theme

Red Hat legal and privacy links

Red Hat legal and privacy links