PostgreSQL WARNING notices should be considered errors

[dhermes]

Terraform Version

1.1.5

Affected Resource(s)

• postgresql_grant

Terraform Configuration Files

resource "postgresql_grant" "public_usage" {
  role        = "widgets_app"
  database    = "widgets"
  schema      = "public"
  object_type = "schema"
  privileges  = ["USAGE"]
}

Debug Output

N/A

Panic Output

N/A

Expected Behavior

The PostgreSQL WARNING notice is treated as an error.

Actual Behavior

The PostgreSQL WARNING notice is ignored.

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

1. Create a database widgets with a superuser
2. Create a new role widgets_app with a superuser, make sure this role is not a superuser
3. Try to do the grant in psql to see the WARNING notice

   widgets=> GRANT USAGE ON SCHEMA public TO widgets_app;
   WARNING:  no privileges were granted for "public"
   GRANT
   

[dhermes]

I wrote up a Go script that uses both lib/pq and pgx to see how the WARNING notice manifests with database/sql (i.e. standard library) usage. Neither one does anything.

main.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"os"

	_ "github.com/jackc/pgx/v4/stdlib"
	_ "github.com/lib/pq"
	"github.com/spf13/cobra"
)

const (
	grantUsage = "GRANT USAGE ON SCHEMA public TO widgets_app"
)

type Config struct {
	ConnectionURL string
	Driver        string
}

func openPool(c Config) (*sql.DB, error) {
	return sql.Open(c.Driver, c.ConnectionURL)
}

func Run(ctx context.Context, c Config) error {
	pool, err := openPool(c)
	if err != nil {
		return err
	}

	err = pool.PingContext(ctx)
	if err != nil {
		return err
	}

	_, err = pool.ExecContext(ctx, grantUsage)
	return err
}

func run() error {
	ctx := context.Background()
	c := Config{}
	cmd := &cobra.Command{
		Use:           "postgresql-warning",
		Short:         "Cause a PostgreSQL warning notice with Go",
		SilenceErrors: true,
		SilenceUsage:  true,
		RunE: func(_ *cobra.Command, _ []string) error {
			return Run(ctx, c)
		},
	}

	cmd.PersistentFlags().StringVar(
		&c.ConnectionURL,
		"connection-url",
		c.ConnectionURL,
		"The connection URL to use to connect to the database",
	)
	cmd.PersistentFlags().StringVar(
		&c.Driver,
		"driver",
		c.Driver,
		"The driver to use (pgx or postgres for lib/pq)",
	)

	required := []string{"connection-url", "driver"}
	for _, name := range required {
		err := cobra.MarkFlagRequired(cmd.PersistentFlags(), name)
		if err != nil {
			return err
		}
	}

	return cmd.Execute()
}

func main() {
	err := run()
	if err != nil {
		fmt.Fprintf(os.Stderr, "%v\n", err)
		os.Exit(1)
	}
}

Running:

$ go run ./main.go --connection-url "postgres://widgets_app:[email protected]:21532/widgets?sslmode=disable" --driver postgres
$
$ go run ./main.go --connection-url "postgres://widgets_app:[email protected]:21532/widgets?sslmode=disable" --driver pgx

WireShark Capture of Outbound (GRANT) Query Message (>Q)

WireShark Capture of Notice (<N) Message

WireShark Capture of Command Complete and Ready For Query (<C/Z) Messages

[dhermes]

It's possible to wire up listeners, but just requires a little more care. I.e. via

func openPool(c Config) (*sql.DB, error) {
	if c.Driver == "postgres" {
		base, err := pq.NewConnector(c.ConnectionURL)
		if err != nil {
			return nil, err
		}
		connector := pq.ConnectorWithNoticeHandler(base, func(notice *pq.Error) {
			fmt.Printf("Notice received (lib/pq): %#v\n", notice)
		})
		pool := sql.OpenDB(connector)
		return pool, nil
	}

	if c.Driver == "pgx" {
		cc, err := pgx.ParseConfig(c.ConnectionURL)
		if err != nil {
			return nil, err
		}
		cc.OnNotice = func(_ *pgconn.PgConn, notice *pgconn.Notice) {
			fmt.Printf("Notice received (pgx): %#v\n", notice)
		}

		pool := stdlib.OpenDB(*cc)
		return pool, nil
	}

	return sql.Open(c.Driver, c.ConnectionURL)
}

we get

$ go run ./main.go --connection-url "postgres://widgets_app:[email protected]:21532/widgets?sslmode=disable" --driver postgres
Notice received (lib/pq): &pq.Error{Severity:"WARNING", Code:"01007", Message:"no privileges were granted for \"public\"", Detail:"", Hint:"", Position:"", InternalPosition:"", InternalQuery:"", Where:"", Schema:"", Table:"", Column:"", DataTypeName:"", Constraint:"", File:"aclchk.c", Line:"329", Routine:"restrict_and_check_grant"}
$
$
$ go run ./main.go --connection-url "postgres://widgets_app:[email protected]:21532/widgets?sslmode=disable" --driver pgx
Notice received (pgx): &pgconn.Notice{Severity:"WARNING", Code:"01007", Message:"no privileges were granted for \"public\"", Detail:"", Hint:"", Position:0, InternalPosition:0, InternalQuery:"", Where:"", SchemaName:"", TableName:"", ColumnName:"", DataTypeName:"", ConstraintName:"", File:"aclchk.c", Line:329, Routine:"restrict_and_check_grant"}

However, correlating these callbacks with the request that made the connection may be challenging if you use a single DB connection pool with lots of concurrent requests.