Postgres Pro
Downloads
Home   >   mailing lists

Thread: Views permessions

Views permessions

From
salah jubeh
Date:
Hello Guys,

There is a problem confusing me. I have two views 'VIEW1' and 'VIEW2' 


1. VIEW2 depends on VIEW1
2. VIEW2 and VIEW1 have the exact permissions
3. I can execute  SELECT * from VIEW1 ; without problem
4. When I execute  SELECT * from VIEW2; I get 


ERROR:  permission denied for relation VIEW1

********** Error **********

ERROR: permission denied for relation VIEW1
SQL state: 42501

5. The owner of the views is not me, But I am a super user
6. The Database version is 8.3

What is wrong here and how can I trace the problem, I checked the views permissions many times (i did that manually based on the view def.). Also, Which system catalog maps the permissions and the roles 

Regards

Re: Views permessions

From
Tom Lane
Date:
salah jubeh <s_jubeh@yahoo.com> writes:
> There is a problem confusing me. I have two views 'VIEW1' and 'VIEW2'

> 1. VIEW2 depends on VIEW1
> 2. VIEW2 and VIEW1 have the exact permissions
> 3. I can execute  SELECT * from VIEW1 ; without problem
> 4. When I execute  SELECT * from VIEW2; I get
> ERROR:  permission denied for relation VIEW1
> 5. The owner of the views is not me, But I am a super user

VIEW2's reference to VIEW1 is checked according to the permissions
granted to the owner of VIEW2.  Whether the ultimate caller is a
superuser doesn't affect this.

            regards, tom lane

Re: Views permessions

From
salah jubeh
Date:
I have found the table where views are roles- permissions are stored and I checked it automatically and still permissions are identical  i.e. the following query returns 0 rows

SELECT grantor, grantee, table_catalog, table_schema, is_grantable, with_hierarchy
FROM information_schema.role_table_grants
WHERE table_name = 'view1'
except
SELECT grantor, grantee, table_catalog, table_schema, is_grantable, with_hierarchy
FROM information_schema.role_table_grants
WHERE table_name = 'view2'
union
SELECT grantor, grantee, table_catalog, table_schema, is_grantable, with_hierarchy
FROM information_schema.role_table_grants
WHERE table_name = 'view2'
except
SELECT grantor, grantee, table_catalog, table_schema, is_grantable, with_hierarchy
FROM information_schema.role_table_grants
WHERE table_name = 'view1';


Do you think there is a bug or something like that...

Regards
 
From: Tom Lane <tgl@sss.pgh.pa.us>
To: salah jubeh <s_jubeh@yahoo.com>
Cc: pgsql <pgsql-general@postgresql.org>
Sent: Friday, May 20, 2011 3:47 PM
Subject: Re: [GENERAL] Views permessions

salah jubeh <s_jubeh@yahoo.com> writes:
> There is a problem confusing me. I have two views 'VIEW1' and 'VIEW2' 

> 1. VIEW2 depends on VIEW1
> 2. VIEW2 and VIEW1 have the exact permissions
> 3. I can execute  SELECT * from VIEW1 ; without problem
> 4. When I execute  SELECT * from VIEW2; I get 
> ERROR:  permission denied for relation VIEW1
> 5. The owner of the views is not me, But I am a super user

VIEW2's reference to VIEW1 is checked according to the permissions
granted to the owner of VIEW2.  Whether the ultimate caller is a
superuser doesn't affect this.

            regards, tom lane


Fw: Views permessions

From
salah jubeh
Date:

----- Forwarded Message -----
From: salah jubeh <s_jubeh@yahoo.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql <pgsql-general@postgresql.org>
Sent: Friday, May 20, 2011 3:54 PM
Subject: Re: [GENERAL] Views permessions

I have found the table where views are roles- permissions are stored and I checked it automatically and still permissions are identical  i.e. the following query returns 0 rows

SELECT grantor, grantee, table_catalog, table_schema, is_grantable, with_hierarchy
FROM information_schema.role_table_grants
WHERE table_name = 'view1'
except
SELECT grantor, grantee, table_catalog, table_schema, is_grantable, with_hierarchy
FROM information_schema.role_table_grants
WHERE table_name = 'view2'
union
SELECT grantor, grantee, table_catalog, table_schema, is_grantable, with_hierarchy
FROM information_schema.role_table_grants
WHERE table_name = 'view2'
except
SELECT grantor, grantee, table_catalog, table_schema, is_grantable, with_hierarchy
FROM information_schema.role_table_grants
WHERE table_name = 'view1';


Do you think there is a bug or something like that...

Regards
 
From: Tom Lane <tgl@sss.pgh.pa.us>
To: salah jubeh <s_jubeh@yahoo.com>
Cc: pgsql <pgsql-general@postgresql.org>
Sent: Friday, May 20, 2011 3:47 PM
Subject: Re: [GENERAL] Views permessions

salah jubeh <s_jubeh@yahoo.com> writes:
> There is a problem confusing me. I have two views 'VIEW1' and 'VIEW2' 

> 1. VIEW2 depends on VIEW1
> 2. VIEW2 and VIEW1 have the exact permissions
> 3. I can execute  SELECT * from VIEW1 ; without problem
> 4. When I execute  SELECT * from VIEW2; I get 
> ERROR:  permission denied for relation VIEW1
> 5. The owner of the views is not me, But I am a super user

VIEW2's reference to VIEW1 is checked according to the permissions
granted to the owner of VIEW2.  Whether the ultimate caller is a
superuser doesn't affect this.

            regards, tom lane