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
