Running the RHQ agent as root? Alternatives? ACLs!

 

Some RHQ plugins require access to some resources that are normally only readable by root or the respective owner. The example I am using here is the postgres plugin. This plugin needs access to $PGDATA/postgresql.conf in order to show the configuration in the UI (and to possibly update it).

Unfortunately postgres requires this file to be owned by user postgres and only be read(-writable) by this user (mode 600) and the directory containing this file ($PGDATA) is also required to be owned by user postgres and only be accessible by user postgres (mode 700).

 

One way to access the data it to run the RHQ agent as root and be done. But even if the agent does not do any harm, many admins don't feel comfortable with it - especially when using plugins from third parties that they don't have the source for. Or when using the script plugin, which can use shell scripts to do its work.

Luckily there is an easy way to get around this limitation: ACLs

ACL (access control lists) are a posix feature that is implemented in most (all) modern system these days. The way to set and query them are different unfortunately.

On Red HatEnterprise Linux (and Fedora and probably all other Linuxes) you can set them like this ('hrupp' is used as agent user):

postgres$ pwd
/var/db/postgres
postgres$ setfacl -m u:hrupp:rw $PGDATA/postgresql.conf
postgres$ setfactl -m u:hrupp:x $PGDATA

ls shows that there are ACLs enabled:

root# ls -lsa
 8 drwx--x---+ 13 postgres postgres  4096 Dec 21 14:04 .
24 -rw-rw----+  1 postgres postgres 16872 Dec 17 12:11 postgresql.conf

See the little + in the perms? That indicates an active ACL. Those can be queried via getfacl:

root# getfacl .
# file: .
# owner: postgres
# group: postgres
user::rwx
user:hrupp:--x
group::---
mask::--x
other::---
 
root# getfacl postgresql.conf
# file: postgresql.conf
# owner: postgres
# group: postgres
user::rw-
user:hrupp:rw-
group::---
mask::rw-
other::---

Also remember that the mount options need to enable ACLs first.:

root# grep acl /etc/fstab
/dev/mapper/VG_data-data1       /var/db     ext4    defaults,acl    1 3

 

On Mac OS X the command to see them in directory listings is 'ls -lea' (shown below). To set an ACL you can use chmod (here 'hrupp' is used as agent user):

postgres$ pwd
/var/db/postgres
postgres$ chmod +a "hrupp allow read,write" postgresql.conf
postgres$ ls -le postgresql.conf
 -rw-------+ 1 postgres  postgres  16759 Jul 22  2009 postgresql.conf
0: user:hrupp allow read,write
postgres$ chmod +a "hrupp allow execute" .
postgres$ ls -lea
drwx------+ 23 postgres  postgres    782 Dec 30 15:00 . 
0: user:hrupp allow search
-rw-------+  1 postgres  postgres  16759 Jul 22  2009 postgresql.conf 
0: user:hrupp allow read,write

The '0:' tells us that this is the first acl on the file. If there were more acls set, they would be enumerated there as well and evaluated in order.

 

I have been told that recent Windows versions also support POSIX ACLs, so this should work there as well.

---

This tip was brought to you by the excellent RHCSA training.

 

PGDayEU 2010

I had the big luck to be able to attend the first day of PGDay EU conference here in Stuttgart. Conference was held at the SI-Erlebniszentrum - a location well known to me as the Java Forum Stuttgart took place there for many years.

The conference had around 200 attendees and the main tracks were given in two large rooms. Almost all of the "celebrities" like Simon Riggs, Dave Fetter, Magnus Hagander, Heikki Linnegas, Bruce Momijan (with this son!) were there.

I am not too much a database expert, so talks were very technical to me :) But not non-understandable :-)

One definitively cool talk was given by Gianni Ciolly from 2ndquadrant: he was playing chess against postgres (http://twitpic.com/3dfojr and http://yfrog.com/2q3nq01j). Gianni showed the SQL involved and then played against the DB. The chess figures involved were just done by UTF-8 characters :-)

Bruce Momijan talked about rapid upgrades from 8.x (or even 9.0) to 9.x via the re-written db_upgrade. With it upgrades of a huge database can take as little as 44 seconds (in link mode). Definitively something to have a look at.

After lunch I talked about "Servermonitoring mit RHQ" (in German). I had ~ 40 attendees, which was nice. There definitively was interest and I got some good questions afterwards. I've put my slides online at http://www.pilhuhn.de/hwr/misc/PGDay_EU_2010.pdf . If you want to know more about RHQ, visit http://rhq-project.org.

After attending Simon Riggs' talk about replication, I went to Devrim Gündüz, who was talking about failover using the Red Hat Cluster suite. This talk was very nicely presented with a lot of involvement of the speaker :-)

In the evening EnterpriseDB sponsored a party with food and drinks. There were lots of interesting talks at the tables going on, people all were very nice.

So this PGDay was a very positive experience for me. Unfortunately I could not make it to the 2nd day with more interesting talks.

Small tip when repeatedly doing upgrade testing

Suppose you want to test upgrading your software from version x to y. This often includes updates of database schemas, tables and content.

The obvious way to do this is

do {
  install version x
  quit x
  install version y
  verify upgrade
} while (upgrade was bad)

The install version x step here is usually time consuming and involves UI interactions.

A better approach here is to

install version x
create a db backup
verified = false
while (not verified )  {
  install version y
  if ( upgrade good )
      verified = true
  else
      install db backup
}

With PostgreSQL taking a backup would look like this:

pg_dump -f outfile -b -C dbname

e.g.:

pg_dump -f ~/jon231.dump -b -C jon231

and then the re-install:

pg_restore outfile

e.g.:

pg_restore ~/jon231.dump

Postgres/Hibernate sql fun - NOT

This probably isn't really a postgres, but a more general issue, but then ...

I was writing some stuff in EJB-QL and all I got from Postgres was

ERROR: could not identify an ordering operator for type record
SQLState:42883

This sucks of course :)
After some trial and error I found the following in my query:

