[rt-users] RT setup and Postgres object ownership

[ktm at rice.edu]

Hi Darren,

You can grant access using roles in PostgreSQL. So even though
the DB is owned by postgres, your configured rt user can still
access the data.

Cheers,
Ken

On Mon, Nov 26, 2012 at 03:50:58PM -0700, Darren Spruell wrote:
> 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
> --------
> We're hiring! http://bestpractical.com/jobs
>