Skip to content

Commit 983bc6b

Browse files
jsilvelaarmrumnencia
authored
fix(roles): prevent postgres from leaking role passwords in logs (#9950)
When PostgreSQL executes CREATE/ALTER ROLE with a cleartext password, it can log the password via log_statement and log_min_error_statement. Wrap password-carrying role statements in a transaction that suppresses logging with SET LOCAL, preventing leakage in both success and failure scenarios. Closes #9799 Signed-off-by: Jaime Silvela <[email protected]> Signed-off-by: Armando Ruocco <[email protected]> Signed-off-by: Marco Nenciarini <[email protected]> Co-authored-by: Armando Ruocco <[email protected]> Co-authored-by: Marco Nenciarini <[email protected]>
1 parent 0c0fa23 commit 983bc6b

8 files changed

Lines changed: 199 additions & 30 deletions

File tree

‎.wordlist-en-custom.txt‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -696,6 +696,7 @@ cisecurity
696696
claimRef
697697
className
698698
classid
699+
cleartext
699700
cli
700701
clientCA
701702
clientCASecret

‎docs/src/declarative_role_management.md‎

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -179,6 +179,23 @@ stringData:
179179
password: SCRAM-SHA-256$<iteration count>:<salt>$<StoredKey>:<ServerKey>
180180
```
181181

182+
### Safety when transmitting cleartext passwords
183+
184+
While role passwords are safely managed in Kubernetes using Secrets,
185+
there is still a risk on the PostgreSQL side. If creating/altering a role with
186+
password, PostgreSQL may print the password as part of the query statement
187+
in some `postgres` logs, as mentioned in the [PostgreSQL documentation](https://www.postgresql.org/docs/current/sql-createrole.html):
188+
189+
> The password will be transmitted to the server in cleartext, and it might
190+
> also be logged in the client's command history or the server log
191+
192+
CloudNativePG adds a safety layer by temporarily suppressing both statement
193+
logging (`log_statement`) and error statement logging
194+
(`log_min_error_statement`) for any CREATE or ALTER operation on a role with
195+
password, thus preventing leakage in both success and failure scenarios.
196+
The Status section of the cluster does not print the query statement for any
197+
managed role operation.
198+
182199
## Unrealizable role configurations
183200

184201
In PostgreSQL, in some cases, commands cannot be honored by the database and

‎docs/src/security.md‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -592,6 +592,14 @@ versions it is set to `md5`.
592592
`postgres` user to `NULL` (de facto disabling remote access through password authentication).
593593
:::
594594

595+
:::info[Important]
596+
PostgreSQL may include cleartext role passwords in its logs for some role
597+
operations, as mentioned in the [PostgreSQL documentation](https://www.postgresql.org/docs/current/sql-createrole.html).
598+
CloudNativePG ensures that `postgres` logging (both statement logging and
599+
error statement logging) is temporarily suppressed for CREATE/ALTER ROLE
600+
operations with passwords, thus preventing any password leakage.
601+
:::
602+
595603
See the ["Secrets" section in the "Connecting from an application" page](applications.md#secrets) for more information.
596604

597605
You can use those files to configure application access to the database.

‎internal/management/controller/instance_controller.go‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1307,7 +1307,7 @@ func (r *InstanceReconciler) reconcileUser(ctx context.Context, username string,
13071307
return fmt.Errorf("wrong username '%v' in secret, expected '%v'", usernameFromSecret, username)
13081308
}
13091309

1310-
err = postgresutils.SetUserPassword(username, password, db)
1310+
err = postgresutils.SetUserPassword(ctx, username, password, db)
13111311
if err != nil {
13121312
return err
13131313
}

‎internal/management/controller/roles/postgres.go‎

Lines changed: 22 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,8 @@ import (
3030
"github.com/jackc/pgx/v5"
3131
"github.com/jackc/pgx/v5/pgtype"
3232
"github.com/lib/pq"
33+
34+
postgresutils "github.com/cloudnative-pg/cloudnative-pg/pkg/management/postgres/utils"
3335
)
3436

3537
// List the available roles excluding all the roles that start with `pg_`
@@ -100,6 +102,14 @@ func List(ctx context.Context, db *sql.DB) ([]DatabaseRole, error) {
100102
return roles, nil
101103
}
102104

105+
func executeRoleStatement(ctx context.Context, db *sql.DB, statement string, suppressLogging bool) error {
106+
if suppressLogging {
107+
return postgresutils.ExecWithSuppressedLogging(ctx, db, statement)
108+
}
109+
_, err := db.ExecContext(ctx, statement)
110+
return err
111+
}
112+
103113
// Update the role
104114
func Update(ctx context.Context, db *sql.DB, role DatabaseRole) error {
105115
contextLog := log.FromContext(ctx).WithName("roles_reconciler")
@@ -109,15 +119,15 @@ func Update(ctx context.Context, db *sql.DB, role DatabaseRole) error {
109119
}
110120
var query strings.Builder
111121

112-
query.WriteString(fmt.Sprintf("ALTER ROLE %s ", pgx.Identifier{role.Name}.Sanitize()))
122+
query.WriteString(fmt.Sprintf("ALTER ROLE %s", pgx.Identifier{role.Name}.Sanitize()))
113123
appendRoleOptions(role, &query)
124+
// Log before appending password to prevent password leakage in operator logs
114125
contextLog.Debug("Updating role", "role", role.Name, "query", query.String())
115126
// NOTE: always apply the password update. Since the transaction ID of the role
116127
// will change no matter what, the next reconciliation cycle we would update the password
117128
appendPasswordOption(role, &query)
118129

119-
_, err := db.ExecContext(ctx, query.String())
120-
if err != nil {
130+
if err := executeRoleStatement(ctx, db, query.String(), roleHasPassword(role)); err != nil {
121131
return wrapErr(err)
122132
}
123133
return nil
@@ -133,16 +143,17 @@ func Create(ctx context.Context, db *sql.DB, role DatabaseRole) error {
133143
}
134144

135145
var query strings.Builder
136-
query.WriteString(fmt.Sprintf("CREATE ROLE %s ", pgx.Identifier{role.Name}.Sanitize()))
146+
query.WriteString(fmt.Sprintf("CREATE ROLE %s", pgx.Identifier{role.Name}.Sanitize()))
137147
appendRoleOptions(role, &query)
138148
appendInRoleOptions(role, &query)
139-
appendPasswordOption(role, &query)
149+
// Log before appending password to prevent password leakage in operator logs
140150
contextLog.Debug("Creating", "query", query.String())
151+
appendPasswordOption(role, &query)
141152

142153
// NOTE: defensively we might think of doing CREATE ... IF EXISTS
143154
// but at least during development, we want to catch the error
144155
// Even after, this may be "the kubernetes way"
145-
if _, err := db.ExecContext(ctx, query.String()); err != nil {
156+
if err := executeRoleStatement(ctx, db, query.String(), roleHasPassword(role)); err != nil {
146157
return wrapErr(err)
147158
}
148159

@@ -310,7 +321,7 @@ func appendInRoleOptions(role DatabaseRole, query *strings.Builder) {
310321
quotedInRoles[i] = pgx.Identifier{inRole}.Sanitize()
311322
}
312323

313-
fmt.Fprintf(query, " IN ROLE %s ", strings.Join(quotedInRoles, ","))
324+
fmt.Fprintf(query, " IN ROLE %s", strings.Join(quotedInRoles, ","))
314325
}
315326
}
316327

@@ -360,6 +371,10 @@ func appendRoleOptions(role DatabaseRole, query *strings.Builder) {
360371
fmt.Fprintf(query, " CONNECTION LIMIT %d", role.ConnectionLimit)
361372
}
362373

374+
func roleHasPassword(role DatabaseRole) bool {
375+
return !role.ignorePassword && role.password.Valid
376+
}
377+
363378
func appendPasswordOption(role DatabaseRole, query *strings.Builder) {
364379
switch {
365380
case role.ignorePassword:

‎internal/management/controller/roles/postgres_test.go‎

Lines changed: 90 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -115,6 +115,9 @@ var _ = Describe("Postgres RoleManager implementation test", func() {
115115
"NOSUPERUSER CONNECTION LIMIT 2 IN ROLE \"pg_monitoring\" PASSWORD 'myPassword' VALID UNTIL '2100-01-01 00:00:00Z'",
116116
wantedRole.Name)
117117

118+
wantedLogStatementSuppressionStmt := "SET LOCAL log_statement = 'none'"
119+
wantedLogPreventionStmt := "SET LOCAL log_min_error_statement = 'PANIC'"
120+
118121
wantedRoleWithoutValidUntilExpectedCrtStmt := fmt.Sprintf(
119122
"CREATE ROLE \"%s\" BYPASSRLS NOCREATEDB CREATEROLE NOINHERIT LOGIN NOREPLICATION "+
120123
"NOSUPERUSER CONNECTION LIMIT 2 IN ROLE \"pg_monitoring\" PASSWORD 'myPassword'",
@@ -134,7 +137,12 @@ var _ = Describe("Postgres RoleManager implementation test", func() {
134137
wantedRole.Name, pq.QuoteLiteral(wantedRole.Comment))
135138

136139
wantedRoleExpectedAltStmt := fmt.Sprintf(
137-
"ALTER ROLE \"%s\" BYPASSRLS NOCREATEDB CREATEROLE NOINHERIT LOGIN NOREPLICATION NOSUPERUSER CONNECTION LIMIT 2 ",
140+
"ALTER ROLE \"%s\" BYPASSRLS NOCREATEDB CREATEROLE NOINHERIT LOGIN NOREPLICATION NOSUPERUSER CONNECTION LIMIT 2 "+
141+
"VALID UNTIL '2100-01-01 00:00:00Z'",
142+
wantedRole.Name)
143+
wantedRoleExpectedAltWithPasswordStmt := fmt.Sprintf(
144+
"ALTER ROLE \"%s\" BYPASSRLS NOCREATEDB CREATEROLE NOINHERIT LOGIN NOREPLICATION NOSUPERUSER CONNECTION LIMIT 2 "+
145+
"PASSWORD 'myPassword' VALID UNTIL '2100-01-01 00:00:00Z'",
138146
wantedRole.Name)
139147
unWantedRoleExpectedDelStmt := fmt.Sprintf("DROP ROLE \"%s\"", unWantedRole.Name)
140148

@@ -248,12 +256,18 @@ var _ = Describe("Postgres RoleManager implementation test", func() {
248256
Expect(err).To(HaveOccurred())
249257
Expect(errors.Unwrap(err)).To(BeEquivalentTo(dbError))
250258
})
251-
It("Create will send a correct CREATE with password to the DB", func(ctx context.Context) {
259+
It("Create with password will send CREATE and prevent Postgres from logging the query", func(ctx context.Context) {
252260
db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))
253261
Expect(err).ToNot(HaveOccurred())
254262

263+
mock.ExpectBegin()
264+
mock.ExpectExec(wantedLogStatementSuppressionStmt).
265+
WillReturnResult(sqlmock.NewResult(0, 1))
266+
mock.ExpectExec(wantedLogPreventionStmt).
267+
WillReturnResult(sqlmock.NewResult(0, 1))
255268
mock.ExpectExec(wantedRoleWithPassExpectedCrtStmt).
256269
WillReturnResult(sqlmock.NewResult(2, 3))
270+
mock.ExpectCommit()
257271

258272
mock.ExpectExec(wantedRoleCommentStmt).
259273
WillReturnResult(sqlmock.NewResult(2, 3))
@@ -268,8 +282,14 @@ var _ = Describe("Postgres RoleManager implementation test", func() {
268282
db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))
269283
Expect(err).ToNot(HaveOccurred())
270284

285+
mock.ExpectBegin()
286+
mock.ExpectExec(wantedLogStatementSuppressionStmt).
287+
WillReturnResult(sqlmock.NewResult(0, 1))
288+
mock.ExpectExec(wantedLogPreventionStmt).
289+
WillReturnResult(sqlmock.NewResult(0, 1))
271290
mock.ExpectExec(wantedRoleWithoutValidUntilExpectedCrtStmt).
272291
WillReturnResult(sqlmock.NewResult(2, 3))
292+
mock.ExpectCommit()
273293

274294
mock.ExpectExec(wantedRoleCommentStmt).
275295
WillReturnResult(sqlmock.NewResult(2, 3))
@@ -282,6 +302,29 @@ var _ = Describe("Postgres RoleManager implementation test", func() {
282302
err = Create(ctx, db, dbRole)
283303
Expect(err).ShouldNot(HaveOccurred())
284304
})
305+
It("Create with password will rollback CREATE if there is an error", func(ctx context.Context) {
306+
db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))
307+
Expect(err).ToNot(HaveOccurred())
308+
309+
mock.ExpectBegin()
310+
mock.ExpectExec(wantedLogStatementSuppressionStmt).
311+
WillReturnResult(sqlmock.NewResult(0, 1))
312+
mock.ExpectExec(wantedLogPreventionStmt).
313+
WillReturnResult(sqlmock.NewResult(0, 1))
314+
dbError := errors.New("kaboom")
315+
mock.ExpectExec(wantedRoleWithPassExpectedCrtStmt).
316+
WillReturnError(dbError)
317+
mock.ExpectRollback()
318+
319+
dbRole := roleConfigurationAdapter{RoleConfiguration: wantedRoleWithPass}.toDatabaseRole()
320+
// In this unit test we are not testing the retrieval of secrets, so let's
321+
// fetch the password content by hand
322+
dbRole.password = sql.NullString{Valid: true, String: "myPassword"}
323+
err = Create(ctx, db, dbRole)
324+
Expect(err).To(HaveOccurred())
325+
Expect(err).To(MatchError(dbError))
326+
})
327+
285328
It("Create will send a correct CREATE with password deletion to the DB", func(ctx context.Context) {
286329
db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))
287330
Expect(err).ToNot(HaveOccurred())
@@ -295,7 +338,7 @@ var _ = Describe("Postgres RoleManager implementation test", func() {
295338
roleConfigurationAdapter{RoleConfiguration: wantedRoleWithPassDeletion}.toDatabaseRole())
296339
Expect(err).ShouldNot(HaveOccurred())
297340
})
298-
It("Create will send a correct CREATE with password deletion to the DB", func(ctx context.Context) {
341+
It("Create will send a correct CREATE with default connection limit to the DB", func(ctx context.Context) {
299342
db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))
300343
Expect(err).ToNot(HaveOccurred())
301344

@@ -332,16 +375,54 @@ var _ = Describe("Postgres RoleManager implementation test", func() {
332375
})
333376
// Testing Alter
334377
It("Update will send a correct ALTER to the DB", func(ctx context.Context) {
335-
db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
378+
db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))
336379
Expect(err).ToNot(HaveOccurred())
337380

338381
mock.ExpectExec(wantedRoleExpectedAltStmt).
339382
WillReturnResult(sqlmock.NewResult(2, 3))
340383
err = Update(ctx, db, roleConfigurationAdapter{RoleConfiguration: wantedRole}.toDatabaseRole())
341384
Expect(err).ShouldNot(HaveOccurred())
342385
})
386+
It("Update with password will ALTER the DB and prevent Postgres from logging the query", func(ctx context.Context) {
387+
db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))
388+
Expect(err).ToNot(HaveOccurred())
389+
390+
mock.ExpectBegin()
391+
mock.ExpectExec(wantedLogStatementSuppressionStmt).
392+
WillReturnResult(sqlmock.NewResult(0, 1))
393+
mock.ExpectExec(wantedLogPreventionStmt).
394+
WillReturnResult(sqlmock.NewResult(0, 1))
395+
mock.ExpectExec(wantedRoleExpectedAltWithPasswordStmt).
396+
WillReturnResult(sqlmock.NewResult(2, 3))
397+
mock.ExpectCommit()
398+
399+
dbRole := roleConfigurationAdapter{RoleConfiguration: wantedRoleWithPass}.toDatabaseRole()
400+
dbRole.password = sql.NullString{Valid: true, String: "myPassword"}
401+
err = Update(ctx, db, dbRole)
402+
Expect(err).ShouldNot(HaveOccurred())
403+
})
404+
It("Update with password will rollback ALTER if there is an error", func(ctx context.Context) {
405+
db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))
406+
Expect(err).ToNot(HaveOccurred())
407+
408+
mock.ExpectBegin()
409+
mock.ExpectExec(wantedLogStatementSuppressionStmt).
410+
WillReturnResult(sqlmock.NewResult(0, 1))
411+
mock.ExpectExec(wantedLogPreventionStmt).
412+
WillReturnResult(sqlmock.NewResult(0, 1))
413+
dbError := errors.New("kaboom")
414+
mock.ExpectExec(wantedRoleExpectedAltWithPasswordStmt).
415+
WillReturnError(dbError)
416+
mock.ExpectRollback()
417+
418+
dbRole := roleConfigurationAdapter{RoleConfiguration: wantedRoleWithPass}.toDatabaseRole()
419+
dbRole.password = sql.NullString{Valid: true, String: "myPassword"}
420+
err = Update(ctx, db, dbRole)
421+
Expect(err).To(HaveOccurred())
422+
Expect(err).To(MatchError(dbError))
423+
})
343424
It("Update will return error if there is a problem updating the role in the DB", func(ctx context.Context) {
344-
db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
425+
db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))
345426
Expect(err).ToNot(HaveOccurred())
346427

347428
dbError := errors.New("Kaboom")
@@ -350,12 +431,12 @@ var _ = Describe("Postgres RoleManager implementation test", func() {
350431

351432
err = Update(ctx, db, roleConfigurationAdapter{RoleConfiguration: wantedRole}.toDatabaseRole())
352433
Expect(err).To(HaveOccurred())
353-
Expect(errors.Is(err, dbError)).To(BeTrue())
434+
Expect(err).To(MatchError(dbError))
354435
})
355436

356437
// Testing COMMENT
357438
It("UpdateComment will send a correct COMMENT to the DB", func(ctx context.Context) {
358-
db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
439+
db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))
359440
Expect(err).ToNot(HaveOccurred())
360441

361442
mock.ExpectExec(wantedRoleCommentStmt).
@@ -365,7 +446,7 @@ var _ = Describe("Postgres RoleManager implementation test", func() {
365446
})
366447

367448
It("UpdateComment will return error if there is a problem updating the role in the DB", func(ctx context.Context) {
368-
db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
449+
db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))
369450
Expect(err).ToNot(HaveOccurred())
370451

371452
dbError := errors.New("Kaboom")
@@ -374,7 +455,7 @@ var _ = Describe("Postgres RoleManager implementation test", func() {
374455

375456
err = UpdateComment(ctx, db, roleConfigurationAdapter{RoleConfiguration: wantedRole}.toDatabaseRole())
376457
Expect(err).To(HaveOccurred())
377-
Expect(errors.Is(err, dbError)).To(BeTrue())
458+
Expect(err).To(MatchError(dbError))
378459
})
379460

380461
It("GetParentRoles will return the roles a given role belongs to", func(ctx context.Context) {

‎pkg/management/postgres/utils/roles.go‎

Lines changed: 27 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,7 @@ SPDX-License-Identifier: Apache-2.0
2020
package utils
2121

2222
import (
23+
"context"
2324
"database/sql"
2425
"errors"
2526
"fmt"
@@ -46,11 +47,7 @@ func DisableSuperuserPassword(db *sql.DB) error {
4647
if err != nil {
4748
return err
4849
}
49-
defer func() {
50-
// This has no effect if the transaction
51-
// is committed
52-
_ = tx.Rollback()
53-
}()
50+
defer func() { _ = tx.Rollback() }()
5451

5552
// we don't want to be stuck here if synchronous replicas are still not alive
5653
// and kicking
@@ -62,10 +59,31 @@ func DisableSuperuserPassword(db *sql.DB) error {
6259
return tx.Commit()
6360
}
6461

65-
// SetUserPassword change the password of a user in the PostgreSQL database
66-
func SetUserPassword(username string, password string, db *sql.DB) error {
67-
_, err := db.Exec(fmt.Sprintf("ALTER ROLE %v WITH PASSWORD %v",
62+
// ExecWithSuppressedLogging executes a SQL statement within a transaction that
63+
// suppresses PostgreSQL statement logging, preventing passwords from appearing
64+
// in server logs.
65+
func ExecWithSuppressedLogging(ctx context.Context, db *sql.DB, statement string) error {
66+
tx, err := db.BeginTx(ctx, nil)
67+
if err != nil {
68+
return err
69+
}
70+
defer func() { _ = tx.Rollback() }()
71+
72+
if _, err = tx.ExecContext(ctx, "SET LOCAL log_statement = 'none'"); err != nil {
73+
return err
74+
}
75+
if _, err = tx.ExecContext(ctx, "SET LOCAL log_min_error_statement = 'PANIC'"); err != nil {
76+
return err
77+
}
78+
if _, err = tx.ExecContext(ctx, statement); err != nil {
79+
return err
80+
}
81+
return tx.Commit()
82+
}
83+
84+
// SetUserPassword changes the password of a user in the PostgreSQL database
85+
func SetUserPassword(ctx context.Context, username string, password string, db *sql.DB) error {
86+
return ExecWithSuppressedLogging(ctx, db, fmt.Sprintf("ALTER ROLE %v WITH PASSWORD %v",
6887
pgx.Identifier{username}.Sanitize(),
6988
pq.QuoteLiteral(password)))
70-
return err
7189
}

0 commit comments

Comments
 (0)