Обсуждение: Extending postgres objects with attributes
Several times I wanted to "extend" some of the postgres objects, like roles or functions. For example, sometimes you want to add extra attributes to roles, which are application dependent. Or sometimes you want to store functions and reference them in your custom tables, without losing referential integrity. Now, postgres doesn't allow it's objects (from system tables) be referred to in your custom tables, thereby making the process of extension difficult. (cf. http://archives.postgresql.org/pgsql-general/2004-12/msg00840.php) So I wanted to ask here how people solve/implement such "extension" requirements? Regards, Davor PS using inheritance in this scenario is problematic. U can for example do this: CREATE TABLE tbl_privileges ( priv_execute integer ) INHERITS (pg_authid); but the system internally will probably always use FROM ONLY pg_authid, so it will not see the users from tbl_priviledges. IN other words, those user will not be "real roles". Note also the fact that in this way, pg_authid could seem to have it's UNIQUE constraint on rolname invalidated (besides the other INHERITANCE caveats...) And finally, I wonder if inheriting a system table is even safe...
On 04/07/10 21:43, Davor J. wrote: > PS using inheritance in this scenario is problematic. Yep. Just one issue is that roles are cluster-wide, whereas tables are visible only inside a single database. I generally use the role mechanism as-is, granting users access to roles that control particular privileges. Especially now that Pg has column privileges I rarely even need to use triggers to check for role membership - the standard permissions model is increasingly sufficient. If it's not for you, you could always maintain a table of additional privilege information that new roles with default privileges are added to when a priv check function first "sees" them. With a (say) daily pass to remove entries associated with roles that no longer exist, that should be fine. It's not as nice as using a proper inheritance/extension mechanism, but it leaves you a lot safer from changes caused by PostgreSQL upgrades. -- Craig Ringer
Thanks Craig. I still find it a bit awkward that we have to use "priv check function"-s because we can't define triggers on or reference to system tables. I think that allowing it would significantly extend Postgres possibilities. From a quick google it seems that triggers on system tables is allowed in MySQL. So, all this just leaves me wondering why this is not possible in Postgres. Regards, Davor "Craig Ringer" <craig@postnewspapers.com.au> wrote in message news:4C313581.2060506@postnewspapers.com.au... > On 04/07/10 21:43, Davor J. wrote: > > >> PS using inheritance in this scenario is problematic. > > Yep. Just one issue is that roles are cluster-wide, whereas tables are > visible only inside a single database. > > I generally use the role mechanism as-is, granting users access to roles > that control particular privileges. Especially now that Pg has column > privileges I rarely even need to use triggers to check for role > membership - the standard permissions model is increasingly sufficient. > > If it's not for you, you could always maintain a table of additional > privilege information that new roles with default privileges are added > to when a priv check function first "sees" them. With a (say) daily pass > to remove entries associated with roles that no longer exist, that > should be fine. It's not as nice as using a proper inheritance/extension > mechanism, but it leaves you a lot safer from changes caused by > PostgreSQL upgrades. > > -- > Craig Ringer > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On 06/07/10 17:47, Davor J. wrote: > Thanks Craig. > > I still find it a bit awkward that we have to use "priv check function"-s > because we can't define triggers on or reference to system tables. I think > that allowing it would significantly extend Postgres possibilities. Certainly being able to have fkey references to system tables and have triggers on them - or select system tables at least - would both be a bonus. > From a quick google it seems that triggers on system tables is allowed in > MySQL. > > So, all this just leaves me wondering why this is not possible in Postgres. I don't know all the reasons (its complicated) but I do know one big reason: many of the system tables are shared across the cluster. They're not specific to one particular database. Where would a trigger fire? Could triggers fire in multiple databases? What transaction context would these triggers run in? It's ... ugly. There's ongoing talk about trying to reduce the number of cluster-wide system tables - in particular, to permit user lists specific to a single database. At least one other issue is that many system tables are accessed and modified via non-SPI interfaces below the SQL level. Triggers don't make sense. AFAIK such direct access is sometimes done for performance reasons, sometimes historical reasons, and sometimes because access is required during setup/init before the SPI is ready to use. Knowing MySQL, I wouldn't be too shocked to discover that triggers are permitted on system tables ... but don't always work how you'd expect. That said, it might simply be designed to direct all modifications to those tables through SQL-level interfaces and to cleanly handle triggers cancelling or altering the effects of the queries issued. I doubt it. One thing that helps MySQL support system table triggers is that MySQL "databases" are like PostgreSQL "schemas" - they're just namespaces. Every database in MySQL is accessible from every other database, they can be combined in queries, etc. There's no equivalent of the shared-system-table problem. ( Personally I don't really understand why Pg has its cluster-and-databases design. Are there advantages I can't see over the schema-like thin databases design used by MySQL ? ) -- Craig Ringe
"Craig Ringer" <craig@postnewspapers.com.au> wrote in message news:4C33DC32.7080007@postnewspapers.com.au... > On 06/07/10 17:47, Davor J. wrote: >> Thanks Craig. >> >> I still find it a bit awkward that we have to use "priv check function"-s >> because we can't define triggers on or reference to system tables. I >> think >> that allowing it would significantly extend Postgres possibilities. > > Certainly being able to have fkey references to system tables and have > triggers on them - or select system tables at least - would both be a > bonus. > >> From a quick google it seems that triggers on system tables is allowed in >> MySQL. >> >> So, all this just leaves me wondering why this is not possible in >> Postgres. > > I don't know all the reasons (its complicated) but I do know one big > reason: many of the system tables are shared across the cluster. They're > not specific to one particular database. Where would a trigger fire? > Could triggers fire in multiple databases? What transaction context > would these triggers run in? It's ... ugly. You have a point there. > > There's ongoing talk about trying to reduce the number of cluster-wide > system tables - in particular, to permit user lists specific to a single > database. > > At least one other issue is that many system tables are accessed and > modified via non-SPI interfaces below the SQL level. Triggers don't make > sense. AFAIK such direct access is sometimes done for performance > reasons, sometimes historical reasons, and sometimes because access is > required during setup/init before the SPI is ready to use. > > Knowing MySQL, I wouldn't be too shocked to discover that triggers are > permitted on system tables ... but don't always work how you'd expect. > That said, it might simply be designed to direct all modifications to > those tables through SQL-level interfaces and to cleanly handle triggers > cancelling or altering the effects of the queries issued. I doubt it. > > One thing that helps MySQL support system table triggers is that MySQL > "databases" are like PostgreSQL "schemas" - they're just namespaces. > Every database in MySQL is accessible from every other database, they > can be combined in queries, etc. There's no equivalent of the > shared-system-table problem. > > ( Personally I don't really understand why Pg has its > cluster-and-databases design. Are there advantages I can't see over the > schema-like thin databases design used by MySQL ? ) Well, it certainly looks more advanced, as far as this might be called an advantage. I don't know by enough to judge the issue, but those are all interesting questions you bring up. Thanks. Regards, Davor > > -- > Craig Ringe > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >