Thread: Granting of permissions on tables

I have PostgreSQL v7.02 running on a HP-UX 11.00 box.  I as the owner of
some tables granted permissions to another user.  I executed the following
command from psql

GRANT ALL on <tables here> to <user2>;

after running the command I lost permissions to the tables once I exited
psql.  I had to run psql as the postgres user to correct the situation.

Is this a bug or desired behavior?  I would imagine since I owned the tables
and then granted permissions to another user, I wouldn't lose my
permissions.

Any help or explanation would be appreciated.

Thanks,
Scott Saltsgaver

Saltsgaver, Scott writes:

> GRANT ALL on <tables here> to <user2>;
> 
> after running the command I lost permissions to the tables once I exited
> psql.  I had to run psql as the postgres user to correct the situation.
> 
> Is this a bug or desired behavior?

It's a bug.  Fixed for 7.1.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

"Saltsgaver, Scott" <scottsa@aiinet.com> writes:
> Is this a bug or desired behavior?  I would imagine since I owned the tables
> and then granted permissions to another user, I wouldn't lose my
> permissions.

It's a bug, or at least a misfeature.  As long as you haven't done any
explicit grants or revokes, 7.0 uses an implicit access control list
that grants all privileges to the owner and none to anyone else.
However, the moment you do any explicit grant/revoke, that implicit
ACL entry for the owner isn't used anymore.  You have to explicitly
grant rights to yourself again :-(.

You don't need superuser help to do this, you just have to doGRANT ALL ON table TO yourself
as the table owner.  But it's stupid to have to do that when it's
supposed to be the default condition.  Fixed for 7.1.
        regards, tom lane

After I ran into this condition, the first thing I tried was to grant
permissions back to myself.  PostgreSQL shot me down with a permission
denied error.  So I had to log is as the superuser and then grant
permissions to myself.

Thanks for everyone's help.  So would an exceptable workaround be to grant
permissions to yourself first and then to all other users?

Scott

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, October 05, 2000 5:38 PM
To: Saltsgaver, Scott
Cc: 'pgsql-sql@postgresql.org'
Subject: Re: [SQL] Granting of permissions on tables 


"Saltsgaver, Scott" <scottsa@aiinet.com> writes:
> Is this a bug or desired behavior?  I would imagine since I owned the
tables
> and then granted permissions to another user, I wouldn't lose my
> permissions.

It's a bug, or at least a misfeature.  As long as you haven't done any
explicit grants or revokes, 7.0 uses an implicit access control list
that grants all privileges to the owner and none to anyone else.
However, the moment you do any explicit grant/revoke, that implicit
ACL entry for the owner isn't used anymore.  You have to explicitly
grant rights to yourself again :-(.

You don't need superuser help to do this, you just have to doGRANT ALL ON table TO yourself
as the table owner.  But it's stupid to have to do that when it's
supposed to be the default condition.  Fixed for 7.1.
        regards, tom lane

"Saltsgaver, Scott" <scottsa@aiinet.com> writes:
> After I ran into this condition, the first thing I tried was to grant
> permissions back to myself.  PostgreSQL shot me down with a permission
> denied error.  So I had to log is as the superuser and then grant
> permissions to myself.

Are you sure about that?  What version are you running?  I get

play=> select version();                            version
------------------------------------------------------------------PostgreSQL 7.0.2 on hppa2.0-hp-hpux10.20, compiled by
gcc2.95.2
 
(1 row)

play=> select usename, usesuper from pg_user;usename  | usesuper
----------+----------postgres | ttgl      | ftree     | f
(3 rows)

play=> select current_user;getpgusername
---------------tgl
(1 row)

play=> create table bar (f1 int);
CREATE
play=> select * from bar;f1
----
(0 rows)

play=> grant all on bar to tree;
CHANGE
play=> select * from bar;
ERROR:  bar: Permission denied.
play=> grant all on bar to tgl;
CHANGE
play=> select * from bar;f1
----
(0 rows)

play=>

AFAICT from both experiment and looking at the sources, a table
owner is allowed to change the table's access permissions whether
or not he's currently got any permissions granted to himself;
ie, changing permissions is not a grantable/revokable right,
it's just checked on the basis of who you are.
        regards, tom lane