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

Hello,

I have asked the question on novice ML but it seems there is not much
traffic there. I hope nobody minds I'm re-asking here, on a much more
active list. The problem is as follows:

There are 2 role groups in my cluster: "editors" and "viewers". In each
group there are several users.

In the DB, members of "editors" are allowed to create new tables in one
schema. My problem is that only the very user who created the table is
allowed to view and edit it, by default.

I need to modify this default PostgreSQL's behaviour, so that the ACL on
a new table in this schema is set to SELECT, INSERT, UPDATE, DELETE,
REFERENCES for "editors", and SELECT for "viewers", without having to
manually GRANT rights each time a new table is created. I can't control
this setting from the client software as these are various programs, and
even if I could it'd be still better anyway to have it controlled in one
single place on the server side. But how?

 From reading so far I *suppose* I should create a function which calls
an appropriate GRANT, and trigger it when a new record is added to
"pg_class". Is this doable?

If the function+trigger approach is optimal, can Anybody share a working
example? I have a muddy idea it would be something like this pseudo-code:

CREATE TRIGGER trig_acl
   AFTER INSERT
   ON pg_catalog.pg_class FOR EACH ROW
   EXECUTE PROCEDURE fnct_acl();

CREATE FUNCTION fnct_acl () RETURNS opaque AS '
   DECLARE
     table_name TEXT #how do I fetch the table name???
   BEGIN
     GRANT ALL ON myschema.table_name TO editors;
   END;
' LANGUAGE 'plpgsql';

What should I modify to make it real code?


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?

Thank you for any pointers!

Regards,
Maciek

P.S.
I searched the archives and only found this [1] thread related, but it
does not provide an obvious (for me) solution.

[1]http://www.nabble.com/grant-select-on-all-tables-to1029307.html#a1029307

Maciej Sieczka wrote:
> I need to modify this default PostgreSQL's behaviour, so that the ACL on
> a new table in this schema is set to SELECT, INSERT, UPDATE, DELETE,
> REFERENCES for "editors", and SELECT for "viewers", without having to
> manually GRANT rights each time a new table is created. I can't control
> this setting from the client software as these are various programs, and
> even if I could it'd be still better anyway to have it controlled in one
> single place on the server side. But how?
>
>  From reading so far I *suppose* I should create a function which calls
> an appropriate GRANT, and trigger it when a new record is added to
> "pg_class". Is this doable?

Afraid not. You can't add a trigger to a system table.

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.

> 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.

--
   Richard Huxton
   Archonet Ltd

Richard Huxton pisze:
> Maciej Sieczka wrote:
>> I need to modify this default PostgreSQL's behaviour, so that the ACL on
>> a new table in this schema is set to SELECT, INSERT, UPDATE, DELETE,
>> REFERENCES for "editors", and SELECT for "viewers", without having to
>> manually GRANT rights each time a new table is created. I can't
>> control this setting from the client software as these are various
>> programs, and even if I could it'd be still better anyway to have it
>> controlled in one single place on the server side. But how?
>>
>>  From reading so far I *suppose* I should create a function which calls
>> an appropriate GRANT, and trigger it when a new record is added to
>> "pg_class". Is this doable?

> 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?

>> 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?

Maciek

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

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

Maciej Sieczka wrote:
>
> 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?

The application should either:
1. Take an advisory lock (see the functions/admin functions chapter) so
that it can use another table to indicate which parts of the GIS are in use.
2. Check to see if the data changed while the user was editing but
before committing (known as "optimistic locking"). Then give the user
the option to overwrite/rollback.

A last resort would be locking rows or the whole table, since a user
might click "edit" then go to lunch.

Certainly doing nothing isn't much use if you have multiple users editing.

--
   Richard Huxton
   Archonet Ltd

Richard Huxton pisze:
> Maciej Sieczka wrote:

>> In PG experts
>> opinion, could/should a PostGIS editing software lock table for
>> writing to prevent concurent editing?

> The application should either:
> 1. Take an advisory lock (see the functions/admin functions chapter) so
> that it can use another table to indicate which parts of the GIS are in
> use.
> 2. Check to see if the data changed while the user was editing but
> before committing (known as "optimistic locking"). Then give the user
> the option to overwrite/rollback.
>
> A last resort would be locking rows or the whole table, since a user
> might click "edit" then go to lunch.
>
> Certainly doing nothing isn't much use if you have multiple users editing.

Thanks Richard!

I've forwarded your remarks to QGIS devs [1].

Maciek


[1]http://www.nabble.com/forum/ViewPost.jtp?post=15699694&framed=y

Maciej Sieczka pisze:
> Richard Huxton pisze:

>> 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!

Dear All

Said too soon, not carefully reading Richard's words first: "probably
modify the code" - I'm no programmer (only shell, awk and some python
savvy).

Isn't there a ready to use sort of addon for PgSQL which could intercept
the CREATE TABLE that eg. Base (or any other client) issues, appdend
GRANT to it and forward such a modified instruction to the server?

I looked a lot in list archives, PgFoundry, Google and can't find a
tool. Your hints are more than welcome!

Best
Maciek

Maciej Sieczka wrote:
>
> Isn't there a ready to use sort of addon for PgSQL which could intercept
> the CREATE TABLE that eg. Base (or any other client) issues, appdend
> GRANT to it and forward such a modified instruction to the server?
>
> I looked a lot in list archives, PgFoundry, Google and can't find a
> tool. Your hints are more than welcome!

I'm afraid I don't know of anything. The problem is that the
requirements for this sort of thing are different for every user.
Generally it's because you have a client application you don't control
and want to emulate another backend. That's usually not a complicated job.
--
   Richard Huxton
   Archonet Ltd