2.6. Allowing Read-Only Access to the History Database
2.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 Enable psql commands: Create the user to be granted read-only access to the history database: Grant the newly created user permission to connect to the history database: Grant the newly created user usage of the public schema: Generate the rest of the permissions that will be granted to the newly created user and save them to a file: Use the file you created in the step to grant permissions to the newly created user: Remove the file you used to grant permissions to the newly created user: Exit the postgres user shell by pressing Ctrl + d . Add the following lines for the newly created user to /var/opt/rh/rh-postgresql10/lib/pgsql/data/pg_hba.conf preceding the line beginning local all all , as follows: Reload the PostgreSQL service: To test the read-only user's access permissions: 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.
su - postgres -c 'scl enable rh-postgresql10 bash' psql -U postgres -c "CREATE ROLE username WITH LOGIN ENCRYPTED PASSWORD ' password ';" -d ovirt_engine_history psql -U postgres -c "GRANT CONNECT ON DATABASE ovirt_engine_history TO username ;" psql -U postgres -c "GRANT USAGE ON SCHEMA public TO username ;" ovirt_engine_history 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 -f grant.sql ovirt_engine_history rm grant.sql 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 systemctl reload rh-postgresql10-postgresql su - postgres -c 'scl enable rh-postgresql10 -- psql -U username ovirt_engine_history -h localhost' Password for user username: psql (9.2.23) Type "help" for help. ovirt_engine_history=>