Re: [GENERAL] how to auto GRANT custom ACL on a new table?
Maciej Sieczka wrote: Isn't there a ready to use sort of addon for PgSQL which could intercept the CREATE TABLE that eg. Base (or any other client) issues, appdend GRANT to it and forward such a modified instruction to the server? I looked a lot in list archives, PgFoundry, Google and can't find a tool. Your hints are more than welcome! I'm afraid I don't know of anything. The problem is that the requirements for this sort of thing are different for every user. Generally it's because you have a client application you don't control and want to emulate another backend. That's usually not a complicated job. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] how to auto GRANT custom ACL on a new table?
Maciej Sieczka pisze: Richard Huxton pisze: 1. Add a proxy in front of PostgreSQL and have it intercept the CREATE TABLE statements and follow them with the correct GRANT statements. I'd probably modify the code for pgpool/pgbouncer (http://pgfoundry.org/) That should do it seems! Thanks a lot! Dear All Said too soon, not carefully reading Richard's words first: probably modify the code - I'm no programmer (only shell, awk and some python savvy). Isn't there a ready to use sort of addon for PgSQL which could intercept the CREATE TABLE that eg. Base (or any other client) issues, appdend GRANT to it and forward such a modified instruction to the server? I looked a lot in list archives, PgFoundry, Google and can't find a tool. Your hints are more than welcome! Best Maciek ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] how to auto GRANT custom ACL on a new table?
Maciej Sieczka wrote: I need to modify this default PostgreSQL's behaviour, so that the ACL on a new table in this schema is set to SELECT, INSERT, UPDATE, DELETE, REFERENCES for editors, and SELECT for viewers, without having to manually GRANT rights each time a new table is created. I can't control this setting from the client software as these are various programs, and even if I could it'd be still better anyway to have it controlled in one single place on the server side. But how? From reading so far I *suppose* I should create a function which calls an appropriate GRANT, and trigger it when a new record is added to pg_class. Is this doable? Afraid not. You can't add a trigger to a system table. The only real solution is to have your own function do both - create the table and then grant relevant permissions. Only allow client applications permissions to execute the function, not create tables directly. An extra, but desired functionality, would be if I could also prevent other editors from modifying the table *if* it is being currently being edited by somebody. Is that feasible at all? Sure, just issue LOCK TABLE ... at the start of the transaction. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] how to auto GRANT custom ACL on a new table?
Richard Huxton pisze: Maciej Sieczka wrote: I need to modify this default PostgreSQL's behaviour, so that the ACL on a new table in this schema is set to SELECT, INSERT, UPDATE, DELETE, REFERENCES for editors, and SELECT for viewers, without having to manually GRANT rights each time a new table is created. I can't control this setting from the client software as these are various programs, and even if I could it'd be still better anyway to have it controlled in one single place on the server side. But how? From reading so far I *suppose* I should create a function which calls an appropriate GRANT, and trigger it when a new record is added to pg_class. Is this doable? Afraid not. You can't add a trigger to a system table. Too bad, but thanks much for clarifying this. The only real solution is to have your own function do both - create the table and then grant relevant permissions. Only allow client applications permissions to execute the function, not create tables directly. Users connect to the DB with different clients - eg. OO.org Base. I can't change, easily, how it creates a table (withouth modyfing the source code of Base, or maybe the postgresql-sdbc plugin). It just issues a CREATE TABLE. So would MS Access, Kexi etc., which might come into play later. Solving the issue on the server side would be most robust. Really not doable? An extra, but desired functionality, would be if I could also prevent other editors from modifying the table *if* it is being currently being edited by somebody. Is that feasible at all? Sure, just issue LOCK TABLE ... at the start of the transaction. I guess you assume I can modify the client software, which isn't a feasible option for me. Can I solve the issue on the server side - detect the table is open and lock it then, unlock once closed? Maciek ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] how to auto GRANT custom ACL on a new table?
Maciej Sieczka wrote: Richard Huxton pisze: Afraid not. You can't add a trigger to a system table. Too bad, but thanks much for clarifying this. The only real solution is to have your own function do both - create the table and then grant relevant permissions. Only allow client applications permissions to execute the function, not create tables directly. Users connect to the DB with different clients - eg. OO.org Base. I can't change, easily, how it creates a table (withouth modyfing the source code of Base, or maybe the postgresql-sdbc plugin). It just issues a CREATE TABLE. So would MS Access, Kexi etc., which might come into play later. Solving the issue on the server side would be most robust. Really not doable? Not as things stand. There seem to be (I'm just a user, not a developer) two main issues: 1. There are optimisations on system tables, which make it tricky to also add triggers. 2. You also need a way to handle triggers going wrong, otherwise you can end up with a database that's unusable. I can only think of two workarounds (since you can't modify the client code). 1. Add a proxy in front of PostgreSQL and have it intercept the CREATE TABLE statements and follow them with the correct GRANT statements. I'd probably modify the code for pgpool/pgbouncer (http://pgfoundry.org/) 2. Add a timed job that checks for any new tables and runs the GRANT. This isn't very neat but it should be safe enough since you want to extend permissions rather than reduce them. An extra, but desired functionality, would be if I could also prevent other editors from modifying the table *if* it is being currently being edited by somebody. Is that feasible at all? Sure, just issue LOCK TABLE ... at the start of the transaction. I guess you assume I can modify the client software, which isn't a feasible option for me. Can I solve the issue on the server side - detect the table is open and lock it then, unlock once closed? There's no table is open to detect. I take it this client code isn't really designed to operate with an SQL database, rather some sort of dbase/foxpro files? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] how to auto GRANT custom ACL on a new table?
Richard Huxton pisze: Maciej Sieczka wrote: Richard Huxton pisze: Afraid not. You can't add a trigger to a system table. Too bad, but thanks much for clarifying this. The only real solution is to have your own function do both - create the table and then grant relevant permissions. Only allow client applications permissions to execute the function, not create tables directly. Users connect to the DB with different clients - eg. OO.org Base. I can't change, easily, how it creates a table (withouth modyfing the source code of Base, or maybe the postgresql-sdbc plugin). It just issues a CREATE TABLE. So would MS Access, Kexi etc., which might come into play later. Solving the issue on the server side would be most robust. Really not doable? Not as things stand. There seem to be (I'm just a user, not a developer) two main issues: 1. There are optimisations on system tables, which make it tricky to also add triggers. 2. You also need a way to handle triggers going wrong, otherwise you can end up with a database that's unusable. I can only think of two workarounds (since you can't modify the client code). 1. Add a proxy in front of PostgreSQL and have it intercept the CREATE TABLE statements and follow them with the correct GRANT statements. I'd probably modify the code for pgpool/pgbouncer (http://pgfoundry.org/) That should do it seems! Thanks a lot! 2. Add a timed job that checks for any new tables and runs the GRANT. This isn't very neat but it should be safe enough since you want to extend permissions rather than reduce them. I've been aware of this option but I take it as a last resort. I'd rather set ACL as needed instantly when the table is created. An extra, but desired functionality, would be if I could also prevent other editors from modifying the table *if* it is being currently being edited by somebody. Is that feasible at all? Sure, just issue LOCK TABLE ... at the start of the transaction. I guess you assume I can modify the client software, which isn't a feasible option for me. Can I solve the issue on the server side - detect the table is open and lock it then, unlock once closed? There's no table is open to detect. I take it this client code isn't really designed to operate with an SQL database, rather some sort of dbase/foxpro files? I'd like to prevent concurrent editing of a table. For example PostGIS tables, which are going to be stored in the database too: once a user starts editing the table in QGIS [1], it should be locked (for writing at least), until he turns the edit mode off and commits changes. Only then another user should be able edit the table too. Currently QGIS doesn't do this locking I guess - I have noticed that multiple users can edit the same PostGIS table simultanously (ie. add/delete/modify points/lines/polygons in a PostGIS table at the same time) which can lead to overlapping and corrupted features. Thinking of it now, maybe it is a defect in QGIS it allows that? In PG experts opinion, could/should a PostGIS editing software lock table for writing to prevent concurent editing? Maciek ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] how to auto GRANT custom ACL on a new table?
Maciej Sieczka wrote: I'd like to prevent concurrent editing of a table. For example PostGIS tables, which are going to be stored in the database too: once a user starts editing the table in QGIS [1], it should be locked (for writing at least), until he turns the edit mode off and commits changes. Only then another user should be able edit the table too. Currently QGIS doesn't do this locking I guess - I have noticed that multiple users can edit the same PostGIS table simultanously (ie. add/delete/modify points/lines/polygons in a PostGIS table at the same time) which can lead to overlapping and corrupted features. Thinking of it now, maybe it is a defect in QGIS it allows that? In PG experts opinion, could/should a PostGIS editing software lock table for writing to prevent concurent editing? The application should either: 1. Take an advisory lock (see the functions/admin functions chapter) so that it can use another table to indicate which parts of the GIS are in use. 2. Check to see if the data changed while the user was editing but before committing (known as optimistic locking). Then give the user the option to overwrite/rollback. A last resort would be locking rows or the whole table, since a user might click edit then go to lunch. Certainly doing nothing isn't much use if you have multiple users editing. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] how to auto GRANT custom ACL on a new table?
Richard Huxton pisze: Maciej Sieczka wrote: In PG experts opinion, could/should a PostGIS editing software lock table for writing to prevent concurent editing? The application should either: 1. Take an advisory lock (see the functions/admin functions chapter) so that it can use another table to indicate which parts of the GIS are in use. 2. Check to see if the data changed while the user was editing but before committing (known as optimistic locking). Then give the user the option to overwrite/rollback. A last resort would be locking rows or the whole table, since a user might click edit then go to lunch. Certainly doing nothing isn't much use if you have multiple users editing. Thanks Richard! I've forwarded your remarks to QGIS devs [1]. Maciek [1]http://www.nabble.com/forum/ViewPost.jtp?post=15699694framed=y ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match