No error when user does not have rights to tables

[NathanZarro]

When the username being used for connection has not been granted the appropriate privileges to the tables, no data is returned, but no error message.

[jackc]

I am unable to duplicate this.

package main

import (
	"log"

	"github.com/jackc/pgx"
)

func main() {
	config, err := pgx.ParseEnvLibpq()
	if err != nil {
		log.Fatal(err)
	}

	conn, err := pgx.Connect(config)
	if err != nil {
		log.Fatal(err)
	}
	defer conn.Close()

	var s string
	err = conn.QueryRow("select id from customers").Scan(&s)
	if err != nil {
		log.Fatal(err)
	}
}

Output:

jack@happy:~/dev/go/src/github.com/jackc/pgx-416$ PGDATABASE=xxxx PGHOST=127.0.0.1 PGUSER=xxxx PGPASSWORD=xxxx go run main.go 
2018/05/10 17:56:09 ERROR: permission denied for relation customers (SQLSTATE 42501)
exit status 1

[vkonstantin]

I have the same bug when I am using conn.Query(...), when I am using QueryRow or Exec - it works fine.

[jackc]

conn.Query sends the query but doesn't immediately read the results like QueryRow and Exec. The error is returned as part of the results. If you try to use the results of conn.Query() you will get an error.

package main

import (
	"log"

	"github.com/jackc/pgx"
)

func main() {
	config, err := pgx.ParseEnvLibpq()
	if err != nil {
		log.Fatal(err)
	}

	conn, err := pgx.Connect(config)
	if err != nil {
		log.Fatal(err)
	}
	defer conn.Close()

	rows, err := conn.Query("select id from users")
	if err != nil {
		log.Fatalf("Query err: %v", err)
	}

	for rows.Next() {
		var s string
		err = rows.Scan(&s)
		if err != nil {
			log.Fatalf("Scan err: %v", err)
		}
	}

	if rows.Err() != nil {
		log.Fatalf("rows.Err(): %v", rows.Err())
	}
}

Output:

jack@happy:~/dev/go/src/github.com/jackc/pgx-416$ PGDATABASE=xxxx PGHOST=127.0.0.1 PGUSER=xxxx PGPASSWORD=xxxx go run main.go
2018/10/19 10:29:47 rows.Err(): ERROR: permission denied for relation users (SQLSTATE 42501)
exit status 1

[baldrich-randori]

why is this issue closed? I'm using sqlx and using queryx and the same issue happens. When I replace pgx with pq it gives the appropriate error.

[jackc]

conn.Query sends the query but doesn't immediately read the results like QueryRow and Exec. The error is returned as part of the results. If you try to use the results of conn.Query() you will get an error.

[duckie]

I still have this issue when is the user is not allowed to fetch any row. My sample:

  rows, err := p.Conn.Query(context.Background(), "select * from requests where state='requested';")
  defer rows.Close()
  if err != nil {
    log.Error("Failed to fetch unprocessed requests.")
    os.Exit(1)
  }
  if rows.Err()  != nil {
    log.Error("Failed to fetch unprocessed requests.")
    os.Exit(1)
  }

  s := make([]server.Request,0)
  for rows.Next() {
    request := new(model.Request)
    err = rows.Scan(request)
    if err != nil {
      log.Fatalf("Postgres row scan error: %v", err)
      continue
    }
    s = append(s, *request)
  }

No error is shown anywhere, but on the Posthresql console, I get ERROR: permission denied for table requests. Running on docker image postgres:12.6-alpine.

[jackc]

You need to check rows.Err() after rows.Next().

[duckie]

@jackc My bad, that works indeed. rows.Next() is false, so the error must be checked after the loop indeed.

Thank you for such a quick answer.