I needed to make sure I had two users in an Amazon Redshift cluster (readonly and readwrite). I needed to make sure their permissions were set appropriately so the first step was to first see what their permissions were on the schema and then the tables.
First we check their schema permissions with the below query that makes use of the has_schema_privilege function:
SELECT
u.usename,
s.schemaname,
has_schema_privilege(u.usename,s.schemaname,'create') AS user_has_create_permission,
has_schema_privilege(u.usename,s.schemaname,'usage') AS user_has_usage_permission
FROM
pg_user u
CROSS JOIN
(SELECT DISTINCT schemaname FROM pg_tables) s
WHERE
(u.usename = 'readonly'
OR
u.usename = 'readwrite')
AND
s.schemaname = 'public';
The query above filters on the specific users and schema I’m checking against. You are welcome to customize this query to look for all users and/or all schemas. This query results in the below permissions on the public schema for these users:
usename | schemaname | user_has_create_permission | user_has_usage_permission
-----------+------------+----------------------------+---------------------------
readonly | public | t | t
readwrite | public | t | t
This looks good so let’s check the tables within the schema using the has_table_privilege function:
SELECT
u.usename,
t.schemaname||'.'||t.tablename as path,
has_table_privilege(u.usename,t.tablename,'select') AS has_select,
has_table_privilege(u.usename,t.tablename,'insert') AS has_insert,
has_table_privilege(u.usename,t.tablename,'update') AS has_update,
has_table_privilege(u.usename,t.tablename,'delete') AS has_delete,
has_table_privilege(u.usename,t.tablename,'references') AS has_reference
FROM pg_user u
CROSS JOIN
pg_tables t
WHERE
(u.usename = 'readonly'
OR
u.usename = 'readwrite')
AND
t.schemaname = 'public'
AND
t.tablename = 'employees';
Again, I’m querying for very specific details. You are welcome to alter the query to fit your needs. This query results in the following:
usename | path | has_select | has_insert | has_update | has_delete | has_reference
-----------+-----------------+------------+------------+------------+------------+--------------
readonly | public.employees | f | f | f | f | f
readwrite | public.employees | f | f | f | f | f
It looks like these users do not have the correct access to the public.employees schema and table so this needs to be corrected. You can reference the AWS Redshift GRANT command details to see what permissions we should grant these users. I’ll use the below GRANT statements to correct each users permissions respectively.
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO readwrite;
Now let’s double check our work:
dev=# SELECT
u.usename,
t.schemaname||'.'||t.tablename as path,
has_table_privilege(u.usename,t.tablename,'select') AS has_select,
has_table_privilege(u.usename,t.tablename,'insert') AS has_insert,
has_table_privilege(u.usename,t.tablename,'update') AS has_update,
has_table_privilege(u.usename,t.tablename,'delete') AS has_delete,
has_table_privilege(u.usename,t.tablename,'references') AS has_reference
FROM pg_user u
CROSS JOIN
pg_tables t
WHERE
(u.usename = 'readonly'
OR
u.usename = 'readwrite')
AND
t.schemaname = 'public'
AND
t.tablename = 'employees';
usename | path | has_select | has_insert | has_update | has_delete | has_reference
-----------+-----------------+------------+------------+------------+------------+--------------
readonly | public.employees | t | f | f | f | f
readwrite | public.employees | t | t | t | t | t
(2 rows)
These permissions look like what we’d expect! We can further test with the user’s themselves. The below code block is attempting a select and then an update with the readonly user.
dev=> select * from employees limit 1;
employee_id | employee_name | employee_status | manager | employee_email | ssn | vip | location | payment_method
------------+--------------+----------------+---------------------+----------------------+-------------+-----+----------+----------------
1 | Bob Smith | Inactive | [email protected] | [email protected] | 802-85-6966 | f | 1 | phpe
(1 row)
dev=> update employees set employee_email='[email protected]' where employee_id=1;
ERROR: permission denied for relation employees
We can issue a SELECT but the UPDATE fails for a permissions issue so this looks good.
Let’s checkout our readwrite user:
dev=> select * from employees limit 1;
employee_id | employee_name | employee_status | manager | employee_email | ssn | vip | location | payment_method
------------+--------------+----------------+---------------------+----------------------+-------------+-----+----------+----------------
1 | Bob Smith | Inactive | [email protected] | [email protected] | 802-85-6966 | f | 1 | phpe
(1 row)
dev=> update employees set employee_email='[email protected]' where employee_id=1;
UPDATE 1
dev=> select * from employees limit 1;
employee_id | employee_name | employee_status | manager | employee_email | ssn | vip | location | payment_method
------------+-----------------+----------------+-----------------------+-----------------------------+-------------+-----+----------+----------------
1 | Bob Smith | Inactive | [email protected] | [email protected] | 802-85-6966 | f | 1 | phpe
(1 row)
Yaay it worked! Yes, this article assumes you already have the users created. Yes, we probably should look at doing role based permissions instead of giving the users direct permission. This was a small dev environment so I didn’t need to go crazy.
