{"id":19307,"date":"2022-10-04T09:27:58","date_gmt":"2022-10-04T07:27:58","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=19307"},"modified":"2022-10-04T09:36:15","modified_gmt":"2022-10-04T07:36:15","slug":"be-careful-with-public-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/","title":{"rendered":"Be careful with “public” in PostgreSQL"},"content":{"rendered":"

PostgreSQL comes with the “public” schema by default and we always recommend to drop it for PostgreSQL versions up to 14. The reason is, that everybody who is able to connect to a database, by default, can create objects in the public schema. This will not anymore be possible starting with PostgreSQL 15. You can check the release notes<\/a> for that change, it is this one: “Remove PUBLIC creation permission on the public schema (Noah Misch)”.<\/p>\n

<\/p>\n

In addition to the “public” schema, there is also the special role “public”. This role can be used to grant something to all other roles, but this also comes with some misunderstanding of what is possible by default. By default, the public role has this permissions:<\/p>\n

 username | object_type |    object_name     |            array            \n----------+-------------+--------------------+-----------------------------\n public   | DATABASE    | postgres           | {CONNECT,TEMPORARY}\n public   | DATABASE    | template1          | {CONNECT}\n public   | LANGUAGE    | internal           | {USAGE}\n public   | LANGUAGE    | c                  | {USAGE}\n public   | LANGUAGE    | sql                | {USAGE}\n public   | LANGUAGE    | plpgsql            | {USAGE}\n public   | SCHEMA      | pg_catalog         | {USAGE}\n public   | SCHEMA      | public             | {USAGE}\n public   | SCHEMA      | information_schema | {USAGE}\n(9 rows)\n<\/pre>\n
There are some “usage” privileges, which seem to be fine, but there is also the “connect” privilege against the “postgres” and the “template1” databases (if you are wondering how to get these privileges out of the system, then you should check the various “has_***” functions in the documentation<\/a>).<\/p>\n
Last week we’ve been contacted by a customer who ran into this:<\/p>\n
postgres=# create database d;\nCREATE DATABASE\npostgres=# create user u with login password 'u';\nCREATE ROLE\npostgres=# revoke connect on database d from u;\nREVOKE\n<\/pre>\n
Simple setup: One database, one user and a revoke of the connect privilege from that database from the user. Will that user be able to connect to the database? Simple test from outside the database host:<\/p>\n
dwe@ltdwe:~$ psql -h 192.168.100.241 -p 5432 -U u d\npsql (14.5, server 16devel)\nWARNING: psql major version 14, server major version 16.\n         Some psql features might not work.\nType \"help\" for help.\n\nd=> \n<\/pre>\n
Works just fine, even if the connect privilege was revoked (of course I’ve modified pg_hba.conf<\/a> before). Why is that? The reason is the “public” role. If we check the privileges of that role once more, we can see this:<\/p>\n
 username | object_type |    object_name     |            array\n----------+-------------+--------------------+-----------------------------\n public   | DATABASE    | postgres           | {CONNECT,TEMPORARY}\n public   | DATABASE    | template1          | {CONNECT}\n public   | DATABASE    | d                  | {CONNECT,TEMPORARY}\n public   | LANGUAGE    | internal           | {USAGE}\n public   | LANGUAGE    | c                  | {USAGE}\n public   | LANGUAGE    | sql                | {USAGE}\n public   | LANGUAGE    | plpgsql            | {USAGE}\n public   | SCHEMA      | pg_catalog         | {USAGE}\n public   | SCHEMA      | public             | {USAGE}\n public   | SCHEMA      | information_schema | {USAGE}\n(10 rows)\n<\/pre>\n
As the “public” role has the connect privilege by default, this is automatically available to all other users, and this is the reason why it still works. What you should do, is this:<\/p>\n
postgres=# revoke connect on database d from public;\nREVOKE\n<\/pre>\n
Once connect is revoked from public, the user is not anymore able to connect:<\/p>\n
dwe@ltdwe:~$ psql -h 192.168.100.241 -p 5432 -U u d\npsql: error: connection to server at \"192.168.100.241\", port 5432 failed: FATAL:  permission denied for database \"d\"\nDETAIL:  User does not have CONNECT privilege.\n<\/pre>\n
Keep in mind, that privileges which are granted to “public” are valid for all users.<\/p>\n","protected":false},"excerpt":{"rendered":"
PostgreSQL comes with the “public” schema by default and we always recommend to drop it for PostgreSQL versions up to 14. The reason is, that everybody who is able to connect to a database, by default, can create objects in the public schema. This will not anymore be possible starting with PostgreSQL 15. You can […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[2602],"type_dbi":[],"class_list":["post-19307","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql-2"],"acf":[],"yoast_head":"\nBe careful with "public" in PostgreSQL - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Be careful with "public" in PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"PostgreSQL comes with the “public” schema by default and we always recommend to drop it for PostgreSQL versions up to 14. The reason is, that everybody who is able to connect to a database, by default, can create objects in the public schema. This will not anymore be possible starting with PostgreSQL 15. You can […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-10-04T07:27:58+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-10-04T07:36:15+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Be careful with “public” in PostgreSQL\",\"datePublished\":\"2022-10-04T07:27:58+00:00\",\"dateModified\":\"2022-10-04T07:36:15+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/\"},\"wordCount\":327,\"commentCount\":3,\"keywords\":[\"postgresql\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/\",\"name\":\"Be careful with \\\"public\\\" in PostgreSQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2022-10-04T07:27:58+00:00\",\"dateModified\":\"2022-10-04T07:36:15+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Be careful with “public” in PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Be careful with \"public\" in PostgreSQL - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"Be careful with \"public\" in PostgreSQL","og_description":"PostgreSQL comes with the “public” schema by default and we always recommend to drop it for PostgreSQL versions up to 14. The reason is, that everybody who is able to connect to a database, by default, can create objects in the public schema. This will not anymore be possible starting with PostgreSQL 15. You can […]","og_url":"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2022-10-04T07:27:58+00:00","article_modified_time":"2022-10-04T07:36:15+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Be careful with “public” in PostgreSQL","datePublished":"2022-10-04T07:27:58+00:00","dateModified":"2022-10-04T07:36:15+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/"},"wordCount":327,"commentCount":3,"keywords":["postgresql"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/","name":"Be careful with \"public\" in PostgreSQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2022-10-04T07:27:58+00:00","dateModified":"2022-10-04T07:36:15+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Be careful with “public” in PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/19307","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=19307"}],"version-history":[{"count":12,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/19307\/revisions"}],"predecessor-version":[{"id":19319,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/19307\/revisions\/19319"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=19307"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=19307"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=19307"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=19307"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}