path: root/src/test/regress/expected/privileges.out

--
-- Test access privileges
--
-- Clean up in case a prior regression run failed
-- Suppress NOTICE messages when users/groups don't exist
SET client_min_messages TO 'error';
DROP ROLE IF EXISTS regressgroup1;
DROP ROLE IF EXISTS regressgroup2;
DROP ROLE IF EXISTS regressuser1;
DROP ROLE IF EXISTS regressuser2;
DROP ROLE IF EXISTS regressuser3;
DROP ROLE IF EXISTS regressuser4;
RESET client_min_messages;
-- test proper begins here
CREATE USER regressuser1;
CREATE USER regressuser2;
CREATE USER regressuser3;
CREATE USER regressuser4;
CREATE USER regressuser4;	-- duplicate
ERROR:  role "regressuser4" already exists
CREATE GROUP regressgroup1;
CREATE GROUP regressgroup2 WITH USER regressuser1, regressuser2;
ALTER GROUP regressgroup1 ADD USER regressuser4;
ALTER GROUP regressgroup2 ADD USER regressuser2;	-- duplicate
NOTICE:  role "regressuser2" is already a member of role "regressgroup2"
ALTER GROUP regressgroup2 DROP USER regressuser2;
ALTER GROUP regressgroup2 ADD USER regressuser4;
-- test owner privileges
SET SESSION AUTHORIZATION regressuser1;
SELECT session_user, current_user;
 session_user | current_user 
--------------+--------------
 regressuser1 | regressuser1
(1 row)

CREATE TABLE atest1 ( a int, b text );
SELECT * FROM atest1;
 a | b 
---+---
(0 rows)

INSERT INTO atest1 VALUES (1, 'one');
DELETE FROM atest1;
UPDATE atest1 SET a = 1 WHERE b = 'blech';
LOCK atest1 IN ACCESS EXCLUSIVE MODE;
REVOKE ALL ON atest1 FROM PUBLIC;
SELECT * FROM atest1;
 a | b 
---+---
(0 rows)

GRANT ALL ON atest1 TO regressuser2;
GRANT SELECT ON atest1 TO regressuser3, regressuser4;
SELECT * FROM atest1;
 a | b 
---+---
(0 rows)

CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
GRANT SELECT ON atest2 TO regressuser2;
GRANT UPDATE ON atest2 TO regressuser3;
GRANT INSERT ON atest2 TO regressuser4;
SET SESSION AUTHORIZATION regressuser2;
SELECT session_user, current_user;
 session_user | current_user 
--------------+--------------
 regressuser2 | regressuser2
(1 row)

-- try various combinations of queries on atest1 and atest2
SELECT * FROM atest1; -- ok
 a | b 
---+---
(0 rows)

SELECT * FROM atest2; -- ok
 col1 | col2 
------+------
(0 rows)

INSERT INTO atest1 VALUES (2, 'two'); -- ok
INSERT INTO atest2 VALUES ('foo', true); -- fail
ERROR:  permission denied for relation atest2
INSERT INTO atest1 SELECT 1, b FROM atest1; -- ok
UPDATE atest1 SET a = 1 WHERE a = 2; -- ok
UPDATE atest2 SET col2 = NOT col2; -- fail
ERROR:  permission denied for relation atest2
SELECT * FROM atest1 FOR UPDATE; -- ok
 a |  b  
---+-----
 1 | two
 1 | two
(2 rows)

SELECT * FROM atest2 FOR UPDATE; -- fail
ERROR:  permission denied for relation atest2
DELETE FROM atest2; -- fail
ERROR:  permission denied for relation atest2
LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- fail
ERROR:  permission denied for relation atest2
COPY atest2 FROM stdin; -- fail
ERROR:  permission denied for relation atest2
GRANT ALL ON atest1 TO PUBLIC; -- fail
WARNING:  no privileges were granted for "atest1"
-- checks in subquery, both ok
SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
 a | b 
---+---
(0 rows)

SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
 col1 | col2 
------+------
(0 rows)

SET SESSION AUTHORIZATION regressuser3;
SELECT session_user, current_user;
 session_user | current_user 
--------------+--------------
 regressuser3 | regressuser3
(1 row)

SELECT * FROM atest1; -- ok
 a |  b  
---+-----
 1 | two
 1 | two
(2 rows)

