The end is nigh! PostgreSQL has substantially tightened restrictions on the use of the "public" schema.
Here, a standard login user (not superuser) tries to make a table, as one does:
user=> CREATE TABLE mydata (id integer); ERROR: permission denied for schema public LINE 1: CREATE TABLE mydata (id integer);
NoooO! Why can I not write a table into public?
For developers and experimenters, one of the long-time joys of PostgreSQL has been the free-and-easy security policy that PostgreSQL has shipped with for the "public" schema.
- "public" is in the default
search_path, so you can always find things in it; and,
- any user can create new objects in "public"; so,
- "just throw it in public!" has been an easy collaboration trick.
However, for anyone using a database for more than 15 minutes, the implications of "loosy goosey public" are pretty clear:
- "public" ends up as a garbage pile of temporary and long-abandoned tables,
- "public" is a quiet security hole, as it tends to stay on the default
search_path, sometimes in front of other schemas.
Initially, The Wild West
Setting up a fresh database and experimenting should be fun!
The easiest thing is to just give everybody the "postgres" super-user password. Infinite privileges and complete transparency for all!
However, this approach is limited to the earliest days of experimentation. You will need something more as you move into production.
Separating User Data
Eventually you will have production data, and need to limit access to that data, but retain temporary and experimental data and users. How can you easily segment that scratch data from production data?
PostgreSQL makes it very easy to keep user data separate, because the
search_path starts with a "user schema". See that
$user entry in the
search_path? It's a dynamic variable that gets filled in with your user name.
postgres=> show search_path; search_path ----------------- "$user", public
To try it out, first make a basic login user.
-- as 'postgres' CREATE USER luser1;
Then, make a "user schema" for that user.
-- as 'postgres' CREATE SCHEMA luser1 AUTHORIZATION luser1;
luser1creates a table without specifying the schema, the table will be created in the first schema on their
search_path. By default, the first schema is
$userwhich expands to their user name, and there is now a schema that matches their user name, and thus the place the table is created!
-- as 'luser1' CREATE TABLE my_test_data (id integer);
List of relations Schema | Name | Type | Owner --------+--------------+-------+------- luser1 | my_test_data | table | luser1
So, if you simply create a user schema for each new login user you create, you can neatly separate user data from other data, as each user will end up creating new tables and objects inside their user schema by default.
Sharing User Data
Even back in PostgreSQL 14, in order to share data between user-owned tables in
public schema, it was necessary to
GRANT SELECT on those tables to the
roles you wanted to access them.
With a "user schema" setup, the same principle applies, except you also have to
GRANT USAGE on your user schema to any roles you want to access your table.
-- as 'luser1' GRANT USAGE ON SCHEMA luser1 TO luser2; GRANT SELECT ON my_test_data TO luser2;
Sharing with Roles
Rather than individually granting
SELECT to every user who might
want to work with their data, a user can grant to an entire class of users. If
the database administrator sets up a role that includes all the relevant users:
-- as 'postgres' CREATE USER luser1 LOGIN; CREATE USER luser2 LOGIN; CREATE ROLE lusers; GRANT lusers TO luser1, luser2; -- as 'luser1' GRANT USAGE ON SCHEMA luser1 TO lusers; GRANT SELECT ON my_test_data TO lusers;
Now as new accounts are added to the
lusers role, they will automagically be
able to access the tables that
luser1 has shared.
- PostgreSQL 15 removes the global write privilege from the
- But, it's really easy to provide the same level of collaboration to users, by making use of user schemas, and role-based access.
October 12, 2022 •More by this author