group by resource4_.NAME , ... , (measuremen1_.TIME, measuremen1_.ID)

Removing the parens ('(',')') finally made the query run successfully.

Now you may ask, what was the input that led to this? Well, here is is:

SELECT new Foo(a.id, b.id, ..., bla.id, bla.time)
FROM .... , org.acme.Bla bla
GROUP by a.id, b.id, bla

So the query translated the bla in the GROUP BY to (bla,id, bla.time) - including the parens. Explicitly listing bla.id, bla.time in the GROUP BY clause solved this.
This is no fun, as the EJB-QL Query got correctly translated and postgres does not give any hint, what part of this longish SQL it does not like.

RHQ - tip of the day: postgres and login permission

When creating a database and database user for RHQ, Postgres knows two commands to create a user:

create user
and
create role

They both do the same thing and create a database user. The big difference (which may cause you some grey hair) is that create role does not automatically allow log in.

So to create a database user for RHQ, use the create user form. Example:

create user rhqadmin password 'rhqadmin'

If you have further login issues, see also my other posting about "local is not local"

---

Technorati Tags:
RHQ

Postgres: local is not local (or how to solve connection issues)

When setting up RHQ and doing some testing around an open bug report, I stumbled once more over the PostgreSQL access configuration. Usually I just configure it and it works, but this time it just did not do what I wanted it to do ... It is not that it is not documented, but as I have seen others fighting here too, I will comment a little.

PostgreSQL use two files that configure who can access the database. The first one, postgresql.conf defines the network interfaces, PostgreSQL is listening on:

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all

If this line is commented, as shown it will only listen on TCP sockets on localhost (127.0.0.1) for
IPv4 and IPv6(!) and additionally on a unix domain socket.
If you want to give access to people from another computer, you need to uncomment the listen_address entry and list the respective network interfaces to listen on. Then restart the PostgreSQL server.

The second file is pg_hba.conf. It contains the specific about which user may access which database with which way of connecting and authentication:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
 
# "local" is for Unix domain socket connections only
#local   all         all                               trust
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
#host    all         all         ::1/128               trust

When you connect to the database via psql, you are by default using a unix domain socket connection - so the line starting with "local" applies.
If you try to connect via a JDBC driver, as RHQ and JBossON do, the connection will be done via TCP, which means the lines starting with "host" are applicable.

So even if you are successfully able to connect to Postgres via psql, it does not mean, that you can do so via other means or from remote hosts.

So how can you verify the connection setup without first starting your java app? Well, psql allows you to supply a hostname -- if this is there, it will use a TCP connection. Note that if you specify
-h localhost, the connection could go over TCPv6. To force the use of TCPv4 use -h 127.0.0.1.

With the above pg_hba.conf this could look like this:

snert$ psql -h localhost -Urhqadmin -d rhq 
psql: FATAL:  no pg_hba.conf entry for host "::1", user "rhqadmin", database "rhq", SSL off

This is ok, as the line for ::1/128 was commented out

snert$ psql -h 127.0.0.1 -Urhqadmin -d rhq 
Password for user rhq:
Welcome to psql 8.3.3, the PostgreSQL interactive terminal.
rqh=>

Now we succeeded.

---

Technorati Tags:
RHQ

pg_stat_activity is your friend

PgAdmin for postgres has a nice feature called "Server status". It is great if you sit in front of a GUI.
But if you want to do some remote work or tell a customer to report that server status, it is not quite the right thing.

But at the end, pgAdmin is only doing

select * from pg_stat_activity order by procpid;


This will show you all client connections together with the command they are currently executing.

Real Benchmark on Postgres

Sun submitted results of the SpecJAppserver benchmark running on Glassfish and Postgres.
Josh Berkus discusses the results at his blog: http://blogs.ittoolbox.com/database/soup/archives/postgresql-publishes-first-real-benchmark-17470.
The benchmark results can be found here.

Congratulations to the whole team that made this possible. It is a big sign that Postgres is in fact fast (other than what urban legends are always trying to tell).

Of course no one has that configuration 'at home' and I would recommend JBossAS anyway :), but anyway ... :-)