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

4 minute read
0

My user received a permission denied error for a newly created object. How do I grant the user the required permissions to access newly created objects in the schema?

Short description

Users who want to access newly created objects in the schema must have access privileges granted by an object owner or a superuser.

When a user can't 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 objects present in a schema when the access was first granted. By default, access isn't automatically granted for objects that are created under the current schema.

To resolve this issue, grant access privileges to the user or through group roles. Use 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 will be used to create future objects under the schema. Then, replace 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. When you run the ALTER DEFAULT PRIVILEGES command, the privileges on existing objects aren't changed.

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

    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 as a superuser:

    SELECT * FROM svv_relation_privileges where identity_name = 'newtestuser';

Example resolution

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.

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

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

The command grants newtestuser with SELECT access to the current tables present under the newtestschema. Currently, only the newtesttable1 table is present under the newtestschema. As a result, 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 newtestschema.newtesttable2 table, then the following error appears:

ERROR: permission denied for relation newtesttable2.

To resolve the error, awsuser does the following steps:

  1. Grants access to the newtesttable2 table. To do this, awsuser runs the following example command:

    grant select on table newtestschema.newtesttable2 to newtestuser;
  2. Grants access to newtestuser for any future tables created by awsuser under the newtestschema. To do this, awsuser run 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, newtestuser will have SELECT access to the newtesttable3 table.

  3. To check that default privileges were granted 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';

    To review the permissions granted directly to newtestuser, awsuser runs the following query:

    SELECT * from svv_relation_privileges where identity_name = 'newtestuser';

    The output shows that awsuser grants SELECT privileges to newtestuser for all new tables created by awsuser 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

Related information

PG_DEFAULT_ACL

AWS OFFICIAL
AWS OFFICIALUpdated a month ago