PostgreSQL Tests Endpoints

Quickstart#

todo

Parameters#

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

The following parameters are available to the probe:

Examples#

pgsql-ping

• GET Method

Try it:

• 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:

pgsql-query

• GET Method

Try it:

• 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:

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