SELECT * FROM atest2; -- fail
ERROR:  permission denied for relation atest2
INSERT INTO atest1 VALUES (2, 'two'); -- fail
ERROR:  permission denied for relation atest1
INSERT INTO atest2 VALUES ('foo', true); -- fail
ERROR:  permission denied for relation atest2
INSERT INTO atest1 SELECT 1, b FROM atest1; -- fail
ERROR:  permission denied for relation atest1
UPDATE atest1 SET a = 1 WHERE a = 2; -- fail
ERROR:  permission denied for relation atest1
UPDATE atest2 SET col2 = NULL; -- ok
UPDATE atest2 SET col2 = NOT col2; -- fails; requires SELECT on atest2
ERROR:  permission denied for relation atest2
UPDATE atest2 SET col2 = true FROM atest1 WHERE atest1.a = 5; -- ok
SELECT * FROM atest1 FOR UPDATE; -- fail
ERROR:  permission denied for relation atest1
SELECT * FROM atest2 FOR UPDATE; -- fail
ERROR:  permission denied for relation atest2
DELETE FROM atest2; -- fail
ERROR:  permission denied for relation atest2
LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- ok
COPY atest2 FROM stdin; -- fail
ERROR:  permission denied for relation atest2
-- checks in subquery, both fail
SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
ERROR:  permission denied for relation atest2
SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
ERROR:  permission denied for relation atest2
SET SESSION AUTHORIZATION regressuser4;
COPY atest2 FROM stdin; -- ok
SELECT * FROM atest1; -- ok
 a |  b  
---+-----
 1 | two
 1 | two
(2 rows)

-- groups
SET SESSION AUTHORIZATION regressuser3;
CREATE TABLE atest3 (one int, two int, three int);
GRANT DELETE ON atest3 TO GROUP regressgroup2;
SET SESSION AUTHORIZATION regressuser1;
SELECT * FROM atest3; -- fail
ERROR:  permission denied for relation atest3
DELETE FROM atest3; -- ok
-- views
SET SESSION AUTHORIZATION regressuser3;
CREATE VIEW atestv1 AS SELECT * FROM atest1; -- ok
/* The next *should* fail, but it's not implemented that way yet. */
CREATE VIEW atestv2 AS SELECT * FROM atest2;
CREATE VIEW atestv3 AS SELECT * FROM atest3; -- ok
SELECT * FROM atestv1; -- ok
 a |  b  
---+-----
 1 | two
 1 | two
(2 rows)

SELECT * FROM atestv2; -- fail
ERROR:  permission denied for relation atest2
GRANT SELECT ON atestv1, atestv3 TO regressuser4;
GRANT SELECT ON atestv2 TO regressuser2;
SET SESSION AUTHORIZATION regressuser4;
SELECT * FROM atestv1; -- ok
 a |  b  
---+-----
 1 | two
 1 | two
(2 rows)

SELECT * FROM atestv2; -- fail
ERROR:  permission denied for relation atestv2
SELECT * FROM atestv3; -- ok
 one | two | three 
-----+-----+-------
(0 rows)

CREATE VIEW atestv4 AS SELECT * FROM atestv3; -- nested view
SELECT * FROM atestv4; -- ok
 one | two | three 
-----+-----+-------
(0 rows)

GRANT SELECT ON atestv4 TO regressuser2;
SET SESSION AUTHORIZATION regressuser2;
-- Two complex cases:
SELECT * FROM atestv3; -- fail
ERROR:  permission denied for relation atestv3
SELECT * FROM atestv4; -- ok (even though regressuser2 cannot access underlying atestv3)
 one | two | three 
-----+-----+-------
(0 rows)

SELECT * FROM atest2; -- ok
 col1 | col2 
------+------
 bar  | t
(1 row)

