Thread: Permissions Broken in 7.0.x?

Greetings, I am having problem with table permissions and view permissions.

Say I am user bob and I own a database, bobdb.  Let us also assume a user,
tester has been added to the db, via create user...

% psql bobdb
psql => create table test (   name       char(32) not null,   age        int4 );
psql => revoke all on test from tester;
CHANGE
psql => select * from test;
ERROR:  test: Permission denied.

Why would this be so?  I only revoked the user tester. So, I add myself:

Additionally, if I have a trigger on a table which sets the updated_date and
updated_by on any update, and I have a view with a rule which passes any
update to the table (thus calling the trigger), I get a permission denied.
I am supposing that the user updating the view does not have privileges to
the trigger?  How would I grant them privileges to the trigger?

Thank you for your assistance.

--Brian

Brian Powell <brian@owlscreech.com> writes:
> % psql bobdb
> psql => create table test (
>     name       char(32) not null,
>     age        int4
>   );
> psql => revoke all on test from tester;
> CHANGE
> psql => select * from test;
> ERROR:  test: Permission denied.

Yeah, that's a bug --- confusion about the default permissions set.
After doing an explicit GRANT or REVOKE on a table you own, you need to
grant permissions to yourself again; the default assumption that you
have all permissions gets lost :-(.  This is fixed for 7.1.

> Additionally, if I have a trigger on a table which sets the updated_date and
> updated_by on any update, and I have a view with a rule which passes any
> update to the table (thus calling the trigger), I get a permission denied.
> I am supposing that the user updating the view does not have privileges to
> the trigger?  How would I grant them privileges to the trigger?

Don't think there's a way to do that right now.  There's been talk of
making triggers run "setuid" to the creating user, but it's not been
done AFAIR.
        regards, tom lane

Tom,

Thank you for the reply.  I have found another issue that I am curious
about:  I have a table with foreign key constraint to itself (a parent_id
field).  I have a view to the table with permissions for a user to insert
into the view (of course, a rule exists to insert into the real table).  The
problem is that it fails because of the constraint!  Any way around this?
Or, am I sort of stuck granting all of these privileges to the table so that
triggers, constraints, etc. will work?

Thanks,
Brian 

> Brian Powell <brian@owlscreech.com> writes:
>> % psql bobdb
>> psql => create table test (
>>     name       char(32) not null,
>>     age        int4
>>   );
>> psql => revoke all on test from tester;
>> CHANGE
>> psql => select * from test;
>> ERROR:  test: Permission denied.
> 
> Yeah, that's a bug --- confusion about the default permissions set.
> After doing an explicit GRANT or REVOKE on a table you own, you need to
> grant permissions to yourself again; the default assumption that you
> have all permissions gets lost :-(.  This is fixed for 7.1.
> 
>> Additionally, if I have a trigger on a table which sets the updated_date and
>> updated_by on any update, and I have a view with a rule which passes any
>> update to the table (thus calling the trigger), I get a permission denied.
>> I am supposing that the user updating the view does not have privileges to
>> the trigger?  How would I grant them privileges to the trigger?
> 
> Don't think there's a way to do that right now.  There's been talk of
> making triggers run "setuid" to the creating user, but it's not been
> done AFAIR.


--Brian