Not open to public
Back in 2020 at the FOSDEM PGDay I discovered how the public schema’s permissions were far too permissive.
In any PostgreSQL up to version 14 any logged user could create relations or functions in the public schema posing a concrete risk for the database security.
In this post I want to recollect the thoughts about the issue, how to fix it and why it shouldn’t be ignored even if the instance has been migrated to a PostgreSQL version in theory not affected by the problem.
The ACL format
The ACL format in PostgreSQL has its own syntax and meaning. In order to display the ACL for the public schema is sufficient to connect to a database on a PostgreSQL 14 instance using psql and run the command \dn+.
If we want to display the query executed by \dn+ we can run psql with the option -E. This is what we get with the -E option.
psql -E postgres
psql (14.11)
Type "help" for help.
postgres=# \dn+
********* QUERY **********
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",
pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges",
pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
**************************
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
(1 row)The column “Access privileges” shows the ACL.
The ACL in postgresql has the following format:
<GRANTEE>=<PRIVILEGES>/<GRANTOR>- Usage. The grantee can can use (read/write) the objects created within the schema. Granting individual object privileges may be necessary though.
- Create. The grantee can create objects into the schema.
In the column “Access privileges” are shown two ACL, one for each line.
- postgres=UC/postgres In the first line the postgres grants Usage and Create on the schema public to itself.
- =UC/postgres In the second line the postgres user grants Usage and Create to an empty grantee.
The empty grantee coincides with the special role public which includes every role created on the system.
Therefore the ACL =UC/postgres means that any logged user on the instance has the Usage and Create privilege on the public schema.
Hijacking the database
How this affects the security then? An example derived from the FOSDEM PGDay 2020 presentation will clarify the problem.
Let say we have a table app_users made of three fields, id_user,user_name,user_email. The user_email field contains emails with mixed case and we want to select from the field returning always the output in lowercase though.
CREATE TABLE app_users
(
id_user serial PRIMARY KEY,
user_name varchar(32) UNIQUE,
user_email varchar(128)
);
INSERT INTO app_users (user_name,user_email) VALUES
('example','info@Example.Com'),
('foo','FOO@foo.Com'),
('bar','Bar@foo.Com'),
('foobar','FooBar@foo.Com')
;A perfectly legit query can be the following.
SELECT lower(user_email) FROM app_users;
lower
------------------
info@example.com
foo@foo.com
bar@foo.com
foobar@foo.com
(4 rows)The function lower accepts a text data type argument.
