How to create a user with PSQL

Connect to your PostgreSQL server instance using the following command:

sudo -u postgres psql

Select the database you would like to connect to Chartio:

\c databasename;

Create a new role for your Chartio read-only user:

CREATE ROLE chartio_read_only_user LOGIN PASSWORD 'secure_password';

Grant the necessary privileges for the new user to connect to your database:

GRANT CONNECT ON DATABASE exampledb TO chartio_read_only_user;
GRANT USAGE ON SCHEMA public TO chartio_read_only_user;
Connect a database

Run the following command to generate the queries that will grant select access to your new user:

SELECT 'GRANT SELECT ON '||schemaname||'."'||tablename||'" TO chartio_read_only_user;' FROM pg_tables WHERE schemaname IN ('public') ORDER BY schemaname, tablename;

You will see a list of GRANT statements similar to the following:

List of GRANT statements like this

Copy the GRANT statements generated by the query and then paste them into your terminal window. To restrict access to a subset of tables, only run the GRANT statements for the corresponding tables. To limit access to specific columns, please read how to limit PostgreSQL access using schema.

GRANT SELECT ON public."album" TO chartio_read_only_user;
GRANT SELECT ON public."artist" TO chartio_read_only_user;
GRANT SELECT ON public."customer" TO chartio_read_only_user;
GRANT SELECT ON public."employee" TO chartio_read_only_user;
GRANT SELECT ON public."genre" TO chartio_read_only_user;
GRANT SELECT ON public."invoice" TO chartio_read_only_user;
GRANT SELECT ON public."invoiceline" TO chartio_read_only_user;
GRANT SELECT ON public."mediatype" TO chartio_read_only_user;
GRANT SELECT ON public."playlist" TO chartio_read_only_user;
GRANT SELECT ON public."playlisttrack" TO chartio_read_only_user;
GRANT SELECT ON public."track" TO chartio_read_only_user;
Final result

Your new read-only user now has the necessary permissions for Chartio to connect to your database.