Public Schema Security Upgrade in PostgreSQL 15
source link: https://www.percona.com/blog/public-schema-security-upgrade-in-postgresql-15/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Public Schema Security Upgrade in PostgreSQL 15
June 28, 2023
In the Postgres database, the application data can be organized in various ways using Postgres schemas. In the Postgres database cluster, whenever we create a new database, It gets created with the default schema called public schema. This blog post will discuss the Public Schema Security upgrade in PostgreSQL 15.
postgres=# create database d1; CREATE DATABASE postgres=# postgres=# c d1 You are now connected to database "d1" as user "postgres". d1=# dn List of schemas Name | Owner --------+------------------- public | pg_database_owner (1 row) |
When we create a table without specifying the schema name, it gets created in the schema, which is set as per the search_path. By default, The first part of search_path defines the schema with the same name as the current user, and the second part of search_path refers to the public schema.
postgres=# show search_path; search_path ----------------- "$user", public (1 row) |
Postgres looks for the schemas sequentially according to the list mentioned in the search_path, so when we execute create table command, Postgres creates the table in the first schema mentioned in the search_path. If it is not present, it creates it in the following schema.
Similarly, if the schema name is not specified in the select query, Postgres will search for tables within the named schema according to the search_path.
Public Schema security upgrade in PostgreSQL 15
Up to Postgres 14, whenever you create a database user, by default, it gets created with CREATE and USAGE privileges on the public schema.
It means that until Postgres 14, any user can write to the public schema until you manually revoke the user’s create privilege on the public schema.
Starting with PostgreSQL 15, the CREATE privilege on public schema is revoked/removed from all users except the database owner.
In Postgres 15, now new users cannot create tables or write data to Postgres public schema by default. You have to grant create privilege to the new user manually.
The usage privilege on the public schema for the new users is still present in Postgres 15, like in Postgres 14 and previous versions.
The example below shows that a new user (test1) can create a table in Postgres 14 without granting any privileges.
postgres=# conninfo You are connected to database "postgres" as user "test1" via socket in "/var/run/postgresql" at port "5432". postgres=# select version(); version --------------------------------------------------------------------------------------------------------- PostgreSQL 14.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit (1 row) postgres=# postgres=# postgres=# show search_path; search_path ----------------- "$user", public (1 row) postgres=# postgres=# create table department (empID int); CREATE TABLE postgres=# insert into department values (10); INSERT 0 1 postgres=# |
The example below shows that Postgres 15 only allows new users (test1) to create tables by granting them create privileges on the public schema.
postgres=# conninfo You are connected to database "postgres" as user "test1" via socket in "/var/run/postgresql" at port "5432". postgres=# select version(); version --------------------------------------------------------------------------------------------------------- PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit (1 row) postgres=# show search_path; search_path ----------------- "$user", public (1 row) postgres=# create table department (empID int); ERROR: permission denied for schema public LINE 1: create table department (empID int); postgres=# conninfo You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432". postgres=# postgres=# grant create on schema public to test1; GRANT postgres=# postgres=# conninfo You are connected to database "postgres" as user "test1" via socket in "/var/run/postgresql" at port "5432". postgres=# create table department (empID int); CREATE TABLE postgres=# |
The following example shows that the usage privilege on the public schema for the new users is still present in Postgres 15, like in Postgres 14 and previous versions.
postgres=# conninfo You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432". postgres=# postgres=# create table transport ( vehicleno int); CREATE TABLE postgres=# insert into transport values (25); INSERT 0 1 postgres=# postgres=# grant select on transport to test1; GRANT postgres=# q -bash-4.2$ psql -d postgres -U test1 Password for user test1: psql (15.3) Type "help" for help. postgres=# select * from transport; vehicleno ----------- (1 row) postgres=# |
Public schema ownership changes in PostgreSQL 15
In Postgres 14 and previous versions, by default, the public schema is owned by the bootstrap superuser (postgres), but from Postgres 15, ownership of the public schema has been changed to the new pg_database_owner role. It enables every database owner to own the database’s public schema.
The below example shows the ownership changes between Postgres 14 and Postgres 15.
Postgres 14
postgres=# dn List of schemas Name | Owner --------+---------- public | postgres (1 row) |
Postgres 15
postgres=# dn List of schemas Name | Owner --------+------------------- public | pg_database_owner (1 row) postgres=# |
Visit the links below for further details about the Postgres schemas.
https://www.postgresql.org/docs/15/ddl-schemas.html
https://www.postgresql.org/docs/release/15.0/
Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community in a single distribution, designed and tested to work together.
Download Percona Distribution for PostgreSQL Today!
Share This Post!
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK