The variable "K3S_DATASTORE_ENDPOINT" not resolved yet.

[luvres]

The problem is that even if you define the name of the database, k3s v1.28.4+k3s1 onwards only recognizes the "postgres" database. I'm currently using v1.29.0+k3s1 and adjusted it to use the "postgres" database. Even version v1.28.3+k3s1 recognizes the database configuration passed in the K3S_DATASTORE_ENDPOINT variable

curl -sfL https://get.k3s.io | \
...
K3S_DATASTORE_ENDPOINT="postgres://USER:PASS@HOSTNAME:5432/db_k3s?sslmode=verify-full" \
...
INSTALL_K3S_VERSION=v1.28.5+k3s1 sh -s - server

In this example "db_k3s" is ignored and considers "postgres"

[brandond]

That is not what's happening. K3s always connects to the postgres database initially, in order to test for existence of the k3s database. See https://github.com/k3s-io/k3s/issues/9033#issuecomment-1850412488

[th0th]

That is not what's happening. K3s always connects to the postgres database initially, in order to test for existence of the k3s database. See #9033 (comment)

Is this new? Because I never had a line allowing k3s to access the database postgres in my pg_hba.conf, and it was working before?

[brandond]

Yes, under various circumstances. Prior to https://github.com/k3s-io/kine/pull/241 it would connect to that database temporarily, in order to create the kine database if it did not exist. Now it connects to that database in order to check the existence of the kine database in order to avoid creating it if it already exists. There is always some situation under which we may need to use it.

[th0th]

Thanks for the information @brandond, but I didn't fully get it. Should k3s's postgresql user have access to postgres database as well? Or only allowing connection in pg_hba.conf but not giving access enough?

[brandond]

Not sure, please let us know what you find out!

[thpham]

Hello, in my situation, I rely on a managed postgresql instance which doesn't allow access to postgres database and this broke my ability to upgrade k3s to a newer version than v1.28.3-k3s1. This looks to me like a regression , isn't it ?

[brandond]

doesn't allow access to postgres database

It does not and cannot allow access to it, or you have not taken steps to grant access to it? As per https://github.com/k3s-io/k3s/issues/9033#issuecomment-1850412488 the upstream postgres docs indicate that access to the postgres default database should be safe to assume.

[ringerc]

As a postgres contributor, I would never recommend that any application ignore the configured connection string and connect to the postgres database.

Yes, it's commonly used by utilities, but apps should accept what they are given; they cannot assume the administrator will grant the user they are connecting as the privileges to connect to DBs other than the one specified to the application.

An application should run with least privilege. Having to configure the application with access to the postgres database and a role with CREATEDB rights is giving excessive privileges. It's a nice feature for apps to offer for "easy mode" setup, but it should be done by providing a separate "admin connection string" to the app for one time setup, and that connection string must not be stored/retained once used for initialization. It should be entirely optional too; the admin should be able to supply the app with a connection string that can only access the one database the app is allowed to use, and it should just use that.

When I deploy an application I expect it to use the credentials I supply to connect to the database I give it access to.

I will generally have done admin steps first, connecting to an admin db like postgres as a role with CREATEROLE and CREATEDB rights (such as superuser) and running:

CREATE ROLE myapp WITH LOGIN NOSUPERUSER NOCREATEROLE NOCREATEDB;

-- if using psql, use \password to set the password
\password k3s
-- otherwise, client-side hash your password and use
ALTER ROLE k3s WITH ENCRYPTED PASSWORD 'SCRAM-SHA-256$4096:...redacted...';

CREATE DATABASE myapp WITH OWNER myapp;
GRANT CONNECT, CREATE, TEMP ON DATABASE myapp TO myapp;

... then I just give the application a connection string/URI to connect as myapp user to myapp database, and expect it to use that.

---

Even this gives the app excessive privileges. If possible the application should support being configured with a "database admin connection string" that has database owner rights, for schema migrations etc. It should usually run with a less privileged "user connection string" that has GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public TO myapp; (for each schema it uses) and similar rights for sequences etc, so it does not have the rights to create tables, drop tables, modify tables, etc. It can automate this permissions management as part of its schema migrations run from the database owner connection.

---

For people who encounter this issue and need to work around the k3s issue, you should be able to grant your k3s user limited rights to the postgres db to limit the hazard. Connect to the postgres db and:

REVOKE CREATE ON SCHEMA public FROM public;

GRANT CONNECT ON DATABASE postgres TO k3s;

(The REVOKE may upset apps that assume they can do what they want in the public schema of the postgres db, but any such apps need fixing anyway.)