Users can access Amazon Redshift database using a PostgreSQL- compatible psql client. Users can always connect to the Redshift database via leader node. In this article, we will see how to access redshift using psql without password prompt. You can use psql system variables to store required credentials.
Access Redshift using psql without Password Prompt
Password less access is useful when you are trying to execute Redshift queries from scripts such as shell or perl scripts.
Option 1. Export Amazon Redshift Database Environmental Variables
In order to access Redshift database with no password prompt, you need to set up some environmental variables.
Read:
- Redshift SET ON_ERROR_STOP using psql and Examples
- Run Redshift SQL Script File using psql Variable Substitution
- Steps to connect to Redshift using PostgreSQL – psql
PostgreSQL or psql supports below environmental variables:
| Environmental Variable | Description |
| PGHOST | The host name of the Redshift Database. This is usually your Redshift cluster information. This is required if you are connecting Redshift database from edge node or EC2 host systems |
| PGDATABASE | The name of the Redshift database to which you want to connect to. |
| PGPORT | The port number that the Redshift Database instance is running on. Default port number is 5439. |
| PGUSER | Redshift Database user name to connect. Typically, in the project life cycle, Developer, Tester and prod users will be different. |
| PGPASSWORD | Redshift Database user password. |
You have to export the above mentioned system variables in Unix/Linux bash shell.
Follow below steps to connect to Amazon Redshift using system or environmental variables.
Option 2. Use a .pgpass file to store the password
The one more option is to use .pgpass file to store your Redshift password. This is one of the secured and best way to hide your Redshift cluster credentials. You can also control the access to the password file. The file .pgpass in a user’s home directory or the file referenced by PGPASSFILE (set password file location to PGPASSFILE environmental variable) can contain passwords to be used if the connection requires a password.
File should contains information in following line format. You can even add comments to the file by preceding it with # symbol.
hostname:port:database:username:password
For instance, below is the location of file to be used when connection requires password.
Whenever connection requires the password, Redshift database get it from .pgpass file stored in particular user $HOME directory.
Feel free to add any other easy methods that you follow in your organization.
Related Articles
- Psql Windows Exit Status Codes – Explanation
- Psql Invalid Value for Parameter client_encoding – Redshift
- Redshift RSQL Control Statements – IF-ELSE-GOTO-LABEL