How can I grant permissions to newly created objects in Amazon Redshift?

Short description

For a user to access newly created objects in the schema, privileges must be granted to the objects by a superuser.

When a user isn’t able to access newly created objects in the schema, they might receive the following error:

ERROR: permission denied for relation “objectname”.

This error happens when access is granted for only the current objects present in a schema when the access was granted. By default, access isn’t automatically granted on future objects that are created under the schema.

To resolve this issue, grant access privileges to the user using the ALTER DEFAULT PRIVILEGES command.

Resolution

To grant permissions for the current and future tables in the schema, do the following as a superuser:

1.    To grant usage access on the schema and SELECT access on all tables currently present under the schema, run the following commands:
Note: Replace newtestschema with the name of your schema and newtestuser with the name of the user.

grant usage on schema newtestschema to newtestuser;
grant select on all tables in schema newtestschema to newtestuser;

2.    To grant SELECT access to the user for future tables created under the schema, run the following command: Note: Replace awsuser with the username that is used to create future objects under the schema, newtestschema with the schema name, and newtestuser with the username that needs access to future objects.

alter default privileges for user awsuser in schema newtestschema grant select on tables to newtestuser;

Note: Default privileges apply only to new objects. Running ALTER DEFAULT PRIVILEGES doesn’t change privileges on existing objects.

3.    To verify default privileges have been granted to the user, run the following query as a superuser:

select pg_get_userbyid(d.defacluser) as user, 
n.nspname as schema, 
case d.defaclobjtype when 'r' then 'tables' when 'f' then 'functions' end 
as object_type, 
array_to_string(d.defaclacl, ' + ')  as default_privileges 
from pg_catalog.pg_default_acl d 
left join pg_catalog.pg_namespace n on n.oid = d.defaclnamespace;

If access is present, then you will see an entry for the user under the column default_privileges.

Now, when the superuser creates a new object under the schema, the user will have SELECT access over the table.

Example

The following example starts with this configuration:

• A user named newtestuser who isn’t a superuser.
• A schema named newtestschema and one table named newtesttable1 under the schema with a few records.

The superuser named awsuser, grants access to newtestuser on newtestschema schema and all the tables currently present under the schema, using the following example command:

grant usage on schema newtestschema to newtestuser;
grant select on all tables in schema newtestschema to newtestuser;

The preceding command grants newtestuser with SELECT access on the current tables present under the newtestschema. Currently, only the newtesttable1 table is present under the newtestschema. The newtesttable1 table is accessible to newtestuser.

Next, awsuser creates another table named newtesttable2 under the newtestschema. If newtestuser runs a SELECT query on the table newtestschema.newtesttable2, they see the following error:

ERROR: permission denied for relation newtesttable2.

To resolve the error, the awsuser does the following:

1.    Grants access to the table, newtesttable2, by running the following example command:

grant select on table newtestschema.newtesttable2 to newtestuser;

2.    Grants access to newtestuser, on any future tables created by awsuser under the newtestschema, by running the following example command:

alter default privileges for user awsuser in schema newtestschema grant select on tables to newtestuser;

Now, when awsuser creates another new table named newtesttable3, under the newtestschema, the newtestuser will have SELECT access on newtesttable3.

3.    To verify default privileges have been granted to the newtestuser, the awsuser runs the following query:

select pg_get_userbyid(d.defacluser) as user, 
n.nspname as schema, 
case d.defaclobjtype when 'r' then 'tables' when 'f' then 'functions' end 
as object_type, 
array_to_string(d.defaclacl, ' + ')  as default_privileges 
from pg_catalog.pg_default_acl d 
left join pg_catalog.pg_namespace n on n.oid = d.defaclnamespace;

The output of the preceding query is similar to the following:

user    | schema    | object_type    | default_privileges
awsuser | newtestschema    | tables    | newtestuser=r/awsuser

The output shows that awsuser grants SELECT privileges to newtestuser for all new tables created by awsuser in the newtestschema.

---

Related information

PG_DEFAULT_ACL