Thread: Permissions with multiple groups...
I'm receiving permissions denied message when I think I shouldn't.
I have three users cib_unauth, cib_auth, and cib_admin.
I have two groups cib_all and cib_trusted. The first group contains
all the users, the second group contains the latter two.
I'll grant permissions like
REVOKE ALL ON sometable FROM PUBLIC;
GRANT SELECT ON sometable TO GROUP cib_all;
GRANT INSERT ON sometable TO GROUP cib_trusted;
GRANT UPDATE,DELETE on sometable TO cib_admin;
However I'm getting permission denied messages on sometable when I'm issueing
selects as cib_admin.
\dp sometable
sometable | {"=","nielsene=arwR","cib_admin=w","group cib_all="r","group cib_trusted=a"}
Connecting as cib_auth has the same problem, but connecting as cib_unauth
works.
I haven't found anything in the documentation about problems with a user being
a member of two groups. However, I suspect I might be slightly abusing
user groups?
Eric
Eric D Nielsen <nielsene@MIT.EDU> writes:
> I'm receiving permissions denied message when I think I shouldn't.
I tried to duplicate your problem and couldn't:
z=# create user cib_admin;
CREATE USER
z=# create group cib_all user cib_admin;
CREATE GROUP
z=# create group cib_trusted user cib_admin;
CREATE GROUP
z=# create table sometable(f1 int);
CREATE TABLE
z=# REVOKE ALL ON sometable FROM PUBLIC;
REVOKE
z=# GRANT SELECT ON sometable TO GROUP cib_all;
GRANT
z=# GRANT INSERT ON sometable TO GROUP cib_trusted;
GRANT
z=# GRANT UPDATE,DELETE on sometable TO cib_admin;
GRANT
z=# \c - cib_admin
You are now connected as new user cib_admin.
z=> select * from sometable;
f1
----
(0 rows)
z=> insert into sometable values(1);
INSERT 281986 1
z=> update sometable set f1=0;
UPDATE 1
z=> delete from sometable;
DELETE 1
Could you provide an exact test case? Also, which PG version are you
running? (I'm testing CVS tip = 7.3beta1+)
regards, tom lane
Version 7.1.3 (release tarball) on development machine -- Error
7.2.1 on production machine (7.2.1-2 Debian) -- Works
I can't easily upgrade the development machine in the near future, but this
at least gives more ammunition. If you know of any good workaround besides
just GRANT ALL on <each_table> TO test_cib_admin, (which works) I'ld appreciate
hearing about it.
Script:
CREATE USER test_cib_unauth;
CREATE USER test_cib_auth;
CREATE USER test_cib_admin;
CREATE GROUP test_cib_all;
CREATE GROUP test_cib_trusted;
ALTER GROUP test_cib_all ADD USER test_cib_unauth, test_cib_auth, test_cib_admin;
ALTER GROUP test_cib_trusted ADD USER test_cib_auth, test_cib_admin;
CREATE TABLE users (
userid SERIAL PRIMARY KEY,
statusid INT,
siteadmin BOOLEAN,
username TEXT,
hashed TEXT,
email TEXT);
REVOKE ALL PRIVILEGES ON users FROM PUBLIC;
GRANT SELECT, INSERT ON users TO GROUP test_cib_all;
GRANT UPDATE ON users_userid_seq TO GROUP test_cib_all;
GRANT UPDATE ON users TO GROUP test_cib_trusted;
GRANT DELETE ON users TO test_cib_admin;
\c - test_cib_admin
SELECT * FROM users;
--- END SCRIPT ---
Output on 7.1.3.
CREATE USER
CREATE USER
CREATE USER
CREATE GROUP
CREATE GROUP
ALTER GROUP
ALTER GROUP
NOTICE: CREATE TABLE will create implicit sequence ;user_userid_seq' for SERIAL column 'users.userid'
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'users_pkey' for table 'users'
CREATE
CHANGE
CHANGE
CHANGE
CHANGE
CHANGE
You are now connected as new user test_cib_admin.
ERROR: users: Permission Denied.
Eric
Eric D Nielsen <nielsene@MIT.EDU> writes:
> Version 7.1.3 (release tarball) on development machine -- Error
> 7.2.1 on production machine (7.2.1-2 Debian) -- Works
Hm. Evidently you are dealing with a bug that we fixed in 7.2.
> I can't easily upgrade the development machine in the near future,
Sorry to hear that; because even if someone were to go to the trouble
of extracting a back-patchable fix from 7.2, you presumably couldn't
update your machine with the fix.
I recommend finding the necessary political leverage to cause an update.
Perhaps reading the 7.2 release history would help light a fire under
your sysadmin. We fix a *lot* of bugs in every release; this one
is honestly not the worst bug you will find in 7.1.
regards, tom lane
nielsene@MIT.EDU wrote: > Version 7.1.3 (release tarball) on development machine -- Error > 7.2.1 on production machine (7.2.1-2 Debian) -- Works > > I can't easily upgrade the development machine in the near future, but this > at least gives more ammunition. If you know of any good workaround besides > just GRANT ALL on <each_table> TO test_cib_admin, (which works) I'ld appreciate > hearing about it. > ... I haven't seen this written up, and can't find it described in HISTORY or other documentation, so here is my more-than-you-want-to-know explanation of what changed, and a possibly easier workaround. PostgreSQL <= 7.1.3 checked rights like this: If rights are granted to your user name, you get those rights and only those rights. Else, if the right you are trying to use is granted to any group you belong to, access is allowed. Else, if the right you are trying to use is granted to PUBLIC, access is allowed. Else, access is denied. PostgreSQL >= 7.2 checks rights like this: If the right you are trying to use is granted to PUBLIC, access is allowed. Else, if the right you are trying to use is granted to your user name, access is allowed. Else, if the right you are trying to use is granted to any group you belong to, access is allowed. Else, access is denied. The point is that before 7.2, a user-specific GRANT could reduce your rights. Starting with 7.2, you effectively get the union of all rights assigned to PUBLIC, your user name, and all groups you belong to, so a user-specific GRANT cannot reduce your rights at 7.2. This explains the behavior you are seeing. User test_cib_admin is granted DELETE and not SELECT rights, but belongs to group test_cib_all which does have SELECT rights. Under 7.1 you are denied SELECT; under 7.2 you are allowed. To fix it under 7.1, you can grant SELECT rights to the user, as you said in your message. Or, possibly better, you can make a new group just for this user test_cib_admin, and grant the special rights you want them to have (DELETE) to the group instead of the user. Make sure there is no user-specific access control list entry for the user. I haven't tried it, but "I see no reason why this shouldn't work" (my favorite tech support quote).
ljb <lbayuk@mindspring.com> writes:
> The point is that before 7.2, a user-specific GRANT could reduce your
> rights.
I don't believe a word of this ... AFAIR, it's always been a
union-of-rights approach. If it was ever different, the change
was certainly well before 7.1.
regards, tom lane
tgl@sss.pgh.pa.us wrote:
> ljb <lbayuk@mindspring.com> writes:
>> The point is that before 7.2, a user-specific GRANT could reduce your
>> rights.
>
> I don't believe a word of this ... AFAIR, it's always been a
> union-of-rights approach. If it was ever different, the change
> was certainly well before 7.1.
Here is the relevant code difference for aclcheck() in 7.1.3 vs 7.2.1.
Look at the difference in return when the user ID matches.
Check it yourself if you still don't believe me.
--- postgresql-7.1.3/src/backend/catalog/aclchk.c 2001-03-21 22:59:18.000000000 -0500
+++ postgresql-7.2.1/src/backend/catalog/aclchk.c 2001-11-05 12:46:24.000000000 -0500
...
case ACL_IDTYPE_UID:
- /* Look for exact match to user */
+ /* See if permission is granted directly to user */
for (i = 1, aip = aidat + 1; /* skip world entry */
i < num && aip->ai_idtype == ACL_IDTYPE_UID;
++i, ++aip)
{
if (aip->ai_id == id)
{
-#ifdef ACLDEBUG_TRACE
+#ifdef ACLDEBUG
elog(DEBUG, "aclcheck: found user %u/%d",
aip->ai_id, aip->ai_mode);
#endif
- return (aip->ai_mode & mode) ? ACLCHECK_OK : ACLCHECK_NO_PRIV;
+ if (aip->ai_mode & mode)
+ return ACLCHECK_OK;
}
}
ljb <lbayuk@mindspring.com> writes:
> tgl@sss.pgh.pa.us wrote:
>> I don't believe a word of this ... AFAIR, it's always been a
>> union-of-rights approach. If it was ever different, the change
>> was certainly well before 7.1.
> Here is the relevant code difference for aclcheck() in 7.1.3 vs 7.2.1.
> Look at the difference in return when the user ID matches.
You're right, the fix was post-7.1. My apologies.
regards, tom lane