Re: 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