Thread: Why grantor is owner in this case?
Hi guys,
I have a question about the grantor. Why the grantor is owner in the following case ? I think it should be postgres(dba).
postgres=# create user u1;
CREATE ROLE
postgres=# create user u2;
CREATE ROLE
postgres=# set session authorization u1;
SET
postgres=> create table u1_tb1(a int);
CREATE TABLE
postgres=> reset session authorization;
RESET
postgres=# grant update(a) on u1_tb1 to u2;
GRANT
postgres=# select attacl from pg_attribute where attname='a';
attacl
-----------
CREATE ROLE
postgres=# create user u2;
CREATE ROLE
postgres=# set session authorization u1;
SET
postgres=> create table u1_tb1(a int);
CREATE TABLE
postgres=> reset session authorization;
RESET
postgres=# grant update(a) on u1_tb1 to u2;
GRANT
postgres=# select attacl from pg_attribute where attname='a';
attacl
-----------
{u2=w/u1}
(4 rows)
(4 rows)
From attacl "u2=w/u1", we can see the grantor is u1, but in fact the grantor is postgres, the dba of database.
Does anyone know why ? Or is that a bug?
--Dongni
"donniehan" <donniehan@126.com> writes:
> I have a question about the grantor. Why the grantor is owner in the following case ? I think it should be
postgres(dba).
Grants done by a superuser on an object he doesn't own are treated as
being done by the object owner instead. Otherwise you end up with
grants that don't have a clear chain of traceability to the owner,
which causes all sorts of un-fun issues for REVOKE. (I'm too lazy
to come up with the details right now, but if you care you can look
back in the pgsql-hackers archives to find the discussions where this
behavior was agreed on.)
regards, tom lane
Hi Tom,
Thanks for your information. One more question, if i wanna get the accurate information about grantor(Sometimes the application just needs the information), what can i do?
Apparently i cannot get from the acls, so where can i get the info?
-Dongni
"Tom Lane" <tgl@sss.pgh.pa.us> ???? news:4759.1261758025@sss.pgh.pa.us...
> "donniehan" <donniehan@126.com> writes:
>> I have a question about the grantor. Why the grantor is owner in the following case ? I think it should be postgres(dba).
>
> Grants done by a superuser on an object he doesn't own are treated as
> being done by the object owner instead. Otherwise you end up with
> grants that don't have a clear chain of traceability to the owner,
> which causes all sorts of un-fun issues for REVOKE. (I'm too lazy
> to come up with the details right now, but if you care you can look
> back in the pgsql-hackers archives to find the discussions where this
> behavior was agreed on.)
>
> regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Dear Tom,
I'm sorry to bother you. i really care about this behavior, but i couldn't find the discussions you mentioned in pgsql-hackers archives.
Would you please tell me more about the discussions(about date? the related issue?), so that i can search it and find it more easily?
Thank you very much !
Regards
-Dongni
"Tom Lane" <tgl@sss.pgh.pa.us> ???? news:4759.1261758025@sss.pgh.pa.us...
> "donniehan" <donniehan@126.com> writes:
>> I have a question about the grantor. Why the grantor is owner in the following case ? I think it should be postgres(dba).
>
> Grants done by a superuser on an object he doesn't own are treated as
> being done by the object owner instead. Otherwise you end up with
> grants that don't have a clear chain of traceability to the owner,
> which causes all sorts of un-fun issues for REVOKE. (I'm too lazy
> to come up with the details right now, but if you care you can look
> back in the pgsql-hackers archives to find the discussions where this
> behavior was agreed on.)
>
> regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
donniehan wrote: > Dear Tom, > > I'm sorry to bother you. i really care about this behavior, but i couldn't find the discussions you mentioned in pgsql-hackersarchives. > Would you please tell me more about the discussions(about date? the related issue?), so that i can search it and find itmore easily? Maybe he's referring to this discussion: http://archives.postgresql.org/message-id/1176775180.4152.97.camel%40dogma.v10.wvs Note that on archives.pg.org it is truncated and continues here: http://archives.postgresql.org/message-id/20070503225044.GK4218@alvh.no-ip.org -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes: > donniehan wrote: >> I'm sorry to bother you. i really care about this behavior, but i couldn't find the discussions you mentioned in pgsql-hackersarchives. >> Would you please tell me more about the discussions(about date? the related issue?), so that i can search it and findit more easily? > Maybe he's referring to this discussion: > http://archives.postgresql.org/message-id/1176775180.4152.97.camel%40dogma.v10.wvs No, it's a lot older than that. See http://archives.postgresql.org/pgsql-hackers/2003-10/msg01497.php http://archives.postgresql.org/pgsql-committers/2003-10/msg00305.php The original 7.4-devel behavior made it effectively impossible for a superuser to *revoke* privileges, which is certainly not acceptable in practice. Looking at the CVS history of aclchk.c, I notice that we later installed a similar provision with respect to roles: grants/revokes are done as the role that owns the object, not as the role member that is actually issuing the command. Otherwise other role members can't adjust the privileges. This comes down to the fact that privileges granted on the same object by two different roles are distinct, and you can only revoke the ones you granted. Which AFAICT is required behavior per SQL spec. regards, tom lane
On Dec 27, 2009, at 6:59 PM, donniehan wrote: > Hi Tom, > > Thanks for your information. One more question, if i wanna get the accurate information about grantor(Sometimes the applicationjust needs the information), what can i do? > Apparently i cannot get from the acls, so where can i get the info? > > -Dongni First, please, trim posts and don't top post, it makes it confusing for others to follow along with what's currently beingdiscussed in a thread when you don't do either. As to how you can determine who *actually* made a particular grant, there isn't any built-in way. What you'd need to dois wrap your GRANT statements in functions that log what was done and by whom to a table and then consult that log tablewhen you need that info. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k