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

Short description

Users who want to access newly created objects in the schema must have the object owner or a superuser grant access permissions.

If you grant access to only the objects present in a schema when the access was first granted and a user can't access newly created objects in the schema, then the user might receive the following error message:

"ERROR: permission denied for relation 'objectname'"

By default, access isn't automatically granted for objects that are created under the current schema. To resolve this error, use the ALTER DEFAULT PRIVILEGES command to grant access privileges to the user or group roles.

Resolution

Prerequisites: Confirm that you granted schema USAGE and existing object permissions. Make sure that you configured default privileges for future objects, and used HAS_TABLE_PRIVILEGE() to verify permissions.

Grant access to current tables

To grant usage access on the schema and SELECT access on tables currently present under the schema, as a superuser run the following SQL commands:

grant usage on schema newtestschema to newtestuser;

Note: Replace newtestschema with the name of your schema and newtestuser with the username.

grant select on all tables in schema newtestschema to newtestuser;

Note: Replace newtestschema with the name of your schema and newtestuser with the name of the user.

Grant access to future tables

To grant SELECT access to the user for future tables created under the schema, as a superuser run the following ALTER DEFAULT PRIVLEGES command:

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

Note: Replace awsuser with the username used to create future objects under the schema, newtestschema with the schema name, and newtestuser with the username that needs access to future objects.

Default permissions apply only to new objects. If you run the ALTER DEFAULT PRIVILEGES command, then the permissions don't change for existing objects.

Verify permissions for a user

To verify that the user has the default permissions granted, as a superuser run the following query:

select * from svv_default_privileges where grantee_name = 'newtestuser'; 
select * from svv_default_privileges where schema_name = 'newtestschema'; 
select * from svv_default_privileges where grantee_type = 'role';

To review the permissions granted directly to the user, run the following query:

SELECT * FROM svv_relation_privileges where identity_name = 'newtestuser';

Example resolution

The following example starts with this configuration:

• A superuser names awsuser.
• A user named newtestuser who isn't a superuser.
• A schema named newtestschema.
• A table named newtesttable1 under the schema with a few records.

A superuser named awsuser grants access to newtestuser on newtestschema schema and on the tables currently present under the schema. To grant access, awsuser runs the following command:

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

Note: Replace newtestschema with your schema name and newtestuser with your user's username.

The preceding command grants newtestuser with SELECT access to the one table currently present under the newtestschema.

Next, awsuser creates a table named newtesttable2 under the newtestschema. If newtestuser runs a SELECT query on the newtestschema.newtesttable2 table without the correct permissions, then newtestuser gets the following error message:

"ERROR: permission denied for relation newtesttable2."

To resolve this error, awsuser completes the following steps:

1. To grant access to the newtesttable2 table, awsuser runs the following example command:

   grant select on table newtestschema.newtesttable2 to newtestuser;

   Note: Replace newtestschema with your schema name, newtesttable2 with your table name, and newtestuser with your user's username.
2. To grant access to newtestuser for any future tables created by awsuser under the newtestschema, awsuser runs the following example command:

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

   Note: Replace awsuser with your superuser and newtestschema with your schema name.
   If awsuser creates a new table named newtesttable3 under the newtestschema, then newtestuser has SELECT access to the newtesttable3 table.
3. To check that you granted default permissions to newtestuser, awsuser runs the following query:

   select * from svv_default_privileges where grantee_name = 'newtestuser'; 
   select * from svv_default_privileges where schema_name = 'newtestschema'; 
   select * from svv_default_privileges where grantee_type = 'role';

   Note: Replace newtestuser with your user's username and newtestschema with your schema name.
4. To review the permissions granted directly to newtestuser, awsuser runs the following query:

   SELECT * from svv_relation_privileges where identity_name = 'newtestuser';

   Note: Replace newtestuser with your user's username.
   The following output shows that awsuser grants SELECT permissions to newtestuser for new tables that awsuser created in the newtestschema.

   schema_name | object_type | owner_id | owner_name | owner_type | privilege_type | grantee_id | grantee_name | grantee_type | admin_option newtestschema | RELATION | 100 | awsuser | user | SELECT | 101 | newtestuser | user | FALSE