Thread: CREATE RULE problem/question requesting workaround
Hi all;
I am using PostgreSQL 7.4 on RedHat 9, though I don't think that is important
to this problem.
I am attempting to write a rule that will check to see whether certain
conditions are right for a SELECT query and abort the query of those
connections are not right. In this case, I am trying to do additional
permissions enforcement. I have written a function to do the checking,
but when I create the rule, I get an error. So here is the example
(the actual user-defined function is not important-- if it just returns true,
I get the same error):
CREATE OR REPLACE FUNCTION sh_fail_nonsu_admin(BOOL)
RETURNS BOOL
AS'
DECLARE
allow_admin ALIAS FOR $1;
is_admin BOOL;
BEGIN
IF (SELECT sh_is_superuser()) THEN
RETURN TRUE;
ELSEIF allow_admin == TRUE THEN
SELECT INTO is_admin admin FROM owners
WHERE login = sh_get_eff_user();
IF is_admin == TRUE THEN
RETURN TRUE;
END IF;
END IF;
RAISE EXCEPTION ''PERMISSION DENIED'';
RETURN FALSE;
END;
' LANGUAGE PLPGSQL;
Basically the purpose of the function is to check whether the user is a
superuser of the application (and thus able to bypass my shared-hosting
environment security system and rely on database perms instead. As you
can see it merely checks a few options and then returns true or raises
an exception.
Now, the problem is that when I try to create the rule (something like)
CREATE OR REPLACE RULE hermes_sh_perm
AS ON SELECT TO hermes_shared_hosting.auth_shadow
DO select sh_fail_nonsu_admin(FALSE)
I get the following error:
ERROR: rules on SELECT must have action INSTEAD SELECT
If I try to write it as an unqualified join:
CREATE OR REPLACE RULE hermes_sh_perm
AS ON SELECT TO hermes_shared_hosting.auth_shadow
DO INSTEAD SELECT * from hermes_shared_hosting.auth_shadow,
sh_fail_nonsu_admin(FALSE)
I get the following error:
ERROR: SELECT rule's target list has too many entries
I have tried to rewrite the rule as a CASE statement as well, but again
I get the same problem. Is there any way to try to enforce a rewrite
rule in such a away as to do the select query as normal, but if
necessary raise an exception (to abort the select)?
Of course triggers don't work on select, so I am wondering what else can be
done.
Best Wishes,
Chris Travers
Chris Travers <chris@travelamericas.com> writes:
> Is there any way to try to enforce a rewrite
> rule in such a away as to do the select query as normal, but if
> necessary raise an exception (to abort the select)?
No.
You need to rethink your permissions design. One idea that you *can*
make work is to have a view that everyone can read, but are only able to
see selected rows in. pg_stats works that way, and I think there are
examples in the information schema as well.
regards, tom lane
On Mon, 2003-12-08 at 00:46, Tom Lane wrote: > Chris Travers <chris@travelamericas.com> writes: > > Is there any way to try to enforce a rewrite > > rule in such a away as to do the select query as normal, but if > > necessary raise an exception (to abort the select)? > > No. > > You need to rethink your permissions design. One idea that you *can* > make work is to have a view that everyone can read, but are only able to > see selected rows in. pg_stats works that way, and I think there are > examples in the information schema as well. > Not that this will help me at the moment, but any plans to remove the requirement that SELECT rules be unconditional, and use DO INSTEAD? I can see where lifting this restriction could cause some headaches for those implementing these sorts of rules (infinite recursion, etc), but it would be really nice to have for these types of circumstances (restricting the tables that must allow everyone read access to 2 or so). The way I would like to this implemented would be: 1) For all rules but DO INSTEAD on a select statement, the query results are discarded. This could allow utility functions to be "triggered" by select statements. 2) A different option would be to allow statement-level triggers for select queries. (We don't need tuple-level triggers, which would probably be overkill). The reason I am trying to do this is because I am trying to create a simple, lightweight layer to port a program designed for dedicated hosting (and database permissions) into a shared hosting environment. I can get all permissions to be enforced aside from select permissions. Adding every table as a view is not going to be likely to be feasible since it will involve a major maintenance nightmare. Depending on requirements, we may look at allowing most of the database to be readable to all users, but this is not ideal and I am looking for maintainable workarounds. I do thank you for your suggestion and we may use it for one or 2 tables. Best Wishes, Chris Travers
Chris Travers <chris@travelamericas.com> writes:
> Adding every table as a view is not going to be likely to be feasible
> since it will involve a major maintenance nightmare. Depending on
> requirements, we may look at allowing most of the database to be
> readable to all users, but this is not ideal and I am looking for
> maintainable workarounds.
Is it really not possible to express what you need with plain old SQL
permissions? It seems like you are going out of your way to avoid the
obvious solution.
regards, tom lane
On Mon, 2003-12-08 at 01:39, Tom Lane wrote:
>
> Is it really not possible to express what you need with plain old SQL
> permissions? It seems like you are going out of your way to avoid the
> obvious solution.
Thanks-- as you can see, that is what the application was *designed* to
use. The problem is porting the application which was designed under
the assumption that every user would have a database account into an
environment where that is not possible. In the new environment, only a
few user accounts will exist and these will be used by many users.
Therefore I can only assume one user account for the entire application.
BTW, I have figured out the last of my issues, and I now have a working
solution after a EUREKA moment today... FWIW, here is the solution. In
writing the utilities to make this solution work, I have composed a
number of other helpful utilities which others may find of use.
Here is the solution:
CREATE SCHEMA shadow;
SELECT move_relation('my_table', 'public', 'shadow');
-- move_relation is a custom function I wrote
CREATE VIEW my_table AS
SELECT *, oid FROM shadow.my_table
WHERE (SELECT check_func());
-- I then programatically add insert, update, and delete rules
-- check_func() returns BOOL, controlling whether the query returns any
-- rows. It can also raise an exception, causing the transaction to
-- abort.
I finally (a few moments ago) finally solved the last problem I was
having which was a permissions issue. Now it works well and has no
known issues. Not quite as elegant as I had hoped, but far more elegant
than I had feared.
Because this package also by necessity also contains a number of other
useful functions I may send it to the HACKERS list.
Best Wishes,
Chris Travers
Chris Travers <chris@travelamericas.com> writes:
> On Mon, 2003-12-08 at 01:39, Tom Lane wrote:
>> Is it really not possible to express what you need with plain old SQL
>> permissions? It seems like you are going out of your way to avoid the
>> obvious solution.
> Thanks-- as you can see, that is what the application was *designed* to
> use. The problem is porting the application which was designed under
> the assumption that every user would have a database account into an
> environment where that is not possible.
I guess I'm questioning the assumption that that's not possible.
Having many users share an account when you want to enforce different
permissions for each user seems like a fundamentally bad idea. Postgres
users are sufficiently lightweight entities that I think you could and
should just make more of 'em.
regards, tom lane
On Tue, 2003-12-09 at 00:54, Tom Lane wrote:
> Chris Travers <chris@travelamericas.com> writes:
>
> I guess I'm questioning the assumption that that's not possible.
> Having many users share an account when you want to enforce different
> permissions for each user seems like a fundamentally bad idea. Postgres
> users are sufficiently lightweight entities that I think you could and
> should just make more of 'em.
>
> regards, tom lane
Moving the system onto a server operated by a shared hosting company--
they have limits to the number of users I can use. This is an attempt
to circumvent that limit and allow for the app to be aware of a much
larger number. In most other web apps, they simply use a user table and
do no permissions enforcement, delegating that to the web app.
However, I don't want to go this route because the web app is more
exposed (security-wise) than the database server. It would also require
a huge degree of retrofitting into the app. I have actually solved all
the major technical hurdles and am in the final stages of assembling my
solution. The solution provides for:
1: A table of database user accounts which are allowed to bypass these
triggers (f. ex. for backup/restore of the database).
2: A customizable view permission system which can be used to block
rows as well as queries.
3: A number of utility functions which form the building block of these
allow you to:
* Request a table of the column names in the table.
* Move a table from one schema to another
* Check to see if a relation is really a table (and/or a view).
The permissions are checked against the same catalog which is used to
store permissions metadata in the standard version of the software
(permissions are assigned in "levels" to "modules", and each "module"
usually contains many tables). In the standard version, these
permissions are translated into database permissions and GRANTED as
appropriate. In the shared hosting version, the permission triggers
simply check this table, so it is not that different.
Best Wishes,
Chris Travers