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;

-- Trevor only needs read-only (i.e. SELECT) access.
-- If you want to see multiple schemas in Trevor you may need to run the below multiple times.
GRANT SELECT ON ALL TABLES IN SCHEMA schema_you_want_to_connect_to_trevor TO trevor;

-- also grant access to any future tables that are added to this schema (the above GRANT only applies 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

Comments

0 comments

Please sign in to leave a comment.