{"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:27:58","modified_gmt":"2022-10-04T07:27:58","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 &#8220;public&#8221; in PostgreSQL"},"content":{"rendered":"<p>PostgreSQL comes with the &#8220;public&#8221; 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 <a href=\"https:\/\/www.postgresql.org\/docs\/15\/release-15.html#id-1.11.6.5.5\" target=\"_blank\" rel=\"noopener\">release notes<\/a> for that change, it is this one: &#8220;Remove PUBLIC creation permission on the public schema (Noah Misch)&#8221;.<\/p>\n<p>In addition to the &#8220;public&#8221; schema, there is also the special role &#8220;public&#8221;. 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<pre class=\"brush: sql; gutter: true; first-line: 1\"> 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<p>There are some &#8220;usage&#8221; privileges, which seem to be fine, but there is also the &#8220;connect&#8221; privilege against the &#8220;postgres&#8221; and the &#8220;template1&#8221; databases (if you are wondering how to get these privileges out of the system, then you should check the various <a href=\"https:\/\/www.postgresql.org\/docs\/current\/functions-info.html\" target=\"_blank\" rel=\"noopener\">&#8220;has_***&#8221; functions in the documentation<\/a>).<\/p>\n<p>Last week we&#8217;ve been contacted by a customer who ran into this:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">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<p>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<pre class=\"brush: bash; gutter: true; first-line: 1\">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=&gt; \n<\/pre>\n<p>Works just fine, even if the connect privilege was revoked (of course I&#8217;ve modified <a href=\"https:\/\/www.postgresql.org\/docs\/current\/auth-pg-hba-conf.html\" target=\"_blank\" rel=\"noopener\">pg_hba.conf<\/a> before). Why is that? The reason is the &#8220;public&#8221; role. If we check the privileges of that role once more, we can see this:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [5]\"> 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<p>As the &#8220;public&#8221; 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<pre class=\"brush: sql; gutter: true; first-line: 1\">postgres=# revoke connect on database d from public;\nREVOKE\n<\/pre>\n<p>Once connect is revoked from public, the user is not anymore able to connect:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">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<p>Keep in mind, that privileges which are granted to &#8220;public&#8221; are valid for all users.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>PostgreSQL comes with the &#8220;public&#8221; 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&hellip;&nbsp;<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","neve_meta_reading_time":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[4133],"tags":[],"class_list":["post-19307","post","type-post","status-publish","format-standard","hentry","category-database"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.8 (Yoast SEO v28.8) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Be careful with &quot;public&quot; in PostgreSQL - dbi services | 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 &quot;public&quot; in PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"PostgreSQL comes with the &#8220;public&#8221; 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&hellip;&nbsp;\" \/>\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 services | Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/dbiservices\/\" \/>\n<meta property=\"article:published_time\" content=\"2022-10-04T07:27:58+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:site\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\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\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0b4548362cac3eeff8b3eaf329e3530a\"},\"headline\":\"Be careful with &#8220;public&#8221; in PostgreSQL\",\"datePublished\":\"2022-10-04T07:27:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/be-careful-with-public-in-postgresql\\\/\"},\"wordCount\":327,\"commentCount\":3,\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"articleSection\":[\"Database\"],\"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 services | Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2022-10-04T07:27:58+00:00\",\"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 &#8220;public&#8221; in PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi services Blog\",\"description\":\"#BeSharing\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"alternateName\":\"dbi Blog\",\"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\":\"Organization\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\",\"name\":\"dbi services\",\"alternateName\":\"dbi\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"width\":512,\"height\":512,\"caption\":\"dbi services\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/dbiservices\\\/\",\"https:\\\/\\\/x.com\\\/dbiservices\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/dbi-services\\\/\",\"https:\\\/\\\/www.youtube.com\\\/user\\\/dbiservices\"],\"description\":\"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.\",\"email\":\"info@dbi-services.com\",\"telephone\":\"+41 32 422 96 00\",\"legalName\":\"dbi services SA\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0b4548362cac3eeff8b3eaf329e3530a\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"caption\":\"Open source Team\"},\"description\":\"Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Be careful with \"public\" in PostgreSQL - dbi services | 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 &#8220;public&#8221; 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&hellip;&nbsp;","og_url":"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/","og_site_name":"dbi services | Blog","article_publisher":"https:\/\/www.facebook.com\/dbiservices\/","article_published_time":"2022-10-04T07:27:58+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_creator":"@dbiservices","twitter_site":"@dbiservices","twitter_misc":{"Written by":"Open source Team","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":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0b4548362cac3eeff8b3eaf329e3530a"},"headline":"Be careful with &#8220;public&#8221; in PostgreSQL","datePublished":"2022-10-04T07:27:58+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/be-careful-with-public-in-postgresql\/"},"wordCount":327,"commentCount":3,"publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"articleSection":["Database"],"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 services | Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2022-10-04T07:27:58+00:00","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 &#8220;public&#8221; in PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi services Blog","description":"#BeSharing","publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"alternateName":"dbi Blog","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":"Organization","@id":"https:\/\/www.dbi-services.com\/blog\/#organization","name":"dbi services","alternateName":"dbi","url":"https:\/\/www.dbi-services.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","width":512,"height":512,"caption":"dbi services"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/dbiservices\/","https:\/\/x.com\/dbiservices","https:\/\/www.linkedin.com\/company\/dbi-services\/","https:\/\/www.youtube.com\/user\/dbiservices"],"description":"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.","email":"info@dbi-services.com","telephone":"+41 32 422 96 00","legalName":"dbi services SA"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0b4548362cac3eeff8b3eaf329e3530a","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","caption":"Open source Team"},"description":"Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.","url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_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\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=19307"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/19307\/revisions"}],"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"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}