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
-----------
{u2=w/u1}
(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...
Show quoted text
"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...
Show quoted text
"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-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?
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-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?
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 application just 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 being discussed 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 do is wrap your GRANT statements in functions that log what was done and by whom to a table and then consult that log table when you need that info.
Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k