[rt-users] RT setup and Postgres object ownership

[Darren Spruell]

Recent user of RT on PostgreSQL backend (a couple of years on MySQL)
and have been struggling to understand something related to DB access
privileges under postgres with RT's setup.

I don't understand how the default ownership/rights after the RT
setup, done using the postgres superuser (DBA), allow the RT user to
have read/write access to RT objects. What I got:

# Database ownership set to Pg superuser. No privileges for other roles present.
postgres=# \l+
   Name    |     Owner      | Encoding |   Collate   |    Ctype    |
Access privileges   |  Size   | Tablespace |
Description
-----------+----------------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 rt4       | postgres       | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
                    | 9061 kB | pg_default |


# Only a single schema, "public". Only Pg superuser granted explicit
privileges; no privileges for other roles present.
rt4=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres


# Owner of all tables is also DB superuser (by inheritance, I'm assuming).
rt4=# \dt+
                               List of relations
 Schema |          Name           | Type  |  Owner   |    Size    | Description
--------+-------------------------+-------+----------+------------+-------------
 public | acl                     | table | postgres | 40 kB      |
 public | articles                | table | postgres | 8192 bytes |
 public | attachments             | table | postgres | 72 kB      |
 public | attributes              | table | postgres | 96 kB      |
 public | cachedgroupmembers      | table | postgres | 48 kB      |
 public | classes                 | table | postgres | 16 kB      |
 public | customfields            | table | postgres | 16 kB      |
 public | customfieldvalues       | table | postgres | 88 kB      |
 public | groupmembers            | table | postgres | 8192 bytes |
 public | groups                  | table | postgres | 48 kB      |
 public | links                   | table | postgres | 16 kB      |
 public | objectclasses           | table | postgres | 8192 bytes |
 public | objectcustomfields      | table | postgres | 40 kB      |
 public | objectcustomfieldvalues | table | postgres | 16 kB      |
 public | objecttopics            | table | postgres | 0 bytes    |
 public | principals              | table | postgres | 40 kB      |
 public | queues                  | table | postgres | 16 kB      |
 public | scripactions            | table | postgres | 16 kB      |
 public | scripconditions         | table | postgres | 16 kB      |
 public | scrips                  | table | postgres | 48 kB      |
 public | sessions                | table | postgres | 160 kB     |
 public | templates               | table | postgres | 48 kB      |
 public | tickets                 | table | postgres | 8192 bytes |
 public | topics                  | table | postgres | 8192 bytes |
 public | transactions            | table | postgres | 48 kB      |
 public | users                   | table | postgres | 16 kB      |


I'm guessing this is more a a postgres access privileges topic than a
RT topic - but curious how the application DB user has privileges to
these objects, and also why the default installation doesn't set the
app user (rt_user) as the owner of the database and then allow
inheritance to set ownership on child objects.

-- 
Darren Spruell
phatbuckett at gmail.com