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