Data TutorialsPostgreSQL

How to create a user with PSQL

Posted on March 30, 2020

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.

Visual SQL Hero

Introducing Visual SQL

SQL may be the language of data, but not everyone can understand it. With our visual version of SQL, now anyone at your company can query data from almost any source—no coding required.

Learn about Visual SQL