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
[GENERAL] how to auto GRANT custom ACL on a new table?
Hello, I have asked the question on novice ML but it seems there is not much traffic there. I hope nobody minds I'm re-asking here, on a much more active list. The problem is as follows: There are 2 role groups in my cluster: editors and viewers. In each group there are several users. In the DB, members of editors are allowed to create new tables in one schema. My problem is that only the very user who created the table is allowed to view and edit it, by default. 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? If the function+trigger approach is optimal, can Anybody share a working example? I have a muddy idea it would be something like this pseudo-code: CREATE TRIGGER trig_acl AFTER INSERT ON pg_catalog.pg_class FOR EACH ROW EXECUTE PROCEDURE fnct_acl(); CREATE FUNCTION fnct_acl () RETURNS opaque AS ' DECLARE table_name TEXT #how do I fetch the table name??? BEGIN GRANT ALL ON myschema.table_name TO editors; END; ' LANGUAGE 'plpgsql'; What should I modify to make it real code? 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? Thank you for any pointers! Regards, Maciek P.S. I searched the archives and only found this [1] thread related, but it does not provide an obvious (for me) solution. [1]http://www.nabble.com/grant-select-on-all-tables-to1029307.html#a1029307 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
