The PostgreSQL Global Development team released the second beta version of PostgreSQL 17 on June 27th, 2024, and it is now available for testing. In this beta version, we can explore the new features that will be released in the official final release of PostgreSQL 17. In this blog, we will discuss some of the […]
Jul
10
2024
10
2024
--
The Powerful Features Released in PostgreSQL 17 Beta 2
Nov
30
2023
30
2023
--
Enhancing PostgreSQL Security: How to Encrypt the pgBackRest Repository
Encryption is the process of turning data into an unrecognizable format unless the necessary password (also known as passphrase) or decryption key is provided.
This blog describes how to encrypt the pgBackRest repository. pgBackRest is the backup tool used to perform Postgres database backup, restoration, and point-in-time recovery (PITR). The repository is where pgBackRest stores backups and archives WAL segments.
pgBackRest will encrypt the repository based on a user-provided password, thereby preventing unauthorized access to data stored within the repository.
In this demonstration, it is assumed that the pgBackRest is already installed and configured on the dedicated backup node and configured to take backups from the remote database node. The repository will be configured with a cipher type and key to demonstrate encryption.
Follow the below steps to encrypt the pgBackRest repository:
Backup node 172.20.20.20 (Dummy IP)
Remote DB node 172.15.15.15 (Dummy IP)
1) First, generate the cipher key. pgBackRest will use this cipher key to encrypt the pgBackRest repository.
It is important to use a long, random passphrase for the cipher key. A good way to generate one is to run: openssl rand -base64 48. (on the backup node):
postgres@ip-172.20.20.20:~$ openssl rand -base64 48 PNaf798o9Sz1RRRRRRRRhH62R1BSQal+lAxpb3ZTAblNPTxC72E1nAcQGVwn40co postgres@ip-172.20.20.20:~$
2) On the backup node, add the cipher type and key parameters in the pgBackRest configuration file. /etc/pgbackrest.conf:
vi /etc/pgbackrest.conf repo1-cipher-pass=PNaf798o9Sz1RRRRRRRRhH62R1BSQal+lAxpb3ZTAblNPTxC72E1nAcQGVwn40co repo1-cipher-type=aes-256-cbc
If you have an existing pgbackrest setup, then the existing stanza cannot be used after configuring the encryption for the repository. A new stanza needs to be created for taking the backup in the encryption-enabled repository. Using the existing stanza will result in the following error:
In the below example, the existing stanza dbtest has been used.
postgres@ip-172.20.20.20:~$ pgbackrest --config=/etc/pgbackrest.conf --stanza=dbtest --log-level-console=info backup --type=full 2023-09-08 14:22:06.178 P00 INFO: backup command begin 2.47: --config=/etc/pgbackrest.conf --exec-id=5971-2fe78c47 --log-level-console=info --log-level-file=debug--pg1-host=172.15.15.15 --pg1-host-user=postgres --pg1-path=/var/lib/postgresql/15/main --process-max=2 --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=dbtest --start-fast --stop-auto --type=full ERROR: [095]: unable to load info file '/var/lib/pgbackrest/backup/dbtest/backup.info' or '/var/lib/pgbackrest/backup/dbtest/ backup.info.copy': CryptoError: cipher header invalid HINT: is or was the repo encrypted? CryptoError: cipher header invalid HINT: is or was the repo encrypted? HINT: backup.info cannot be opened and is required to perform a backup. HINT: has a stanza-create been performed? 2023-09-08 14:22:06.180 P00 INFO: backup command end: aborted with exception [095] postgres@ip-172.20.20.20:~$
The pgBackRest configuration files will look like this after adding the cypher pass (key) and type.
Backup node
cat /etc/pgbackrest.conf: [global] repo1-path=/var/lib/pgbackrest repo1-retention-full=2 process-max=2 log-level-console=info log-level-file=debug start-fast=y stop-auto=y repo1-cipher-pass=PNaf798o9Sz1RRRRRRRRhH62R1BSQal+lAxpb3ZTAblNPTxC72E1nAcQGVwn40co repo1-cipher-type=aes-256-cbc [dbtest_new] pg1-path=/var/lib/postgresql/15/main pg1-host=172.15.15.15 pg1-host-user=postgres
DB node
cat /etc/pgbackrest.conf: [global] repo1-path=/var/lib/pgbackrest repo1-host=172.20.20.20 repo1-host-user=postgres process-max=2 log-level-console=info log-level-file=debug [dbtest_new] pg1-path=/var/lib/postgresql/15/main
3) Create a new stanza (on the backup node):
postgres@ip-172.20.20.20:~$ pgbackrest --config=/etc/pgbackrest.conf --stanza=dbtest_new stanza-create 2023-09-08 14:24:55.779 P00 INFO: stanza-create command begin 2.47: --config=/etc/pgbackrest.conf --exec-id=5980-f29c6484 --log-level-console=info --log-level-file=debug --pg1-host=172.15.15.15 --pg1-host-user=postgres --pg1-path=/var/lib/postgresql/15/main --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --stanza=dbtest_new 2023-09-08 14:24:56.927 P00 INFO: stanza-create for stanza 'dbtest_new' on repo1 2023-09-08 14:24:57.045 P00 INFO: stanza-create command end: completed successfully (1269ms) postgres@ip-172.20.20.20:~$
4) Update the archive_command with the new stanza details on the DB node:
postgres=# ALTER SYSTEM SET archive_command = '/bin/pgbackrest --config=/etc/pgbackrest.conf --stanza=dbtest_new archive-push %p'; ALTER SYSTEM
5) Reload the Postgres cluster (on the DB node):
postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)
6) Execute the check command. The check command validates that pgBackRest and the archive_command setting are configured correctly for archiving and backups for the specified stanza:
postgres@ip-172.20.20.20:~$ pgbackrest --config=/etc/pgbackrest.conf --stanza=dbtest_new --log-level-console=info check 2023-09-08 15:26:34.349 P00 INFO: check command begin 2.47: --config=/etc/pgbackrest.conf --exec-id=7993-5acde7b9 --log-level-console=info --log-level-file=debug --pg1-host=172.15.15.15 --pg1-host-user=postgres --pg1-path=/var/lib/postgresql/15/main --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --stanza=dbtest_new 2023-09-08 15:26:35.585 P00 INFO: check repo1 configuration (primary) 2023-09-08 15:26:35.788 P00 INFO: check repo1 archive for WAL (primary) 2023-09-08 15:26:36.990 P00 INFO: WAL segment 000000010000000000000018 successfully archived to '/var/lib/pgbackrest/archive/dbtest_new/15-1/0000000100000000/000000010000000000000018-7cef04977b8b50f102a3d74ace8ab1cc4a035c8d.gz' on repo1 2023-09-08 15:26:37.092 P00 INFO: check command end: completed successfully (2745ms) postgres@ip-172.20.20.20:~$
7) Perform a FULL backup:
postgres@ip-172.20.20.20:~$ pgbackrest --config=/etc/pgbackrest.conf --stanza=dbtest_new --log-level-console=info backup --type=full 2023-09-08 15:26:49.028 P00 INFO: backup command begin 2.47: --config=/etc/pgbackrest.conf --exec-id=8060-e6fa0627 --log-level-console=info --log-level-file=debug --pg1-host=172.15.15.15 --pg1-host-user=postgres --pg1-path=/var/lib/postgresql/15/main --process-max=2 --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=dbtest_new --start-fast --stop-auto --type=full 2023-09-08 15:26:50.016 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes 2023-09-08 15:26:50.622 P00 INFO: backup start archive = 00000001000000000000001A, lsn = 0/1A000028 2023-09-08 15:26:50.622 P00 INFO: check archive for prior segment 000000010000000000000019 2023-09-08 15:26:54.242 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive 2023-09-08 15:26:54.447 P00 INFO: backup stop archive = 00000001000000000000001A, lsn = 0/1A000100 2023-09-08 15:26:54.454 P00 INFO: check archive for segment(s) 00000001000000000000001A:00000001000000000000001A 2023-09-08 15:26:54.970 P00 INFO: new backup label = 20230908-152649F 2023-09-08 15:26:55.024 P00 INFO: full backup size = 22.0MB, file total = 961 2023-09-08 15:26:55.024 P00 INFO: backup command end: completed successfully (5999ms) 2023-09-08 15:26:55.025 P00 INFO: expire command begin 2.47: --config=/etc/pgbackrest.conf --exec-id=8060-e6fa0627 --log-level-console=info --log-level-file=debug --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=dbtest_new 2023-09-08 15:26:55.026 P00 INFO: repo1: expire full backup 20230908-145538F 2023-09-08 15:26:55.035 P00 INFO: repo1: remove expired backup 20230908-145538F 2023-09-08 15:26:55.068 P00 INFO: repo1: 15-1 remove archive, start = 000000010000000000000015, stop = 000000010000000000000016 2023-09-08 15:26:55.068 P00 INFO: expire command end: completed successfully (43ms) postgres@ip-172.20.20.20:~$
8) Perform a DIFFERENTIAL backup (optional step):
postgres@ip-172.20.20.20:~$ pgbackrest --config=/etc/pgbackrest.conf --stanza=dbtest_new --log-level-console=info backup --type=diff 2023-09-08 15:27:01.723 P00 INFO: backup command begin 2.47: --config=/etc/pgbackrest.conf --exec-id=8216-4d363fc8 --log-level-console=info --log-level-file=debug --pg1-host=172.15.15.15 --pg1-host-user=postgres --pg1-path=/var/lib/postgresql/15/main --process-max=2 --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=dbtest_new --start-fast --stop-auto --type=diff 2023-09-08 15:27:02.644 P00 INFO: last backup label = 20230908-152649F, version = 2.47 2023-09-08 15:27:02.645 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes 2023-09-08 15:27:03.250 P00 INFO: backup start archive = 00000001000000000000001C, lsn = 0/1C000028 2023-09-08 15:27:03.251 P00 INFO: check archive for prior segment 00000001000000000000001B 2023-09-08 15:27:04.810 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive 2023-09-08 15:27:05.012 P00 INFO: backup stop archive = 00000001000000000000001C, lsn = 0/1C000100 2023-09-08 15:27:05.017 P00 INFO: check archive for segment(s) 00000001000000000000001C:00000001000000000000001C 2023-09-08 15:27:05.536 P00 INFO: new backup label = 20230908-152649F_20230908-152702D 2023-09-08 15:27:05.591 P00 INFO: diff backup size = 8.3KB, file total = 961 2023-09-08 15:27:05.592 P00 INFO: backup command end: completed successfully (3872ms) 2023-09-08 15:27:05.592 P00 INFO: expire command begin 2.47: --config=/etc/pgbackrest.conf --exec-id=8216-4d363fc8 --log-level-console=info --log-level-file=debug --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=dbtest_new 2023-09-08 15:27:05.602 P00 INFO: repo1: 15-1 no archive to remove 2023-09-08 15:27:05.603 P00 INFO: expire command end: completed successfully (11ms) postgres@ip-172.20.20.20:~$ |
9) To check out the backup status and its details, use the info command:
postgres@ip-172.20.20.20:~$ pgbackrest --config=/etc/pgbackrest.conf --stanza=dbtest_new info stanza: dbtest_new status: ok cipher: aes-256-cbc db (current) wal archive min/max (15): 000000010000000000000017/00000001000000000000001C full backup: 20230908-151854F timestamp start/stop: 2023-09-08 15:18:54+00 / 2023-09-08 15:18:59+00 wal start/stop: 000000010000000000000017 / 000000010000000000000017 database size: 22.0MB, database backup size: 22.0MB repo1: backup set size: 2.9MB, backup size: 2.9MB full backup: 20230908-152649F timestamp start/stop: 2023-09-08 15:26:49+00 / 2023-09-08 15:26:54+00 wal start/stop: 00000001000000000000001A / 00000001000000000000001A database size: 22.0MB, database backup size: 22.0MB repo1: backup set size: 2.9MB, backup size: 2.9MB diff backup: 20230908-152649F_20230908-152702D timestamp start/stop: 2023-09-08 15:27:02+00 / 2023-09-08 15:27:04+00 wal start/stop: 00000001000000000000001C / 00000001000000000000001C database size: 22.0MB, database backup size: 8.3KB repo1: backup set size: 2.9MB, backup size: 512B backup reference list: 20230908-152649F postgres@ip-172.20.20.20:~$
Conclusion
pgBackRest is a completely free and open source backup tool available for PostgreSQL, and here we have seen the steps to encrypt the pgBackRest repository. You may use it for personal or commercial purposes without any restrictions whatsoever. Its advanced backup repository encryption feature is very easy to implement.
To learn more about the pgBackRest backup tool and encryption, click the links below:
- https://pgbackrest.org/user-guide.html
- https://pgbackrest.org/user-guide.html#quickstart/configure-encryption
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.
Oct
09
2023
09
2023
--
PostgreSQL Tuple-Level Statistics With pgstattuple
Since Postgres table bloat degrades database performance, we can improve its performance by removing the table bloat. We can use the pgstattuple extension to identify the bloated tables.
This extension provides several functions for obtaining tuple-level statistics. Because the pgstattuple functions produce extensive page-level information, access to them is, by default, limited. Only the pg_stat_scan_tables role has the ability to EXECUTE the pgstattuple functions by default. The pgstattuple functions are not restricted to the superuser.
Using the pgstattuple function, we can list the tables with a high percentage of dead tuples and run a manual VACUUM to reclaim the space occupied by the dead tuples.
In this blog, we will discuss the pgstattuple extension, which provides various functions to obtain tuple-level statistics.
First, create the pgstattuple extension. The latest version of pgstattuple is 1.5.
postgres@ip-172-31-46-212:~$ psql psql (15.4 (Ubuntu 15.4-1.pgdg22.04+1)) Type "help" for help. postgres=# select * from pg_available_extensions where name='pgstattuple'; name | default_version | installed_version | comment -------------+-----------------+-------------------+----------------------------- pgstattuple | 1.5 | | show tuple-level statistics (1 row) postgres=# postgres=# create extension pgstattuple; CREATE EXTENSION postgres=# postgres=# select * from pg_available_extensions where name='pgstattuple'; name | default_version | installed_version | comment -------------+-----------------+-------------------+----------------------------- pgstattuple | 1.5 | 1.5 | show tuple-level statistics (1 row) postgres=#
By default, only the superuser has access to the pgstattuple functions; however, you can grant non-superuser access by granting the pg_stat_scan_tables role to the non-superuser.
postgres=# grant pg_stat_scan_tables to <nonsuperuser>;
GRANT ROLE
postgres=> du <nonsuperuser>
List of roles
Role name | Attributes | Member of
———–+————+———————–
<nonsuperuser> | | {pg_stat_scan_tables}
postgres=>
Next, before using the pgstattuple functions, let’s create a table and index for demonstration.
postgres=# create table workshop (jobno int); CREATE TABLE postgres=# insert into workshop values (generate_series(1,80000)); INSERT 0 80000 postgres=# create index workshop_index on workshop (jobno); CREATE INDEX postgres=#
pgstattuple functions
pgstattuple(regclass): This function returns a relation’s physical length, percentage of “dead” tuples, and other info. This may help users to determine whether a vacuum is necessary or not. The argument is the target relation’s name (optionally schema-qualified) or OID.
For example:
SELECT * FROM pgstattuple('pg_catalog.pg_proc');
SELECT * FROM pgstattuple('workshop');
pgstattuple only obtains a read lock on the relation. As a result, the pgstattuple output does not represent an instantaneous snapshot. The concurrent updates will change the output of the pgstattuple.
The example below shows the tuple statistics of the workshop table.
postgres=# SELECT * FROM pgstattuple('workshop');
-[ RECORD 1 ]------+--------
table_len | 2899968
tuple_count | 80000
tuple_len | 2240000
tuple_percent | 77.24
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 10056
free_percent | 0.35
Below are the pgstattuple output columns and their description.
| Column | Type | Description |
| table_len | bigint | Physical relation length in bytes |
| tuple_count | bigint | Number of live tuples |
| tuple_len | bigint | Total length of live tuples in bytes |
| tuple_percent | float8 | Percentage of live tuples |
| dead_tuple_count | bigint | Number of dead tuples |
| dead_tuple_len | bigint | Total length of dead tuples in bytes |
| dead_tuple_percent | float8 | Percentage of dead tuples |
| free_space | bigint | Total free space in bytes |
| free_percent | float8 | Percentage of free space |
Let’s check the tuple statistics when we delete or update the rows. Below are the current tuple statistics for the reference.
postgres=# SELECT * FROM pgstattuple('workshop');
-[ RECORD 1 ]------+--------
table_len | 2899968
tuple_count | 80000
tuple_len | 2240000
tuple_percent | 77.24
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 10056
free_percent | 0.35
Delete the few ROWS from the table.
postgres=# DELETE FROM workshop WHERE jobno % 8 = 0; DELETE 10000 postgres=#
In the below output, the dead_tuple_count shows that Postgres marked the rows as deleted but did not remove them from the table, as the length of the table is the same after the deletion of the rows.
postgres=# SELECT * FROM pgstattuple('workshop');
-[ RECORD 1 ]------+--------
table_len | 2899968
tuple_count | 70000
tuple_len | 1960000
tuple_percent | 67.59
dead_tuple_count | 10000
dead_tuple_len | 280000
dead_tuple_percent | 9.66
free_space | 10056
free_percent | 0.35
Now execute the VACUUM on the table; after running the plain VACUUM, we see that the:
- free_space has increased. Vacuum has reclaimed the space and made it available for re-use at the database level.
- Dead rows have been removed.
- The table_len is the same as O.S. level space is not reclaimed.
postgres=# vacuum workshop;
VACUUM
postgres=# SELECT * FROM pgstattuple('workshop');
-[ RECORD 1 ]------+--------
table_len | 2899968
tuple_count | 70000
tuple_len | 1960000
tuple_percent | 67.59
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 330412
free_percent | 11.39
After running the VACUUM FULL, we can see that table_len has decreased. It shows that O.S. level space is reclaimed after running the VACUUM FULL.
postgres=# vacuum full workshop;
VACUUM
postgres=# SELECT * FROM pgstattuple('workshop');
-[ RECORD 1 ]------+--------
table_len | 2539520
tuple_count | 70000
tuple_len | 1960000
tuple_percent | 77.18
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 10840
free_percent | 0.43
postgres=#
pgstattuple queries to check table bloat
We can list the tables where the dead tuple percentage is high by using the query below.
postgres=#select relname,(pgstattuple(oid)).dead_tuple_percent from pg_class where relkind = 'r' order by dead_tuple_percent desc; relname | dead_tuple_percent --------------------------+-------------------- pg_init_privs | 2.56 pg_class | 1.69 pg_statistic | 1.45 pg_authid | 1.37 pg_extension | 1.28 pg_type | 1.17 pg_namespace | 1.17
The below query will show you the tuple statistics of the tables in detail where the dead tuple percentage is high.
SELECT relname,oid,relowner,(pgstattuple(oid)).dead_tuple_percent ,(pgstattuple(oid)).* FROM pg_class WHERE relkind = 'r' order by dead_tuple_percent desc; -[ RECORD 1 ]------+------------------------- relname | pg_init_privs oid | 3394 relowner | 10 dead_tuple_percent | 2.56 table_len | 24576 tuple_count | 222 tuple_len | 17316 tuple_percent | 70.46 dead_tuple_count | 9 dead_tuple_len | 630 dead_tuple_percent | 2.56 free_space | 4568 free_percent | 18.59 -[ RECORD 2 ]------+------------------------- relname | pg_class oid | 1259 relowner | 10 dead_tuple_percent | 1.69 table_len | 114688 tuple_count | 415 tuple_len | 80473 tuple_percent | 70.17 dead_tuple_count | 11 dead_tuple_len | 1937 dead_tuple_percent | 1.69 free_space | 26976 free_percent | 23.52
pgstatindex(regclass): This function returns a record showing information about a B-tree index.
For example:
postgres=# SELECT * FROM pgstatindex ('workshop_index');
-[ RECORD 1 ]------+--------
version | 4
tree_level | 1
index_size | 1589248
root_block_no | 3
internal_pages | 1
leaf_pages | 192
empty_pages | 0
deleted_pages | 0
avg_leaf_density | 89.74
leaf_fragmentation | 0
The output columns and their description.
| Column | Type | Description |
| version | integer | B-tree version number |
| tree_level | integer | Tree level of the root page |
| index_size | bigint | Total index size in bytes |
| root_block_no | bigint | Location of root page (zero if none) |
| internal_pages | bigint | Number of “internal” (upper-level) pages |
| leaf_pages | bigint | Number of leaf pages |
| empty_pages | bigint | Number of empty pages |
| deleted_pages | bigint | Number of deleted pages |
pgstatginindex(regclass): This function returns a record showing information about a GIN index.
For example:
SELECT * FROM pgstatginindex('table_gin_index');
pgstathashindex(regclass): This function returns a record showing information about a HASH index.
For example:
select * from pgstathashindex('table_hash_index');
In this blog, we have seen how the pgstattuple can be used for tuple-level analysis and to find bloated tables or vacuuming candidates. https://www.postgresql.org/docs/current/pgstattuple.html has some additional information on the extension.
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.
Jun
28
2023
28
2023
--
Public Schema Security Upgrade in PostgreSQL 15
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=#
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 ----------- 25 (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.