create database specific user in redshift

1
votes

We are trying to create a user in Amazon Redshift which should only access a specific database. We know how to assign user to specific schema, but I want use public schema.

Till now we tried following commands:

To create user:

create user tt password 'tttt';

To create group:

create group report_group with user tt;

To grant access of database 

grant select on  DATABASE demo TO tt;

but its not allowing running select query and fails with

access denied to schema public

any suggestion in this.

2
amazon-redshiftprivileges
Well, you didn't grant the select (and usage) privilege on the schema public. – a_horse_with_no_name
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO GROUP report_group; alter default privileges in schema public grant select on tables to group report_group; – Dilip

2 Answers

1
votes

You need the below two grants to the user or the group 

GRANT USAGE ON SCHEMA public TO tt;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO tt;
0
votes
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public
TO GROUP report_group;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO GROUP report_group;