Re: [rt-users] RT setup and Postgres object ownership
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 phatbuck...@gmail.com We're hiring! http://bestpractical.com/jobs We're hiring! http://bestpractical.com/jobs
[rt-users] RT setup and Postgres object ownership
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 phatbuck...@gmail.com We're hiring! http://bestpractical.com/jobs
Re: [rt-users] RT setup and Postgres object ownership
On 11/26/2012 02:50 PM, Darren Spruell wrote: 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. We're hiring! http://bestpractical.com/jobs