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 adminuser CREATE 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