Search Postgresql Archives

Re: how to auto GRANT custom ACL on a new table?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux