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]

 



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

[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