{"id":12401,"date":"2026-07-16T18:46:43","date_gmt":"2026-07-16T18:46:43","guid":{"rendered":"https:\/\/putridparrot.com\/blog\/?p=12401"},"modified":"2026-07-16T18:46:43","modified_gmt":"2026-07-16T18:46:43","slug":"creating-and-setting-up-a-postgres-user","status":"publish","type":"post","link":"https:\/\/putridparrot.com\/blog\/creating-and-setting-up-a-postgres-user\/","title":{"rendered":"Creating and setting up a Postgres user"},"content":{"rendered":"<p>In some cases we want to create a readonly user (for example) for our database access, so let&#8217;s walk through the steps etc. to create such a use in Postgresql<\/p>\n<p>Before we start, let&#8217;s see how we can list the existing users<\/p>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">\r\nSELECT rolname\r\nFROM pg_roles\r\nORDER BY rolname;\r\n<\/pre>\n<ul>\n<li><strong>Create the user<\/strong>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">\r\nCREATE USER readonly_user WITH PASSWORD &#039;&lt;password&gt;&#039;;\r\n<\/pre>\n<\/li>\n<li><strong>Allow the user to connect to the database using<\/strong>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">\r\nGRANT CONNECT ON DATABASE &lt;database-name&gt; TO readonly_user;\r\n<\/pre>\n<\/li>\n<li><strong>Grant usage on the schema<\/strong>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">\r\nGRANT USAGE ON SCHEMA &lt;schema-name&gt; TO readonly_user;\r\n<\/pre>\n<\/li>\n<li><strong>Grant SELECT on all existing tables<\/strong>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">\r\nGRANT SELECT ON ALL TABLES IN SCHEMA &lt;schema-name&gt; TO readonly_user;\r\n<\/pre>\n<\/li>\n<li><strong>Ensure future tables are also readable<\/strong>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">\r\nALTER DEFAULT PRIVILEGES IN SCHEMA &lt;schema-name&gt;\r\nGRANT SELECT ON TABLES TO readonly_user;\r\n<\/pre>\n<\/li>\n<\/ul>\n<p>If we want to apply a readonly user to specific tables then we can do the following. Assume we&#8217;ve create the user as above and connected to a database as well as granting access on a schema, then&#8230;<\/p>\n<ul>\n<li><strong>Grant access to your tables<\/strong>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">\r\nGRANT SELECT ON TABLE &lt;schema-name&gt;.&lt;table-name&gt;TO readonly_user;\r\n<\/pre>\n<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>In some cases we want to create a readonly user (for example) for our database access, so let&#8217;s walk through the steps etc. to create such a use in Postgresql Before we start, let&#8217;s see how we can list the existing users SELECT rolname FROM pg_roles ORDER BY rolname; Create the user CREATE USER readonly_user [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[773],"tags":[],"class_list":["post-12401","post","type-post","status-publish","format-standard","hentry","category-postgresql-2"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/12401","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/comments?post=12401"}],"version-history":[{"count":2,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/12401\/revisions"}],"predecessor-version":[{"id":12403,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/12401\/revisions\/12403"}],"wp:attachment":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/media?parent=12401"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/categories?post=12401"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/tags?post=12401"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}