Redshift User Administration
1.1
Add New Users
Amazon
Redshift user accounts can only be created and dropped by a database superuser.
CREATE USER
username PASSWORD '#######';
Connection limit: CREATE USER
username PASSWORD '#######'
connection limit 10;
Syslog access:CREATE USER username PASSWORD '#######' syslog access unrestricted;
Syslog
access specifies the user's access level to the Amazon Redshift system
tables and views.
Superuser:
CREATE USER adminuserCREATE USER username PASSWORD '#######
Show all users: select * from
pg_user
Users can be assigned ownership to tables: alter table schemaname.tablename owner to user001;
1.2
Adding Groups
Groups are used to simplify the management of permissions
and resources.
CREATE GROUP
groupname;
CREATE USER username PASSWORD '#######' in group group001; Show all groups: select * from pg_group
1.3
Remove Users
drop
user username;
A user must reassign or free up all dependent objects and privileged resources. A Redshift utilities GitHub repository provides example statements and views for accessing Redshift Metadata. 1.4
Set user max connection limit
alter
user username connection limit 10;
1.5
Set user query group (needs to tie into setting
up a queue in WLM section above)
If a
user group name is listed in a WLM queue definition, queries run by members of
that user group will be assigned to the corresponding WLM queue.
alter
group groupname add user username;
You
can assign a query to a queue at run time by assigning your query to the
appropriate query group. Use the SET command to begin a query group.
set
query_group to groupname;
1.6
Adding database
It is recommended to use a single database within a Redshift
cluster for all your application related schemas. A database can be created
during the cluster creation process.
View databases:
select datname, datdba, datconnlimit from pg_database_info where datdba > 1;
1.7
Adding schemas
create schema schemaname authorization user001; select
nspname as schema, usename as owner from pg_namespace, pg_user where
pg_namespace.nspowner = pg_user.usesysid and pg_user.usename ='user001';
You can set a schema search path for a user. Unqualified table names will be searched for using the search_path schema name values.alter
user user001 set search_path to schemaname, public;
Grant select on table to group
GRANT SELECT
ON "database".schema.tablename
TO group
groupname;
Query to list username and associated group name
SELECT usename AS user_name,
groname AS group_name
FROM pg_user,
pg_group
WHERE
pg_user.usesysid = ANY(pg_group.grolist)
AND pg_group.groname in (SELECT DISTINCT
pg_group.groname from pg_group);
Query to list permission on group
select relacl ,
'grant ' || substring(
case when charindex('r',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',select ' else '' end
||case when charindex('w',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',update ' else '' end
||case when charindex('a',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',insert ' else '' end
||case when charindex('d',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',delete ' else '' end
||case when charindex('R',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',rule ' else '' end
||case when charindex('x',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',references ' else '' end
||case when charindex('t',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',trigger ' else '' end
||case when charindex('X',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',execute ' else '' end
||case when charindex('U',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',usage ' else '' end
||case when charindex('C',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',create ' else '' end
||case when charindex('T',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',temporary ' else '' end
, 2,10000)
|| ' on '||namespace||'.'||item ||' to "'||pu.groname||'";' as grantsql
from
(SELECT
use.usename as subject,
nsp.nspname as namespace,
c.relname as item,
c.relkind as type,
use2.usename as owner,
c.relacl
FROM
pg_user use
cross join pg_class c
left join pg_namespace
nsp on (c.relnamespace = nsp.oid)
left join pg_user use2 on (c.relowner =
use2.usesysid)
WHERE
c.relowner = use.usesysid
and
nsp.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
ORDER BY
subject,
namespace, item
) join pg_group pu on array_to_string(relacl, '|') like '%'||pu.groname||'%'
where relacl is not null
and pu.groname= ‘groupname’
order by 2
Query
to find table creation date
SELECT
TRIM(nspname) AS schema_name,
TRIM(relname) AS table_name,
relcreationtime AS creation_time
FROM pg_class_info
LEFT JOIN pg_namespace ON
pg_class_info.relnamespace = pg_namespace.oid
WHERE reltype != 0 and TRIM(relname) like '%f_mobiler%'
AND TRIM(nspname) = 'global' order by relcreationtime desc
