Views permessions
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
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
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
----- 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