Inconsistent permission enforcement for schemas
От | Marcin Barczyński |
---|---|
Тема | Inconsistent permission enforcement for schemas |
Дата | |
Msg-id | CAK61bT-uP8rS9fYyc8SEjUNvXfra51+iaRchncnuzk2XJ=Cthg@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Inconsistent permission enforcement for schemas
|
Список | pgsql-general |
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
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
В списке pgsql-general по дате отправления: