privileges.out

PostgreSQL7.4.6 for Linux

(1 row)

select has_table_privilege(t2.usesysid,'pg_shadow','update')
from (select usesysid from pg_user where usename = current_user) as t2;
 has_table_privilege 
---------------------
 t
(1 row)

select has_table_privilege(t2.usesysid,'pg_shadow','delete')
from (select usesysid from pg_user where usename = current_user) as t2;
 has_table_privilege 
---------------------
 t
(1 row)

select has_table_privilege(current_user,t1.oid,'rule')
from (select oid from pg_class where relname = 'pg_shadow') as t1;
 has_table_privilege 
---------------------
 t
(1 row)

select has_table_privilege(current_user,t1.oid,'references')
from (select oid from pg_class where relname = 'pg_shadow') as t1;
 has_table_privilege 
---------------------
 t
(1 row)

select has_table_privilege(t2.usesysid,t1.oid,'select')
from (select oid from pg_class where relname = 'pg_shadow') as t1,
  (select usesysid from pg_user where usename = current_user) as t2;
 has_table_privilege 
---------------------
 t
(1 row)

select has_table_privilege(t2.usesysid,t1.oid,'insert')
from (select oid from pg_class where relname = 'pg_shadow') as t1,
  (select usesysid from pg_user where usename = current_user) as t2;
 has_table_privilege 
---------------------
 t
(1 row)

select has_table_privilege('pg_shadow','update');
 has_table_privilege 
---------------------
 t
(1 row)

select has_table_privilege('pg_shadow','delete');
 has_table_privilege 
---------------------
 t
(1 row)

select has_table_privilege(t1.oid,'select')
from (select oid from pg_class where relname = 'pg_shadow') as t1;
 has_table_privilege 
---------------------
 t
(1 row)

select has_table_privilege(t1.oid,'trigger')
from (select oid from pg_class where relname = 'pg_shadow') as t1;
 has_table_privilege 
---------------------
 t
(1 row)

-- non-superuser
SET SESSION AUTHORIZATION regressuser3;
select has_table_privilege(current_user,'pg_class','select');
 has_table_privilege 
---------------------
 t
(1 row)

select has_table_privilege(current_user,'pg_class','insert');
 has_table_privilege 
---------------------
 f
(1 row)

select has_table_privilege(t2.usesysid,'pg_class','update')
from (select usesysid from pg_user where usename = current_user) as t2;
 has_table_privilege 
---------------------
 f
(1 row)

select has_table_privilege(t2.usesysid,'pg_class','delete')
from (select usesysid from pg_user where usename = current_user) as t2;
 has_table_privilege 
---------------------
 f
(1 row)

select has_table_privilege(current_user,t1.oid,'rule')
from (select oid from pg_class where relname = 'pg_class') as t1;
 has_table_privilege 
---------------------
 f
(1 row)

select has_table_privilege(current_user,t1.oid,'references')
from (select oid from pg_class where relname = 'pg_class') as t1;
 has_table_privilege 
---------------------
 f
(1 row)

select has_table_privilege(t2.usesysid,t1.oid,'select')
from (select oid from pg_class where relname = 'pg_class') as t1,
  (select usesysid from pg_user where usename = current_user) as t2;
 has_table_privilege 
---------------------
 t
(1 row)

select has_table_privilege(t2.usesysid,t1.oid,'insert')
from (select oid from pg_class where relname = 'pg_class') as t1,
  (select usesysid from pg_user where usename = current_user) as t2;
 has_table_privilege 
---------------------
 f
(1 row)

select has_table_privilege('pg_class','update');
 has_table_privilege 
---------------------
 f
(1 row)

select has_table_privilege('pg_class','delete');
 has_table_privilege 
---------------------
 f
(1 row)

select has_table_privilege(t1.oid,'select')
from (select oid from pg_class where relname = 'pg_class') as t1;
 has_table_privilege 
---------------------
 t
(1 row)

select has_table_privilege(t1.oid,'trigger')
from (select oid from pg_class where relname = 'pg_class') as t1;
 has_table_privilege 
---------------------
 f
(1 row)

select has_table_privilege(current_user,'atest1','select');
 has_table_privilege 
---------------------
 t
(1 row)

select has_table_privilege(current_user,'atest1','insert');
 has_table_privilege 
---------------------
 f
(1 row)

select has_table_privilege(t2.usesysid,'atest1','update')
from (select usesysid from pg_user where usename = current_user) as t2;
 has_table_privilege 
---------------------
 f
(1 row)

select has_table_privilege(t2.usesysid,'atest1','delete')
from (select usesysid from pg_user where usename = current_user) as t2;
 has_table_privilege 
---------------------
 f
(1 row)

select has_table_privilege(current_user,t1.oid,'rule')
from (select oid from pg_class where relname = 'atest1') as t1;
 has_table_privilege 
---------------------
 f
(1 row)

select has_table_privilege(current_user,t1.oid,'references')
from (select oid from pg_class where relname = 'atest1') as t1;
 has_table_privilege 
---------------------
 f
(1 row)

select has_table_privilege(t2.usesysid,t1.oid,'select')
from (select oid from pg_class where relname = 'atest1') as t1,
  (select usesysid from pg_user where usename = current_user) as t2;
 has_table_privilege 
---------------------
 t
(1 row)

select has_table_privilege(t2.usesysid,t1.oid,'insert')
from (select oid from pg_class where relname = 'atest1') as t1,
  (select usesysid from pg_user where usename = current_user) as t2;
 has_table_privilege 
---------------------
 f
(1 row)

select has_table_privilege('atest1','update');
 has_table_privilege 
---------------------
 f
(1 row)

select has_table_privilege('atest1','delete');
 has_table_privilege 
---------------------
 f
(1 row)

select has_table_privilege(t1.oid,'select')
from (select oid from pg_class where relname = 'atest1') as t1;
 has_table_privilege 
---------------------
 t
(1 row)

select has_table_privilege(t1.oid,'trigger')
from (select oid from pg_class where relname = 'atest1') as t1;
 has_table_privilege 
---------------------
 f
(1 row)

-- Grant options
SET SESSION AUTHORIZATION regressuser1;
CREATE TABLE atest4 (a int);
GRANT SELECT ON atest4 TO regressuser2 WITH GRANT OPTION;
GRANT UPDATE ON atest4 TO regressuser2;
GRANT SELECT ON atest4 TO GROUP regressgroup1 WITH GRANT OPTION; -- fail
ERROR:  grant options can only be granted to individual users
SET SESSION AUTHORIZATION regressuser2;
GRANT SELECT ON atest4 TO regressuser3;
GRANT UPDATE ON atest4 TO regressuser3; -- fail
ERROR:  permission denied for relation atest4
SET SESSION AUTHORIZATION regressuser1;
REVOKE SELECT ON atest4 FROM regressuser3; -- does nothing
SELECT has_table_privilege('regressuser3', 'atest4', 'SELECT'); -- true
 has_table_privilege 
---------------------
 t
(1 row)

REVOKE SELECT ON atest4 FROM regressuser2; -- fail
ERROR:  dependent privileges exist
HINT:  Use CASCADE to revoke them too.
REVOKE GRANT OPTION FOR SELECT ON atest4 FROM regressuser2 CASCADE; -- ok
SELECT has_table_privilege('regressuser2', 'atest4', 'SELECT'); -- true
 has_table_privilege 
---------------------
 t
(1 row)

SELECT has_table_privilege('regressuser3', 'atest4', 'SELECT'); -- false
 has_table_privilege 
---------------------
 f
(1 row)

SELECT has_table_privilege('regressuser1', 'atest4', 'SELECT WITH GRANT OPTION'); -- true
 has_table_privilege 
---------------------
 t
(1 row)

-- clean up
\c regression
DROP FUNCTION testfunc2(int);
DROP FUNCTION testfunc4(boolean);
DROP VIEW atestv1;
DROP VIEW atestv2;
-- this should cascade to drop atestv4
DROP VIEW atestv3 CASCADE;
NOTICE:  drop cascades to rule _RETURN on view atestv4
NOTICE:  drop cascades to view atestv4
-- this should complain "does not exist"
DROP VIEW atestv4;
ERROR:  view "atestv4" does not exist
DROP TABLE atest1;
DROP TABLE atest2;
DROP TABLE atest3;
DROP TABLE atest4;
DROP GROUP regressgroup1;
DROP GROUP regressgroup2;
DROP USER regressuser1;
DROP USER regressuser2;
DROP USER regressuser3;
DROP USER regressuser4;