Creating a read-only database user in Postgres

We typically recommend that you create a read-only trevor database user that Trevor can connect as.  You can do this easily by running the below commands against your postgres database:

1. Create a trevor user and set a password

CREATE USER trevor WITH PASSWORD 'your_secure_password';

2. Grant read-only access to just the schemas that you would like Trevor to access:

-- As a very minimum Trevor needs to be able to connect to your database
GRANT CONNECT ON DATABASE database_you_want_to_connect_to_trevor TO trevor;

-- Then for each database schema that you want Trevor to access
-- (including the 'public' schema) you will need to:

-- 1. permit trevor to list the tables in your schema
GRANT USAGE ON SCHEMA schema_you_want_to_connect_to_trevor TO trevor;
-- 2. permit trevor to perform SELECT statements against those tables
GRANT SELECT ON ALL TABLES IN SCHEMA schema_you_want_to_connect_to_trevor TO trevor;
-- Note: You will need to run the above for each schema that you want your team to access in Trevor

-- also grant access to any future tables that are added to your schema(s) (the above GRANTs only apply to existing tables)

ALTER DEFAULT PRIVILEGES IN SCHEMA schema_you_want_to_connect_to_trevor GRANT SELECT ON TABLES TO trevor;


Was this article helpful?

0 out of 0 found this helpful

Have more questions? Submit a request



Please sign in to leave a comment.