Articles in this section

See more

Granting Permissions to Database Objects

Avatar
Alex Marino
Updated
Follow

Permissions Overview

For a user to be able to view and interact with a database object such as a schema or table, they must first be granted the correct permissions.  By default, users have the ability to create tables in the “public” schema.  For all other schemas, users or groups will need to be granted explicit permissions.  Table owners and superusers are able to GRANT privileges to other users.  Privileges include access options such as being able to read data in tables and views, write data, and create tables.  Use the GRANT command to give specific privileges for a table, database, schema, or function. To revoke privileges from a database object, use the REVOKE command.

Permissions for schemas and tables are separate.  To grant access to “demo.user_table” you would first need to give the user permission to use the "demo" schema and then permission to interact with the "user_table" table.

Remember that you can (and should!) grant permissions to groups where applicable.  See Group Database Privileges and Managing Database Permissions for guidance on how to architect permissions for your organization.

Schemas

Begin by granting access to the schema, then to particular tables within a schema

grant usage/create/all on schema to user/group

  • usage: makes objects in a schema accessible to users. Specific actions on these objects must be granted separately (for example, SELECT or UPDATE privileges on tables)
  • create: allows users to create objects within a schema. To rename an object, the user must have the CREATE privilege and own the object to be renamed
  • all: grants both USAGE and CREATE on the schema 

Tables 

grant select/insert/update/delete/all on schema.table to user/group

  • select: grants privilege to select data from a table or view using a SELECT statement. The SELECT privilege is also required to reference existing column values for UPDATE or DELETE operations
  • insert: grants privilege to load data into a table using an INSERT statement or a COPY statement
  • update: grants privilege to update a table column using an UPDATE statement. UPDATE operations also require the SELECT privilege
  • delete: grants privilege to delete a data row from a table. DELETE operations also require the SELECT privilege

Alter Default Privileges

The ALTER DEFAULT PRIVILEGES command is very useful when wanting to ensure that users/groups (or roles, if you are using a Postgres instance) automatically gain access to new tables without needing to always remember to run a GRANT statement after a new database object is created.

The way it works is that for every new object that meets the specified criteria, it will automatically run the corresponding GRANT statement.  For example, in Redshift:

ALTER DEFAULT PRIVILEGES FOR USER jsmith IN SCHEMA new_project GRANT ALL ON TABLES TO GROUP new_project_group;

This means that for every new object created by the "jsmith" user in the "new_project" schema, the Redshift group "new_project_group" will be given ALL permissions.  Note that Redshift allows superusers to alter the default privileges for other users.

 

Generate GRANT Statements Using SQL Queries

In both Redshift and Postgres databases system catalog tables contain information about schemas and tables available on the database and can be used to programmatically generate a list of GRANTS. See the Postgres documentation for morepg_namespace andpg_tables. (Redshift is built with Postgres so the Postgres documentation for these system catalog tables is relevant for both types of database)

 

Consider an example where an organization has been managing GRANTS individually for their data team but has created a database group, named data_team, to manage these permissions going forward and now needs to GRANT the group on relevant tables and schemas. This example can be adapted to grant an individual user by removing the GROUP keyword and substituting a username for the group name. 

 

First GRANT access to the schema(s)

SELECT ‘GRANT USAGE ON SCHEMA ‘||NSPNAME||’ TO GROUP data_team;'
FROM pg_namespace
WHERE nspowner <> 1 -- this excludes system schemas
-- you most likely want to add additional conditions here such as 
AND nspname like ‘data_team_%’

Next to GRANT SELECT on all tables in those schemas:

SELECT ‘GRANT SELECT ON ALL TABLES IN SCHEMA ‘||NSPNAME||’ TO GROUP data_team;'
FROM pg_namespace
WHERE nspowner <> 1 – this excludes system schemas
– you may want to add additional conditions here such as 
AND nspname like ‘data_team_%’

Or to grant only on specific tables and they can be filtered for with a where clause, use the pg_tables catalog table if you need to .

SELECT ‘GRANT SELECT ON ‘||t.schemaname||’.’||t.tablename||’ TO GROUP data_team;’
FROM pg_tables t
– select only tables prefixed with ‘sales_’ and owned by the sales_robot user
WHERE t.tablename LIKE ‘sales_%’
AND t.tableowner = ‘sales_robot’

Any of these examples can be modified to GRANT different privileges or use different filtering in the WHERE Clause. See the references below for more information on creating GRANT statements. 

 

Other Notes/Tips

  • Granting schema and table access is best administered at the group/role level. See Group Database Privileges and Managing Database Permissions.

  • To grant privileges on an object, you must meet one of the following criteria:

    • Be the object owner

    • Be a superuser

    • Have a grant privilege for that object and privilege

  • Running a GRANT gives access to the existing schemas/tables at the time the grant is run. If new tables are added to the schema, they will need to be granted separately. See ALTER DEFAULT PRIVILEGES for more options around future objects.

  • Having privileges granted on a view does not require having privileges on the underlying tables.

  • Having privileges granted on a schema does not provide privileges on the tables in the schema. You need to grant access to the underlying tables explicitly.

  • Superusers can access all objects regardless of GRANT and REVOKE commands that set object privileges. To find out more about superusers on your clusters, please contact support@civisanalytics.com.

  •  Common permission problems to be aware of:

    • I can’t see the cluster anywhere in Platform
      • You probably haven’t been added to the corresponding Platform group yet!  Client Success can get that set up for you.
    • I can see the cluster, but the data pane looks pretty sparse. Just a ‘public’ or an ‘information_schema,’ but nothing else
      • Looks like you need to be granted schema privileges! USAGE lets you see the schema, CREATE lets you create new schemas. 
      • If there are no schemas in the cluster, it’s likely that your UI has not been populated with Redshift objects yet. Client Success can help update the UI view of your cluster if this is the case.
    • I see the schema, but there’s no tables inside and I know there should be 
      • Looks like you need to be granted table privileges! Usually select is sufficient, but scroll back up again to see if you need to be granted more!  You can also use the "Update available data" feature to enter the name of a schema or table that you know exists but Platform is not currently aware of.

 

References

 



Related articles

Comments

0 comments

Please sign in to leave a comment.