0

Please help me with my use case requirement as stated below

We have deployed aws redshift cluster with security groups available to all currently But i wish to limit the access of that security group to certain people when connection is made from there personal device (ie. laptop) Those certain people are at different locations around the globe What existing solution can i apply to limit my access to those people using that certain device(i.e laptop)

*Note :- Solution should enable only those people to access the cluster from there personal device

a suggestion for setting up a windows ec2 instance to access redshift has been declined

Users are at different geographical locations and are using different ISP service providers to connect to redshift.To configure the changes on source end i.e user end is not feasible due to some unavoidable reasons

ankitkhanduri
  • 101
  • 2
  • Security groups http://docs.aws.amazon.com/redshift/latest/mgmt/working-with-security-groups.html – Tim Mar 15 '17 at 07:12
  • Thanks tim , i have read the documentation was not much of help for me for my use case scenario – ankitkhanduri Mar 15 '17 at 07:21
  • You will have to limit by IP address or CIDR block. – Tim Mar 15 '17 at 08:06
  • I have tried that tim but as soon as my device re connect to internet its ip address changes and same for other colleagues sincei dont have access to there network which wuld enable me to fix a ip address to mac - address of pc in router(dhcp) i was looking for other solution which wuld enable me to achive tht – ankitkhanduri Mar 15 '17 at 08:25
  • I don't think there's any other way. You're probably better off whitelisting your office IP. – Tim Mar 15 '17 at 17:58

3 Answers3

0

As you're saying

Users are at different geographical locations and are using different ISP service providers to connect to redshift.

I assume we're talking about dynamic IP addresses so you can't limit their IPs inside of the security group as they're always changing. The only way I can think of right now is blocking every external IP address in the security group of the Redshift cluster and setting up a "SSH jump host" inside of the same VPC. If you now enable this host to connect to the Redshift cluster users can access the Redshift via a SSH tunnel. As you can roll out different SSH private keys to each machine you can limit and allow the people independent from each to other to access the Redshift cluster. Several SQL tools are available for DB Connection through SSH tunnels so maybe that is something your users already use and you're able to limit the access to certain users.

Osterjour
  • 845
  • 8
  • 12
0

In a Redshift database, users get access to the database objects, views, and tables depending on the privileges granted to their user accounts. Users can access only those parts for which they have been granted access permission. Depending on how you want to limit Redshift DB access to users, you can use statements such as CREATE USER, GRANT, CREATE GROUP and REVOKE SQL statements. I would recommend group level security for easier maintenance.

Here are some guidelines to Redshift security:

  • Access is granted to the object owner only, by default.

  • User accounts connect users to the database. Privileges are granted explicitly, by assigning them directly to the user account. It may be done implicitly by becoming a member of a group with granted privileges.

  • Groups include multiple users who are collectively assigned accesses. It makes it easy to maintain the utmost level of security.

  • Database tables and other database objects are collectively called schemas. Users may get access to a single or multiple schemas.

Further information on Redshift architecture and capabilities can be found in this article.

Mika Wolf
  • 169
  • 3
0

You can limit access to a Redshift db by creating user/group accounts.

Execute a revoke statement from your 'superuser' account to revoke access to a particular group of users.

For example: revoke all on all tables in test1_db from test2_group2

By default who creates a database object is its owner. So, if test2_group2 creates a new DB, you have to go back and create it again using your superuser account in the redshift architecture.

Here is a page where you can find more information about Redshift architecture and components.

AMaina
  • 11
  • 3