Bug 875500

Alex can you please add instructions on how to configure poatgres to allow remote access from a specific host.

it should include:
- specific postgres configuration
- Iptables configuration

(In reply to comment #2)
> Alex can you please add instructions on how to configure poatgres to allow
> remote access from a specific host.
> 
> it should include:
> - specific postgres configuration
> - Iptables configuration

From a documentation POV we need to see this information so that we can scope these changes accurately. While the section of the Admin Guide linked in comment # 6 seems relevant it's quite possible some of this information also belongs in the Installation Guide.

For now this will remain docs_scoped? (not scoped) until we see some more info here.

Discussed with Dan - Steve G has agreed to document this.
Dan Yasny will provide QA.

To reset requirements:

https://access.redhat.com/knowledge/docs/en-US/Red_Hat_Enterprise_Virtualization/3.1/html/Administration_Guide/Connecting_to_the_history_database.html

should contain the following:

To setup the postgres to serve to all or specific addresses, configure the service:

* All configuration files are located at /var/lib/pgsql/data.

1. Edit postgresql.conf file. Set 2 params:

listen_addresses = "*" (means all, or use comma separated list)
ssl=on

2. Edit pg_hba.conf
in IPv4 section add the line:
hostssl    all             all             <net address/mask>            md5 

3. Follow the commands in postgresql manual (http://www.postgresql.org/docs/8.4/static/ssl-tcp.html#SSL-FILE-USAGE) to create server.key, server.crt files in the /var/lib/pgsql/data.

4. Restart postgresql service.

5. Add iptables rules that allow RHEVM machine to connect to the DB server:
iptables -A INPUT -p tcp -m state --state NEW -m tcp --dport 5432 -j ACCEPT

(In reply to comment #18)
> 4. Restart postgresql service.

Do the ovirt-engine and/or ovirt-engine-dwhd services need to be stopped first?

> 5. Add iptables rules that allow RHEVM machine to connect to the DB server:
> iptables -A INPUT -p tcp -m state --state NEW -m tcp --dport 5432 -j ACCEPT

I don't think we want to say "RHEVM machine" here.  That would only make sense if the database were running on a machine other than the RHEV Manager, in which case all of this would have already been done.

Also, if the iptables configuration is anything close to the default, appending the new rule to the INPUT chain (-A) will add it to the end, after the REJECT rule, so it will have no effect.

Maybe something like:

5. Add an iptables rule to allow non-local access to the database, for example:

  iptables -I INPUT 5 -p tcp -m state --state NEW --dport 5432 -j ACCEPT

In a semi-default configuration, this will add the rule just below the ssh rule.

(In reply to comment #19)
> (In reply to comment #18)
> > 4. Restart postgresql service.
> 
> Do the ovirt-engine and/or ovirt-engine-dwhd services need to be stopped
> first?
> 

engine - yes (as best practice), dwh can survive without being stopped

> I don't think we want to say "RHEVM machine" here.  That would only make
> sense if the database were running on a machine other than the RHEV Manager,
> in which case all of this would have already been done.

agreed

> 
> Also, if the iptables configuration is anything close to the default,
> appending the new rule to the INPUT chain (-A) will add it to the end, after
> the REJECT rule, so it will have no effect.
> 
> Maybe something like:
> 
> 5. Add an iptables rule to allow non-local access to the database, for
> example:
> 
>   iptables -I INPUT 5 -p tcp -m state --state NEW --dport 5432 -j ACCEPT
> 
> In a semi-default configuration, this will add the rule just below the ssh
> rule.

makes sense, but I'd just put in a generic explanation, something like:
"Insert the iptables rule at a reasonable location among the existing rules, for example, in a default setup, the following will insert the postgresql rule just after SSH: 
iptables -I INPUT 5 -p tcp -m state --state NEW --dport 5432 -j ACCEPT

Then save the iptables configuration using iptables-save..."

Use this manual to un-deploy and redeploy the jasperreports server war after postgres restart:
https://community.jboss.org/wiki/DeployAndUndeployWithTheCLI



Yaniv

(In reply to comment #18)
> 3. Follow the commands in postgresql manual
> (http://www.postgresql.org/docs/8.4/static/ssl-tcp.html#SSL-FILE-USAGE) to
> create server.key, server.crt files in the /var/lib/pgsql/data.

I think that it would be useful to provide an example of using the SSL certificate
that was created when the manager was initially installed:

* /etc/pki/ovirt-engine/certs/engine.cer
* /etc/pki/ovirt-engine/keys/engine_id_rsa

I'll summarize what is required to resolve this bug. I need to expand this topic (8281):

https://access.redhat.com/knowledge/docs/en-US/Red_Hat_Enterprise_Virtualization/3.1/html/Administration_Guide/Connecting_to_the_history_database.html

It should be built into a procedure containing the following.

To setup the postgres to serve to all or specific addresses, configure the service. All postgres configuration files are located at /var/lib/pgsql/data.

1. Edit postgresql.conf file. Set 2 params:

listen_addresses = "*" (means all, or use comma separated list)
ssl=on

2. Edit pg_hba.conf
in IPv4 section add the line:
hostssl    all             all             <net address/mask>            md5 

3. Follow the commands in postgresql manual (http://www.postgresql.org/docs/8.4/static/ssl-tcp.html#SSL-FILE-USAGE) to create server.key, server.crt files in the /var/lib/pgsql/data.  Provide an example of using the SSL certificate
that was created when the manager was initially installed:

* /etc/pki/ovirt-engine/certs/engine.cer
* /etc/pki/ovirt-engine/keys/engine_id_rsa

4. Stop the ovirt-engine service.

5. Restart postgresql service.

6. Use this manual to un-deploy and redeploy the jasperreports server war after postgres restart:
https://community.jboss.org/wiki/DeployAndUndeployWithTheCLI

7. Add iptables rules that allows the external machine to connect to the DB server.Insert the iptables rule at a reasonable location among the existing rules, for example, in a default setup, the following will insert the postgresql rule just after SSH: 
iptables -I INPUT 5 -p tcp -m state --state NEW --dport 5432 -j ACCEPT

8. Start the ovirt-engine service. 

From what I can gather, if I do this, I'll have provided the content required to resolve this bug.

Does that about cover it Andy? 

Without help, I should be able to provide this procedure, EXCLUDING:

- "Allowing Read-Only Access to the ovirt-engine-dwh Databause using Postgres Roles" to address Itamar's concern
- Provide an example of using the SSL certificate that was created when the manager was initially installed as suggested by Ian.

(In reply to comment #27)
> Does that about cover it Andy? 
> 
> Without help, I should be able to provide this procedure, EXCLUDING:
> 
> - "Allowing Read-Only Access to the ovirt-engine-dwh Databause using
> Postgres Roles" to address Itamar's concern
> - Provide an example of using the SSL certificate that was created when the
> manager was initially installed as suggested by Ian.

ACK

Red_Hat_Enterprise_Virtualization-Administration_Guide-3.2-web-en-US-3.2-08.el6

Eli - please provide the deatils for missing info (read only access, ssl config)

Eli sent this yesterday:

Creating a read-only role:
Procedure for PG 8.x (in PG 9.x this can be done by a one-liner command...):
assuming the database name is mydb, the read only role is readonlyrole and the admin role is postgres

1) psql -U postgres -c "CREATE ROLE readonlyrole WITH LOGIN;" mydb
2) psql -U postgres -c "GRANT CONNECT ON DATABASE mydb TO readonlyrole;" mydb
3) psql -U postgres -c "GRANT USAGE ON SCHEMA public TO readonlyrole;" mydb
4) psql -U postgres -c "SELECT 'GRANT SELECT ON ' || relname || ' TO readonlyrole;' 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  mydb > grant.sql
5) psql -U postgres -f grant.sql mydb
6) rm grant.sql

Check by :
1) psql -U readonlyrole mydb
2) do a select statement against one of mydb tables/views , this should succeed
3) do insert/delete/update against one of mydb tables, this should fail


As for ssl, Tim seems to already have that covered

1. the script should be re-entrant probably, since we are going to add this out of the box probably in 3.3.

2. db name should be the dwh db name.

3. role name should probably be ovirt-dwh/history-readonly, to make it clear what it is about.

Created:
Allowing Read Only Access to the History Database [13869, rev:379911]

Created attachment 706080 [details]
script for creating a read-only role on PG 8.x database

create_read_only_role.sh [-h] [-s SERVERNAME] [-p PORT] -d DATABASE -u USERNAME -r ROLENAME [-v] 

        -s SERVERNAME - The database servername for the database (def. localhost)
        -p PORT       - The database port for the database       (def. 5432)
        -d DATABASE   - The database name                        (def. engine)
        -u USERNAME   - The admin username for the database.
        -r ROLE       - The role name to be created
        -v            - Turn on verbosity (WARNING: lots of output)
        -h            - This help text.

Hey Eli,

I wrote up what was provided in comment #31. Is that script to be included in the documentation? I guess that would mean it has been QE approved, and we're happy to provide it to customers as such?