Description

In Redshift/Postgres, when a privilege is granted, it takes affect at the moment the grant is issued, and that’s it; it does not extend into the future, it does not cause any other actions ever to occur.

So if we issue something like grant select on all tables in schema, the select privilege is granted on all tables in that schema, at and only at the moment the command is issued.

If then later more tables are created in that schema, they do not have the select privilege granted.

All of the syntax in the grant command about “all tables in” and so on, are merely ways to indicate a set of objects - they are not a commandment that the user will in the future have the given privileges on new objects.

There is of course as you can imagine a need for the capability to specify that privileges are granted, in the future, on new objects. That’s where default privileges come in - this is the mechanism by which this is done.

A default privilege is created by a user, and owned by the user, and it says that whenever that user creates a new object (which can be a table, view, function or procedure), that the specified privileges are automatically to be granted to a given user or group, or to the group-like object public.

Additionally, one extra constraint can be specified, which is that the default privilege applies only to objects created in a particular schema.

This page lists all default privileges in the database.

Columns

Column Descriptions

owner_user_id

The owner user ID. This column is emitted in CSV exports only.

owner

The owner user name.

object:schema_id

If the default privilege is restricted to a schema, this is the schema ID, otherwise NULL.

object:schema

If the default privilege is restricted to a schema, this is the schema name, otherwise NULL.

object:type

The type of object the default privilege is for. There are three values, and the value is the type of object.

Note table includes views (all three types).

recipient:type

The type of the recipient of the privilege. This is one of the following;

recipient:id

If the recipient is a group, this is the group ID.

If the recipient is the group-like object public, this is NULL.

If the recipient is a user, this is the user ID.

recipient:name

If the recipient is a group, this is the group name.

If the recipient is the group-like object public, this is NULL.

If the recipient is a user, this is the user name.

privs

This column shows the privileges granted by the default privilege.

When the privilege is IN CAPITALS, it means the privilege to grant is being given (and in Postgres/Redshift, to be able to give the privilege to grant, the underlying privilege itself is inherently also granted, which makes sense, as you could just grant that privilege to yourself).

privs:function_execute

True if the privilege execute on function is granted, otherwise false. This column is emitted in CSV exports only.

privs:function_grant_execute

True if the privilege to grant execute on function is granted, otherwise false.

In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.

(Which makes sense, as you could just grant that privilege to yourself.)

This column is emitted in CSV exports only.

privs:procedure_execute

True if the privilege execute on procedure is granted, otherwise false. This column is emitted in CSV exports only.

privs:procedure_grant_execute

True if the privilege to grant execute on procedure is granted, otherwise false.

In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.

(Which makes sense, as you could just grant that privilege to yourself.)

This column is emitted in CSV exports only.

privs:table_delete

True if the privilege delete on table is granted, otherwise false. This column is emitted in CSV exports only.

privs:table_grant_delete

True if the privilege to grant delete on table is granted, otherwise false.

In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.

(Which makes sense, as you could just grant that privilege to yourself.)

This column is emitted in CSV exports only.

privs:table_drop

True if the privilege drop on table is granted, otherwise false. This column is emitted in CSV exports only.

privs:table_grant_drop

True if the privilege to grant drop on table is granted, otherwise false.

In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.

(Which makes sense, as you could just grant that privilege to yourself.)

This column is emitted in CSV exports only.

privs:table_insert

True if the privilege insert on table is granted, otherwise false. This column is emitted in CSV exports only.

privs:table_grant_insert

True if the privilege to grant insert on table is granted, otherwise false.

In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.

(Which makes sense, as you could just grant that privilege to yourself.)

This column is emitted in CSV exports only.

privs:table_reference

True if the privilege reference on table is granted, otherwise false.

This privilege allows a user to create references (foreign keys) on a table, but to do so, this privilege must be held on both the table itself and the foreign key table.

This column is emitted in CSV exports only.

privs:table_grant_reference

True if the privilege to grant reference on table is granted, otherwise false.

In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.

(Which makes sense, as you could just grant that privilege to yourself.)

This column is emitted in CSV exports only.

privs:table_select

True if the privilege select on table is granted, otherwise false. This column is emitted in CSV exports only.

privs:table_grant_select

True if the privilege to grant select on table is granted, otherwise false.

In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.

(Which makes sense, as you could just grant that privilege to yourself.)

This column is emitted in CSV exports only.

privs:table_update

True if the privilege update on table is granted, otherwise false. This column is emitted in CSV exports only.

privs:table_grant_update

True if the privilege to grant update on table is granted, otherwise false.

In Redshift/Postgres, if the privilege to grant is held, then the underlying privilege itself is also held; granting the privilege to grant also grants the privilege itself.

(Which makes sense, as you could just grant that privilege to yourself.)

This column is emitted in CSV exports only.