I am trying to provide read-only access to a specific environment. I did that on the roles page on Aptible. The member with read-only access cannot tunnel in but with the credentials available on their Aptible account, they could write to DB. Could you please help me with the process of giving a specific member read-only access to a DB
Hello,
This answer includes specifics for the PostgreSQL database type, but is true for most Aptible database types. The commands for user management will change for e.g. MySQL Databases.
Aptible role management for the Environment is limited to what the Aptible user can do through the CLI or Dashboard; Database user management is separate.
You can create other database users on the Database with CREATE USER . The main issue is exposing the Database so that it can be accessed by this individual without giving them access to the aptible database user’s credentials. Normally you use aptible db:tunnel to access the Database locally but this command prints the tunnel URL with the aptible user credentials. This leads to a couple of situations.
If you don’t mind giving this individual access to the aptible credentials
Then you can give them Manage access to the Database’s Environment so they can tunnel and just use the read-only user and password to log in via the tunnel. This is relatively easy to implement and can help prevent accidental writes but doesn’t ensure that this individual doesn’t login as aptible . They would also have to remember not to copy/paste the aptible user credentials printed every time they tunnel.
If this individual cannot have access to the aptible credentials
Then they cannot have Manage access to the Database which removes db:tunnel as an option.
If they only need CLI access, you can create an App with a tool like psql installed on a different Environment on the same Stack. They can aptible ssh into the App and use psql to access the Database using the read-only credentials. The Aptible user would require Manage access to this second Environment, but would not need any access to the Database’s Environment for this to work.
If they need access from their computer, then you’ll have to create a Database Endpoint to expose the Database over the internet. We strongly recommend using IP Filtering to restrict access to the IP addresses or address ranges that they’ll be accessing the Database from so that the Database isn’t exposed to the entire internet for anyone to attempt to connect to.
-Bayley