Search Postgresql Archives

Inconsistent permission enforcement for schemas

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello,

Why can a user access a table from a forbidden schema if the table is explicitly specified in the definition of a view in an allowed schema? And not if the table is accessed by a function used in the view.

Example:
```
DROP SCHEMA IF EXISTS forbidden_schema CASCADE;
DROP SCHEMA IF EXISTS allowed_schema CASCADE;
DROP FUNCTION IF EXISTS func CASCADE;
DROP USER IF EXISTS pinky;

CREATE SCHEMA forbidden_schema;
CREATE TABLE forbidden_schema.demo AS SELECT 1;

CREATE SCHEMA allowed_schema;

CREATE OR REPLACE FUNCTION func() RETURNS BIGINT AS
$$
DECLARE
 i BIGINT;
BEGIN
    SELECT * INTO i FROM forbidden_schema.demo;
    RETURN i;
END;
$$ LANGUAGE plpgsql;

CREATE VIEW allowed_schema.can_see AS SELECT * FROM forbidden_schema.demo;
CREATE VIEW allowed_schema.cannot_see AS SELECT * FROM func();

CREATE ROLE pinky LOGIN NOSUPERUSER PASSWORD 'pinky';
GRANT ALL PRIVILEGES ON SCHEMA allowed_schema TO pinky;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA allowed_schema TO pinky;
```

And now, as user pinky:
```
q=> SELECT * FROM allowed_schema.can_see;
 ?column?
----------
        1
(1 row)

q=> SELECT * FROM allowed_schema.cannot_see;
ERROR:  permission denied for schema forbidden_schema
LINE 1: SELECT *        FROM forbidden_schema.demo
                             ^
QUERY:  SELECT *        FROM forbidden_schema.demo
CONTEXT:  PL/pgSQL function func() line 5 at SQL statement
```

Best regards,
Marcin Barczyński

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux