{"id":6944,"date":"2016-01-27T10:19:23","date_gmt":"2016-01-27T09:19:23","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/avoiding-access-to-the-public-schema-in-postgresql\/"},"modified":"2016-01-27T10:19:23","modified_gmt":"2016-01-27T09:19:23","slug":"avoiding-access-to-the-public-schema-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/avoiding-access-to-the-public-schema-in-postgresql\/","title":{"rendered":"Avoiding access to the public schema in PostgreSQL"},"content":{"rendered":"<p>In PostgreSQL every database contains the public schema by default. Every user that gets created and can login is able to create objects there. Here is a little demo: I&#8217;ll create a new user named u1 which is allowed to login. No additional privileges are granted:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create user u1 login password 'u1';\nCREATE ROLE\npostgres=# c postgres u1\nYou are now connected to database \"postgres\" as user \"u1\".\n<\/pre>\n<p>From now on this user is able to connect to any database in the cluster and is able to create objects in the public schema:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=&gt; c postgres u1\nYou are now connected to database \"postgres\" as user \"u1\".\npostgres=&gt; create table t1 ( a int );\nCREATE TABLE\npostgres=&gt; insert into t1 values (1);\nINSERT 0 1\npostgres=&gt; select schemaname,tablename,tableowner \n             from pg_tables \n            where tablename = 't1';\n schemaname | tablename | tableowner \n------------+-----------+------------\n public     | t1        | u1\n(1 row)\n\npostgres=&gt; c edb u1\nYou are now connected to database \"edb\" as user \"u1\".\nedb=&gt; create table t1 ( a int );\nCREATE TABLE\nedb=&gt; select schemaname,tablename,tableowner \n        from pg_tables \n       where tablename = 't1';\n schemaname | tablename | tableowner \n------------+-----------+------------\n public     | t1        | u1\n(1 row)\n<\/pre>\n<p>This is probably not what you want as such a user can fill your database and therefore may cause major issues (performance, disk full etc. ). How can we avoid that?<\/p>\n<p>One way to do it is to revoke everything from public:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# revoke all on schema public from public;\nREVOKE\n<\/pre>\n<p>If we now re-connect to the postgres database and try to create a table this will fail:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# c postgres u1\nYou are now connected to database \"postgres\" as user \"u1\".\npostgres=&gt; create table t2 ( a int );\nERROR:  no schema has been selected to create in\npostgres=&gt; create table public.t2 ( a int );\nERROR:  permission denied for schema public\n<\/pre>\n<p>The issue with this approach is that if we connect to another database we are still allowed to create tables:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# c edb u1\nYou are now connected to database \"edb\" as user \"u1\".\nedb=&gt; create table t2 ( a int );\nCREATE TABLE\nedb=&gt; \n<\/pre>\n<p>Only when we do the same revoke in this database &#8230;<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# c edb\nYou are now connected to database \"edb\" as user \"enterprisedb\".\nedb=# revoke all on schema public from public;    \nREVOKE\n<\/pre>\n<p>.. we are not able to create tables anymore:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nedb=# c edb u1\nYou are now connected to database \"edb\" as user \"u1\".\nedb=&gt; create table t3 ( a int );\nERROR:  no schema has been selected to create in\nedb=&gt; create table public.t3 ( a int );\nERROR:  permission denied for schema public\n<\/pre>\n<p>Seems a little bit complicated. What else can we do? As every new database is created from template1 by default we can do our revokes there:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# c template1\nYou are now connected to database \"template1\" as user \"enterprisedb\".\ntemplate1=# revoke all on schema public from public;\nREVOKE\n<\/pre>\n<p>Every new database should have this included already:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create database db1;\nCREATE DATABASE\npostgres=# c db1 u1\nYou are now connected to database \"db1\" as user \"u1\".\ndb1=&gt; create table t1 ( a int );\nERROR:  no schema has been selected to create in\ndb1=&gt; create table public.t1 ( a int ); \nERROR:  permission denied for schema public\ndb1=&gt; \n<\/pre>\n<p>Much better. <\/p>\n<p>But remember that it is usually not that easy to connect at all because of pg_hba.conf. When we try to connect from outside:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\ndwe@dwe:~$ psql -h 192.168.22.243 -p 5444 -U u1 db1\npsql: FATAL:  no pg_hba.conf entry for host \"192.168.22.1\", user \"u1\", database \"db1\", SSL off\n<\/pre>\n<p>We have much more control here. If we do not grant access to a specific database we will not be able to connect. So we might grant access to the db1 database but not to the postgres database by adding this line to pg_hba.conf:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nhost     db1             u1                  192.168.22.1\/32         md5\n<\/pre>\n<p>Once the server was reloaded or restarted we are able to connect:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\ndwe@dwe:~$ psql -h 192.168.22.243 -p 5444 -U u1 db1\nPassword for user u1: \npsql (9.3.10, server 9.4.5.12)\nWARNING: psql major version 9.3, server major version 9.4.\n         Some psql features might not work.\nType \"help\" for help.\n\ndb1=&gt; \n<\/pre>\n<p>But we are not able to connect to any other database:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\ndwe@dwe:~$ psql -h 192.168.22.243 -p 5444 -U u1 postgres\npsql: FATAL:  no pg_hba.conf entry for host \"192.168.22.1\", user \"u1\", database \"postgres\", SSL off\ndwe@dwe:~$ psql -h 192.168.22.243 -p 5444 -U u1 template1\npsql: FATAL:  no pg_hba.conf entry for host \"192.168.22.1\", user \"u1\", database \"template1\", SSL off\n<\/pre>\n<p>Conclusion: Always make sure that you allow connections from trusted sources only ( via pg_hba.conf ) and think about the public schema before granting access.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In PostgreSQL every database contains the public schema by default. Every user that gets created and can login is able to create objects there. Here is a little demo: I&#8217;ll create a new user named u1 which is allowed to login. No additional privileges are granted: postgres=# create user u1 login password &#8216;u1&#8217;; CREATE ROLE [&hellip;]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77,25],"type_dbi":[],"class_list":["post-6944","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql","tag-security"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Avoiding access to the public schema 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\/avoiding-access-to-the-public-schema-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Avoiding access to the public schema in PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"In PostgreSQL every database contains the public schema by default. Every user that gets created and can login is able to create objects there. Here is a little demo: I&#8217;ll create a new user named u1 which is allowed to login. No additional privileges are granted: postgres=# create user u1 login password &#039;u1&#039;; CREATE ROLE [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/avoiding-access-to-the-public-schema-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-27T09:19:23+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=\"4 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\/avoiding-access-to-the-public-schema-in-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/avoiding-access-to-the-public-schema-in-postgresql\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Avoiding access to the public schema in PostgreSQL\",\"datePublished\":\"2016-01-27T09:19:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/avoiding-access-to-the-public-schema-in-postgresql\/\"},\"wordCount\":331,\"commentCount\":0,\"keywords\":[\"PostgreSQL\",\"Security\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/avoiding-access-to-the-public-schema-in-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/avoiding-access-to-the-public-schema-in-postgresql\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/avoiding-access-to-the-public-schema-in-postgresql\/\",\"name\":\"Avoiding access to the public schema in PostgreSQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-01-27T09:19:23+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/avoiding-access-to-the-public-schema-in-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/avoiding-access-to-the-public-schema-in-postgresql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/avoiding-access-to-the-public-schema-in-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Avoiding access to the public schema 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 &amp;\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":"Avoiding access to the public schema 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\/avoiding-access-to-the-public-schema-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"Avoiding access to the public schema in PostgreSQL","og_description":"In PostgreSQL every database contains the public schema by default. Every user that gets created and can login is able to create objects there. Here is a little demo: I&#8217;ll create a new user named u1 which is allowed to login. No additional privileges are granted: postgres=# create user u1 login password 'u1'; CREATE ROLE [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/avoiding-access-to-the-public-schema-in-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2016-01-27T09:19:23+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/avoiding-access-to-the-public-schema-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/avoiding-access-to-the-public-schema-in-postgresql\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Avoiding access to the public schema in PostgreSQL","datePublished":"2016-01-27T09:19:23+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/avoiding-access-to-the-public-schema-in-postgresql\/"},"wordCount":331,"commentCount":0,"keywords":["PostgreSQL","Security"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/avoiding-access-to-the-public-schema-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/avoiding-access-to-the-public-schema-in-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/avoiding-access-to-the-public-schema-in-postgresql\/","name":"Avoiding access to the public schema in PostgreSQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-01-27T09:19:23+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/avoiding-access-to-the-public-schema-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/avoiding-access-to-the-public-schema-in-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/avoiding-access-to-the-public-schema-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Avoiding access to the public schema 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 &amp;\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\/6944","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=6944"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/6944\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=6944"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=6944"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=6944"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=6944"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}