-
Notifications
You must be signed in to change notification settings - Fork 9
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
AWS IAM support for logging in #377
Comments
I've seen a few requests for this recently (also with regard to Redshift) and I'm wondering how to best implement this. It's probably a lot of UI work, so I want to think about how to best integrate this into Postico. Do you have any suggestions how you imagine the UI should look like? Are there any other DB clients that have AWS IAM integration? As a quick solution, it should be possible to write a shell script that fetches temporary credentials using aws-cli, and then use the 'open' command to connect to the db with Postico: #!/bin/bash
PASS=$(some command that fetches a password)
open -a Postico postgres://user:"$PASS"@dbhost:5432/dbname |
AWS AMI should just use username/password. It is just generated by AWS for you. |
I don't think you are understanding the problem. Some folks use a more advanced method than simple username/password to authenticate to redshift. There is an option to request essentially a one time token using your IAM credentials which is then used to authenticate to the database. Here is some documentation. https://docs.aws.amazon.com/redshift/latest/mgmt/redshift-iam-access-control-identity-based.html |
I'm still not quite sure how to implement IAM authentication. I've read the docs, and it seems pretty straightforward. I guess I'll have to do an API call to get a (temporary) password, and then use that to connect to the PostgreSQL / Redshift server. But how should it work? How would you imagine the UI to look like? Are there any other apps that offer IAM authentication? |
OK, so I played around, and I built a little demo app that takes an AWS Redshift IAM URL, and fetches the DB credentials, and then opens the URL with Postico. There's a bug in Postico 1.4.3 where passwords with special characters from URLs are incorrectly parsed. Since random IAM passwords often contain special characters, this might cause connections to fail. So use the demo app with this build of Postico: Postico 3937.zip I'm not sure how to integrate this into Postico. Can you try the demo app? Is it useful? What would you like improved? |
Looks like I forgot to upload the demo app. It's hard to try a screenshot :) Here's the link: AWS Playground.zip |
For future reference: Redshift IAM URLs are documented here: Note: The "AWS Playground" tool above currently only supports URLS of the form |
@jakob Any updates on this? Definitely would be useful. By the way, I'm happy to offer suggestions on how this could look, UI wise. |
I have a script that I generate my temporary password (password expires after some time). I have a saved favorite connection (where I have unchecked When that password times out Postico is basically stuck and I have to close the window and go back to the connection favorites, click edit, and enter a new password. Why doesn't Postico just prompt for the new username/password which is very common amongst other similar apps? Is there a way to enter the updated password without backing all the way out and starting over? One good immediate enhancement would be to prompt the user for the new password when authentication fails while Postico is re-establishing the connection. This is what my connection favorite looks like: |
Don't suppose there is an update on this is there? Our support team needs to connect to our redshift db's using iam to query around and right now they are all using sql workbench J. If I can upgrade them to postico they will be happy campers, but we can't do that until iam is supported. |
This would be a very welcome feature in our environment . . . |
Ok I moved to Aurora Serverless and am now in need of this! |
Would love this +1 |
Plz to add support. I am crying. |
Any updates on this one? |
Hey, our company really needs that, is there any update? |
Please add this feature - we really need it!!!! |
It's worth noting that even on AWS, IAM users aren't the only non-standard login solution: you can store and rotate passwords in Secrets Manager, which requires a similar credential retrieval step before any connection is initiated. I think the best way to handle this would be more generally, with support for a pre-connect script. This would allow Postico to work with other one-time-password schemes and other database providers. This feature seems to be ubiquitous in database client libraries, although in that case the interface is a simple and flexible function callback. What I would want is a script plugin system, where users can write (say, Python) scripts to handle any pre-connection dance their service requires. You could ship scripts which handle the most popular methods, such as AWS IAM authentication. UI-wise, I would want a submenu under "Connect via SSH" called "Pre-Connection Script". The submenu would be populated with scripts in some folder. Scripts would ideally support two commands: one which lists the required inputs (IAM access key id, IAM secret, AWS region and Secret Name), and one which transforms the inputs into usable Postgres credentials. Choosing a script would replace the standard input fields with ones specified by the script. I know this would be a lot of work, but it would allow Postico to support most non-standard login systems without you having to build that support yourself. |
Support for AWS Secret Manager would be a great idea :) |
I agree with danielbrauer about adding a scripting point. Many companies using AWS will be using something like aws-vault for managing AWS credentials or AWS SSO. This would be pretty difficult to support from inside Postico (e.g. setting environment variables, working directory, Keychain access, etc...) |
This works for me, though I'd really like built-in support :) RDSHOST="abc.xyz.us-east-1.rds.amazonaws.com"
RDSDBNAME="mydatabase"
open -a "Postico 2" "postgres://iamuser:$( \
aws rds generate-db-auth-token --hostname ${RDSHOST} --port 5432 --region us-east-1 --username iamuser \
| python3 -c "import sys; from urllib.parse import quote; sys.stdout.write(sys.stdin.read().replace('%', '%25').replace(':', '%3A').replace('/', '%2F').replace('?', '%3F'))" \
)@${RDSHOST}:5432/${RDSDBNAME}" |
Any progress here? This would be a great feature. |
Sorry, no updates at the moment. I sounds like adding a feature specific for AWS would be too constraining for a lot of you, so the better solution is probably to add a generic scripting hook. Some time back I've already tried to do that, but I had problems reliably starting subprocesses from a sandboxed app. It's however still a feature that I would absolutely love to add to Postico, since it seems to be a blocker for many of you. However, it would be really useful if you could share commands/scripts that you would use to generate login credentials, and show some sample output. This would help me design how this feature works. |
Hi @jakob , most of us are using this article from AWS to generate temporary IAM credentials and connect to the RDS instance using them. Hope this helps. Below is an example of actual usage of the
This command has 2 nested parts:
The temporary password produced in the output is a long string of the following format:
When you authenticate to the PostgreSQL instance on RDS, you have to provide this whole string as a user's password. Please let me know if I can help with providing more information. |
The above solutions very nearly work with Postico 2, but as @kurianoff mentioned the temporary password is a long string that is url-like which I believe trips up the connection string parsing within Postico. For example, if I run I also tried manually copy/pasting one of these temporary passwords into the connection parameters within Postico and it worked as expected, so my temporary solution is to do this: PGPASSWORD="$(aws rds generate-db-auth-token ... )"
echo "$PGPASSWORD" | tr -d '\n' | pbcopy
open -a "Postico 2" "postgres://readonly@$host.com:5432/$database" That way Postico opens and the temporary password is on my clipboard. However, this only works the first time because Postico saves the password by default and does not prompt for it on subsequent connections. It seems these temporary passwords don't play well with connection strings, which is why AWS recommends using a different format with |
@jakob I am using the following for temporary Redshift credentials, albeit in a script with more prompts and error handling: credentials=$(aws redshift get-cluster-credentials \
--cluster-identifier "$CLUSTER_IDENTIFIER" \
--db-user "$DATABASE_USER" \
--db-name "$DATABASE_NAME" \
--duration-seconds "$DURATION_SECONDS" \
--profile "$AWS_PROFILE"
)
iam_username=$(echo "$credentials" | jq -r '.DbUser' | sed -e 's/%/%25/g' -e 's/:/%3A/g' -e 's/\//%2F/g' -e 's/?/%3F/g')
temporary_password=$(echo "$credentials" | jq -r '.DbPassword' | sed -e 's/%/%25/g' -e 's/:/%3A/g' -e 's/\//%2F/g' -e 's/?/%3F/g')
open -a "$POSTICO_CLIENT" "postgres://${iam_username}:${temporary_password}@${DATABASE_HOST}:${DATABASE_PORT}/${DATABASE_NAME}" |
For all those who may be interested (FYI @agpiermarini @jczaplew), I have created a single backend PostgreSQL proxy that is called This helped my users to forget about the need to put a new password into Postico every time they run it. They can simply use their beloved PostgreSQL client while benefitting from the security of RDS IAM authentication. It is not a postgresql connection balancer in any way, it creates a single connection, but it can be easily extended to become a multi-backend proxy as it is built on the amazing postgresql proxy implementation from Encore.dev. Just wanted to share. Hope this example could also be helpful for @jakob to implement full RDS IAM authentication support in Postico. |
Hi! I've just added a new feature to Postico that should make using IAM a lot easier: a pre-connect shell script. I think I came up with a generic solution that should cover pretty much every use case we discussed here. Here's a sneak peek what the feature looks like: I also wrote a lengthy documentation page about it. The main thing I wonder is if it is accessible enough -- it does require writing a script, which could be an obstacle. Please download the latest development build and let me know if you can figure it out. |
Thank you very much, @jakob - I just tested the solution, and it works very well! I haven't tried a
Exactly what was needed! Thanks again 👍 |
Thank you, @jakob! |
@jakob I don't see that someone has answered your question re: which other GUI clients support this auth method - it is supported in DBeaver. |
@kevcube is that something that is limited to the Enterprise version of DBeaver? I couldn't find a feature to support AWS IAM or similar in DBeaver Community Edition (version 23.3.0.202312122044) |
Available in Lite, Enterprise, Ultimate and Team editions. I am using it in a Lite trial now. |
@kevcube Thank you, found it! Seems their support for IAM is pretty elaborate: https://dbeaver.com/docs/dbeaver/AWS-Credentials/ I wanted to do something similar at first, but I struggled with the AWS REST API, which is the reason why it took me so long to support IAM auth. I do hope that the current solution with the shell script is accessible enough for the people who need it. My thinking was that if a company adopts IAM, they would have no problem setting up a shell script. |
AWS does provide a Swift SDK (although only in dev preview) so you might not have to use REST API. Otherwise yeah shell script works for dev-people but in the org I'm working with that would be too big a barrier. They're already committed to dbeaver though so it's not a big deal, just wanted to chime in here after doing some research into postico's support. |
I use the App Store version of Postico. When will this be available in that version or is there a flag I can set to enable it to test? A shell script is a non-issue for me. I prefer it because different parts of our system have other authentication methods, from AWS SSO to secrets, which lets me solve many problems. My scripts would use AWS shell commands to generate the auth needed. Here are some examples of how it would work for me: Login to AWS accountThis sets up which AWS account I am logged into. This will block until the user handles the 2FA/SSO browser flow that will POP up if SSO is enabled for AWS account.
Generate Temp Credentials for DBThis is similar to the type of script I would use, it would depend on the loginAWS.sh above in many cases.
|
@yepher The preconnect shell script feature won't be available in the Mac App Store version. The feature uses a non-sandboxed helper tool that runs the shell script, which is not allowed in the Mac App Store. (At least not for 3rd party developers like myself. The rule does not apply to Apple's own apps.) Please email me if you want to switch to the direct download version. My address is jakob@eggerapps.at Your scripts should work for Postico, but a few tips:
More info here: https://eggerapps.at/postico2/documentation/connection-preconnect-script.html |
@jakob what are your thoughts on implementing the Swift SDK to do this? Wondering if sandboxed Mac apps will have the ability to read the user's |
It would require a lot of work from me for each identity provider (eg. AWS RDS and AWS Redshift have different APIs), and it would be difficult to support all use cases, since there are so many ways to provide credentials. I think the shell script is more flexible.
Yes, that should work. The shell script is executed by a helper app that is not sandboxed. That's why the Mac App Store version of Postico does not support it. |
This worked for me, thank you! |
Please add support for logging in via AWS IAM
The text was updated successfully, but these errors were encountered: