1

I have setup Grafana and PostgreSQL.

I have connect Grafana to Postgresql, and I can run a query if I select "code" in the Explore and pick the Postgresql data source.

But if I use the builder, and would select the table from the table dropdown it mention "no options found"

Why can't I pick the table and columns in the builder?

jvels
  • 319
  • 2
  • 16

2 Answers2

1

I did set my search path in postgresql and now I can see the table: ALTER ROLE grafanareader set search_path = "my schema";

jvels
  • 319
  • 2
  • 16
0

To help you guys out there do this:

Step 1: Pre-requisites Before proceeding, ensure that you have a PostgreSQL database available.

Step 2: Create Read-Only User Follow the instructions mentioned in the Grafana documentation here to create a new read-only user. This user will be used to connect Grafana to the PostgreSQL database. https://grafana.com/docs/grafana/latest/datasources/postgres/

Step 3: Grant SELECT Permissions Once the read-only user is created, you need to grant the SELECT privilege on specific tables within the desired schema. This step limits the user's permissions to read-only access.

Step 1: Create User

To create a new user in PostgreSQL:

CREATE USER grafanareader WITH PASSWORD 'password';

Replace 'password' with the desired password for the user.

Step 2: Grant Schema Usage

Grant the USAGE privilege on the yourSchemaHere schema to the grafanareader user, allowing them to access objects within the schema:

GRANT USAGE ON SCHEMA yourSchemaHere TO grafanareader;

Step 3: Grant SELECT Permissions

Grant the SELECT privilege on specific tables within the yourSchemaHere schema to limit the user's permissions to read-only access:

GRANT SELECT ON yourSchemaHere.YourTable1 TO grafanareader;
GRANT SELECT ON yourSchemaHere.YourTable2  TO grafanareader;
GRANT SELECT ON yourSchemaHere.YourTable3 TO grafanareader;
GRANT SELECT ON yourSchemaHere.YourTable4 TO grafanareader;

Step 4: Set Search Path

Set the search path for the grafanareader role to the yourSchemaHere schema, ensuring that it's the default schema when accessing tables:

ALTER ROLE grafanareader SET search_path = 'yourSchemaHere';

Summary

By following these steps, you have successfully created a user (grafanareader) with a password, granted them USAGE on the yourSchemaHere schema, and provided SELECT access to specific tables within that schema. Additionally, you have set the search path to default to the yourSchemaHere schema for the grafanareader role.

Marty
  • 11
  • 2