RT setup and Postgres object ownership

Darren_Spruell · November 26, 2012, 10:50pm

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.

Only a single schema, “public”. Only Pg superuser granted explicit

Owner of all tables is also DB superuser (by inheritance, I’m assuming).

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@gmail.com

Thomas_Sibley · November 26, 2012, 11:02pm

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.

RT runs the etc/acl.Pg file during install and etc/upgrade/*/acl.Pg
during upgrade. This file generates GRANT commands to run for the
application user. See the output of “\dp” in psql for what permissions
are currently granted.

There are similar acl.* files for other database types, as necessary.

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.

Making the application user the owner would allow the user to do much,
much more than the SELECT, INSERT, UPDATE, and DELETE currently allowed
of it. It’s poor practice to give the application user more privileges
than necessary in case the front end is compromised by a malicious user.

Kenneth_Marshall · November 27, 2012, 1:51pm

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,
KenOn 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@gmail.com

We’re hiring! Careers — Best Practical Solutions

RT setup and Postgres object ownership

Database ownership set to Pg superuser. No privileges for other roles present.

Only a single schema, “public”. Only Pg superuser granted explicit

Owner of all tables is also DB superuser (by inheritance, I’m assuming).

Database ownership set to Pg superuser. No privileges for other roles present.

Only a single schema, “public”. Only Pg superuser granted explicit

Owner of all tables is also DB superuser (by inheritance, I’m assuming).

– Darren Spruell phatbuckett@gmail.com

–
Darren Spruell
phatbuckett@gmail.com