{"id":4329,"date":"2019-01-08T14:26:39","date_gmt":"2019-01-08T22:26:39","guid":{"rendered":"https:\/\/upon2020.com\/blog\/?p=4329"},"modified":"2019-01-08T14:26:39","modified_gmt":"2019-01-08T22:26:39","slug":"notes-on-postgresql-authentication","status":"publish","type":"post","link":"https:\/\/upon2020.com\/blog\/2019\/01\/notes-on-postgresql-authentication\/","title":{"rendered":"Notes on Postgresql authentication"},"content":{"rendered":"<p>My notes on what I learned, so I can re-read them when I need to. And because they might be useful for somebody else, too, I post them here.<\/p>\n<p>Architecture:<\/p>\n<ul>\n<li>Completely unlike MySQL, Postgresql authentication is (can be) related to the Linux user accounts on the system.<\/li>\n<li>By default, after a fresh installation, at least on <a href=\"https:\/\/archlinux.org\/\">Arch Linux<\/a>, all users on the system are allowed to access all Postgres databases. (Yes! Who would have thought that.) No passwords are required, because by default, Postgresql delegates authentication to the OS, and whoever runs a process on the system was authenticated by the OS.<\/li>\n<li>The authentication policies are configurable, through. They key files are:\n<ul>\n<li><code>\/var\/lib\/postgres\/data\/pg_hpa.conf<\/code> on Arch, <code>\/ubos\/lib\/postgres\/data\/pg_hpa.conf<\/code> on <a href=\"https:\/\/ubos.net\/\">UBOS<\/a>: defines which authentication policies apply to which users, which databases and where they are connecting from. You want to require passwords for all of them, except for the postgres user (so you can still log on). Something like this. <code>trust<\/code> means trust the OS that it has done its job, and <code>scram-sha-256<\/code> basically means use a password:\n<pre>local   all             postgres                                trust\r\nhost    all             postgres        127.0.0.1\/32            trust\r\nhost    all             postgres        ::1\/128                 trust\r\n\r\nlocal   all             all                                     scram-sha-256\r\nhost    all             all             127.0.0.1\/32            scram-sha-256\r\nhost    all             all             ::1\/128                 scram-sha-256\r\n<\/pre>\n<\/li>\n<li><code>\/var\/lib\/postgres\/data\/postgresql.conf<\/code> on Arch, <code>\/ubos\/lib\/postgres\/data\/postgresql.conf<\/code> on UBOS: defines, among many other things, how passwords are encrypted in the database. You want <code>password_encryption = scram-sha-256<\/code> (not <code>md5<\/code>). Note that this value must be set before setting any passwords, as they (obviously) cannot be automatically migrated.<\/li>\n<\/ul>\n<\/li>\n<li>To do something as user postgres, you now do <code>sudo -u postgres psql ...<\/code> and it won&#8217;t require a password. All other users, for all databases, require a password.<\/li>\n<li>Also, there are two kinds of permissions a user can have:\n<ul>\n<li>enumerated permissions on particular database object (&#8220;grant&#8221;).<\/li>\n<li>enumerated default permissions on database objects to be created in the future (&#8220;default privileges&#8221;).<\/li>\n<\/ul>\n<\/li>\n<li>Privileges on &#8220;tables&#8221; and on &#8220;sequences&#8221; are maintained separately, and have different enumerated values. And if you create a table using SQL, that will sometimes create a Postgres tables and one or more sequence objects. If you naively set the permissions on the table you just created, you might have missed the ones on the sequences that were also created.<\/li>\n<\/ul>\n<p>Operations:<\/p>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li>To create a new user interactively with a password: run <code>createuser -P &lt;name&gt;<\/code>. There are many other options.<\/li>\n<li>To create a new user from a script: run <code>createuser &lt;name&gt;<\/code> and then set a password: <code>ALTER ROLE \"&lt;name&gt;\" WITH PASSWORD '&lt;pass&gt;<\/code>&#8216; (note the different types of quotes)<\/li>\n<li>To give a user new privileges on an existing database object: <code>GRANT &lt;priv&gt; ON &lt;table&gt; TO &lt;name&gt;<\/code>.<\/li>\n<li>To give a user new default privileges which will be used for database objects created in the future: <code>ALTER DEFAULT PRIVILEGES IN SCHEMA \"public\" GRANT &lt;priv&gt; ON TABLES TO \"&lt;name&gt;<\/code> and <code>ALTER DEFAULT PRIVILEGES IN SCHEMA \"public\" GRANT USAGE ON SEQUENCES TO \"&lt;name&gt;<\/code>.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li>To determine the default privileges of a user: <code>\\dpp &lt;name&gt;<\/code><\/li>\n<li>To determine who gets to access a given table: <code>\\dp &lt;table&gt;<\/code><\/li>\n<\/ul>\n<p>Other useful Postgresl commands worth remembering:<\/p>\n<ul>\n<li style=\"list-style-type: none\">\n<ul>\n<li>Appending a <code>+<\/code> to the command shows (a little bit) more info<\/li>\n<li>Show list of databases: <code>\\l<\/code><\/li>\n<li>Use different database: <code>\\c &lt;name&gt;<\/code><\/li>\n<li>Show list of tables in the current database: <code>\\dt<\/code><\/li>\n<li>Show list of users: <code>\\du<\/code><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>My notes on what I learned, so I can re-read them when I need to. And because they might be useful for somebody else, too, I post them here. Architecture: Completely unlike MySQL, Postgresql authentication is (can be) related to the Linux user accounts on the system. By default, after a fresh installation, at least&hellip;<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"webmentions_disabled":false,"footnotes":""},"categories":[66],"tags":[],"class_list":["post-4329","post","type-post","status-publish","format-standard","hentry","category-technical","kind-article"],"kind":false,"_links":{"self":[{"href":"https:\/\/upon2020.com\/blog\/wp-json\/wp\/v2\/posts\/4329","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/upon2020.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/upon2020.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/upon2020.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/upon2020.com\/blog\/wp-json\/wp\/v2\/comments?post=4329"}],"version-history":[{"count":16,"href":"https:\/\/upon2020.com\/blog\/wp-json\/wp\/v2\/posts\/4329\/revisions"}],"predecessor-version":[{"id":4345,"href":"https:\/\/upon2020.com\/blog\/wp-json\/wp\/v2\/posts\/4329\/revisions\/4345"}],"wp:attachment":[{"href":"https:\/\/upon2020.com\/blog\/wp-json\/wp\/v2\/media?parent=4329"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/upon2020.com\/blog\/wp-json\/wp\/v2\/categories?post=4329"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/upon2020.com\/blog\/wp-json\/wp\/v2\/tags?post=4329"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}