SELECT * FROM atestv2; -- fail (even though regressuser2 can access underlying atest2)
ERROR:  permission denied for relation atest2
-- privileges on functions, languages
-- switch to superuser
\c -
REVOKE ALL PRIVILEGES ON LANGUAGE sql FROM PUBLIC;
GRANT USAGE ON LANGUAGE sql TO regressuser1; -- ok
GRANT USAGE ON LANGUAGE c TO PUBLIC; -- fail
ERROR:  language "c" is not trusted
HINT:  Only superusers can use untrusted languages.
SET SESSION AUTHORIZATION regressuser1;
GRANT USAGE ON LANGUAGE sql TO regressuser2; -- fail
WARNING:  no privileges were granted for "sql"
CREATE FUNCTION testfunc1(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql;
CREATE FUNCTION testfunc2(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql;
REVOKE ALL ON FUNCTION testfunc1(int), testfunc2(int) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION testfunc1(int), testfunc2(int) TO regressuser2;
GRANT USAGE ON FUNCTION testfunc1(int) TO regressuser3; -- semantic error
ERROR:  invalid privilege type USAGE for function
GRANT ALL PRIVILEGES ON FUNCTION testfunc1(int) TO regressuser4;
GRANT ALL PRIVILEGES ON FUNCTION testfunc_nosuch(int) TO regressuser4;
ERROR:  function testfunc_nosuch(integer) does not exist
CREATE FUNCTION testfunc4(boolean) RETURNS text
  AS 'select col1 from atest2 where col2 = $1;'
  LANGUAGE sql SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION testfunc4(boolean) TO regressuser3;
SET SESSION AUTHORIZATION regressuser2;
SELECT testfunc1(5), testfunc2(5); -- ok
 testfunc1 | testfunc2 
-----------+-----------
        10 |        15
(1 row)

CREATE FUNCTION testfunc3(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; -- fail
ERROR:  permission denied for language sql
SET SESSION AUTHORIZATION regressuser3;
SELECT testfunc1(5); -- fail
ERROR:  permission denied for function testfunc1
SELECT col1 FROM atest2 WHERE col2 = true; -- fail
ERROR:  permission denied for relation atest2
SELECT testfunc4(true); -- ok
 testfunc4 
-----------
 bar
(1 row)

SET SESSION AUTHORIZATION regressuser4;
SELECT testfunc1(5); -- ok
 testfunc1 
-----------
        10
(1 row)

DROP FUNCTION testfunc1(int); -- fail
ERROR:  must be owner of function testfunc1
\c -
DROP FUNCTION testfunc1(int); -- ok
-- restore to sanity
GRANT ALL PRIVILEGES ON LANGUAGE sql TO PUBLIC;
-- has_table_privilege function
-- bad-input checks
select has_table_privilege(NULL,'pg_authid','select');
 has_table_privilege 
---------------------
 
(1 row)

select has_table_privilege('pg_shad','select');
ERROR:  relation "pg_shad" does not exist
select has_table_privilege('nosuchuser','pg_authid','select');
ERROR:  role "nosuchuser" does not exist
select has_table_privilege('pg_authid','sel');
ERROR:  unrecognized privilege type: "sel"
select has_table_privilege(-999999,'pg_authid','update');
ERROR:  role with OID 4293967297 does not exist
select has_table_privilege(1,'select');
ERROR:  relation with OID 1 does not exist
-- superuser
\c -
select has_table_privilege(current_user,'pg_authid','select');
 has_table_privilege 
---------------------
 t
(1 row)

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

select has_table_privilege(t2.oid,'pg_authid','update')
from (select oid from pg_roles where rolname = current_user) as t2;
 has_table_privilege 
---------------------
 t
(1 row)

select has_table_privilege(t2.oid,'pg_authid','delete')
from (select oid from pg_roles where rolname = current_user) as t2;
 has_table_privilege 
---------------------
 t
(1 row)

-- 'rule' privilege no longer exists, but for backwards compatibility
-- has_table_privilege still recognizes the keyword and says FALSE
select has_table_privilege(current_user,t1.oid,'rule')
from (select oid from pg_class where relname = 'pg_authid') 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_authid') as t1;
 has_table_privilege 
---------------------
 t
(1 row)

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

select has_table_privilege(t2.oid,t1.oid,'insert')
from (select oid from pg_class where relname = 'pg_authid') as t1,
  (select oid from pg_roles where rolname = current_user) as t2;
 has_table_privilege 
---------------------
 t
(1 row)

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

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

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

select has_table_privilege(t1.oid,'trigger')
from (select oid from pg_class where relname = 'pg_authid') 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.oid,'pg_class','update')
from (select oid from pg_roles where rolname = current_user) as t2;
 has_table_privilege 
---------------------
 f
(1 row)

select has_table_privilege(t2.oid,'pg_class','delete')
from (select oid from pg_roles where rolname = current_user) as t2;
 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.oid,t1.oid,'select')
from (select oid from pg_class where relname = 'pg_class') as t1,
  (select oid from pg_roles where rolname = current_user) as t2;
 has_table_privilege 
---------------------
 t
(1 row)

select has_table_privilege(t2.oid,t1.oid,'insert')
from (select oid from pg_class where relname = 'pg_class') as t1,
  (select oid from pg_roles where rolname = 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.oid,'atest1','update')
from (select oid from pg_roles where rolname = current_user) as t2;
 has_table_privilege 
---------------------
 f
(1 row)

select has_table_privilege(t2.oid,'atest1','delete')
from (select oid from pg_roles where rolname = current_user) as t2;
 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.oid,t1.oid,'select')
from (select oid from pg_class where relname = 'atest1') as t1,
  (select oid from pg_roles where rolname = current_user) as t2;
 has_table_privilege 
---------------------
 t
(1 row)

select has_table_privilege(t2.oid,t1.oid,'insert')
from (select oid from pg_class where relname = 'atest1') as t1,
  (select oid from pg_roles where rolname = 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;
SET SESSION AUTHORIZATION regressuser2;
GRANT SELECT ON atest4 TO regressuser3;
GRANT UPDATE ON atest4 TO regressuser3; -- fail
WARNING:  no privileges were granted for "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;
REVOKE USAGE ON LANGUAGE sql FROM regressuser1;
DROP USER regressuser1;
DROP USER regressuser2;
DROP USER regressuser3;
DROP USER regressuser4;