Notes on Postgresql authentication

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 on Arch Linux, 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.
The authentication policies are configurable, through. They key files are:
- /var/lib/postgres/data/pg_hpa.conf on Arch, /ubos/lib/postgres/data/pg_hpa.conf on UBOS: 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. trust means trust the OS that it has done its job, and scram-sha-256 basically means use a password:
```
local   all             postgres                                trust
host    all             postgres        127.0.0.1/32            trust
host    all             postgres        ::1/128                 trust

local   all             all                                     scram-sha-256
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
```
- /var/lib/postgres/data/postgresql.conf on Arch, /ubos/lib/postgres/data/postgresql.conf on UBOS: defines, among many other things, how passwords are encrypted in the database. You want password_encryption = scram-sha-256 (not md5). Note that this value must be set before setting any passwords, as they (obviously) cannot be automatically migrated.
To do something as user postgres, you now do sudo -u postgres psql ... and it won’t require a password. All other users, for all databases, require a password.
Also, there are two kinds of permissions a user can have:
- enumerated permissions on particular database object (“grant”).
- enumerated default permissions on database objects to be created in the future (“default privileges”).
Privileges on “tables” and on “sequences” 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.

Operations:

- To create a new user interactively with a password: run createuser -P <name>. There are many other options.
- To create a new user from a script: run createuser <name> and then set a password: ALTER ROLE "<name>" WITH PASSWORD '<pass>‘ (note the different types of quotes)
- To give a user new privileges on an existing database object: GRANT <priv> ON <table> TO <name>.
- To give a user new default privileges which will be used for database objects created in the future: ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT <priv> ON TABLES TO "<name> and ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT USAGE ON SEQUENCES TO "<name>.

To determine the default privileges of a user: \dpp <name>
To determine who gets to access a given table: \dp <table>

Other useful Postgresl commands worth remembering:

- Appending a + to the command shows (a little bit) more info
- Show list of databases: \l
- Use different database: \c <name>
- Show list of tables in the current database: \dt
- Show list of users: \du