PostgreSQL Tests Endpoints

Quickstart#

todo

Parameters#

This probe enables verification of PostgreSQL connectivity and execution of queries. The probe has two modes:

ModeDescription
pingdefault Check if a connection can be established to a PostgreSQL server.
queryExecute a query.

The following parameters are available to the probe:

ParameterDescription
hostIP address or string PostgreSQL host.
portinteger PostgreSQL TCP port.
dbnamestring Database name.
usernamestring Database username.
passwordstring Database password.
querystring Query to execute. Only in query mode.

Examples#

pgsql-ping

  • GET Method

Try it:

host: "https://probe.onecheck.io" path: "/pgsql-ping" method: GET parameters: host: sql.sample.li port: 5432 username: john password: a2T4 dbname: coffeeshop format: simple
  • POST Method

curl --request POST \
  --url 'http://probe.onecheck.io' \
  --data 'pgsql host=sql.sample.li port=5432 dbname=coffeeshop username=john password=a2T4'

Try it:

host: "https://probe.onecheck.io" path: "" method: POST headers: X-Key: 5ca13f1300114e188571edfb46796000 parameters: format: simple body: | { "hello": "hello" }

pgsql-query

  • GET Method

Try it:

host: "https://probe.onecheck.io" path: "/pgsql-query" method: GET parameters: host: sql.sample.li port: 5432 username: john password: a2T4 dbname: coffeeshop format: simple query: SELECT+product_name+FROM+products+ORDER+BY+RANDOM%28%29+LIMIT+1
  • POST Method

curl --request POST \
  --url 'http://probe.onecheck.io/?timeout=5&format=json' \
  --data 'pgsql-query host=sql.sample.li port=5432 dbname=coffeeshop username=john password=a2T4 query="SELECT product_name FROM products ORDER BY RANDOM() LIMIT 2"'

Try it:

host: "https://probe.onecheck.io" path: "" method: POST headers: X-Key: 5ca13f1300114e188571edfb46796000 parameters: format: simple body: | { "hello": "hello" }

Security Considerations#

Be aware that you're giving our nodes your connection string. We will never use it (you can monitor that) but you can never be sure that your string connection won't be sniffed or exposed somewhere on the network.

For these reasons, we highly recommend you to:

  • Use our APIs via HTTPS only,
  • Create a read-only user for your checks.

How to create a read-only user with PostgreSQL ?

-- Connect to your database "my_database"

-- create the user
CREATE USER user_readonly_001;

-- set password
ALTER ROLE user_readonly_001 WITH PASSWORD 'koZkiE0v3r1kjS9';

-- grant connect to databases "my_database"
GRANT CONNECT ON DATABASE my_database TO user_readonly_001;

-- connect to your database (for example : \connect my_database)
-- and grand access to schema and tables :

-- ... grant access for schemas
GRANT USAGE ON SCHEMA public TO user_readonly_001;
-- ... if you have another schema:
-- GRANT USAGE ON SCHEMA my_schema TO user_readonly_001;

-- ... grant access to a specific table 
GRANT SELECT ON my_schema.my_table TO user_readonly_001;

And then, allow this user to access your database from all IPs in pg_hba.conf and reload PG :)

To allow all IPs, you can add this line

host    all     user_readonly_001    0.0.0.0/0       md5