Grant SELECT on All Tables in PostgreSQL

Grant SELECT on All Tables in PostgreSQL

This PostgreSQL script creates a read-only user and grants SELECT privileges on all existing tables in a schema, plus ensures the same access is automatically applied to any future tables created in that schema.

Purpose and Overview

Third-party ETL tools, reporting tools, and analytics platforms often require a dedicated PostgreSQL user account with read-only access to all tables in a database. Granting SELECT on each table individually is tedious and error-prone. This script handles it in three statements and covers tables that do not exist yet.

SQL Script

1CREATE USER frisco;
2
3GRANT USAGE ON SCHEMA "public" TO frisco;
4GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO frisco;
5ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT ON TABLES TO frisco;

Code Breakdown

CREATE USER

1CREATE USER frisco;

Creates a new PostgreSQL user account. Replace frisco with the username required by your ETL tool or application. By default the new user has no login password — add WITH PASSWORD 'yourpassword' if the tool requires password authentication.

GRANT USAGE ON SCHEMA

1GRANT USAGE ON SCHEMA "public" TO frisco;

Grants the user permission to look up objects within the public schema. Without this, the user cannot resolve table names even if table-level SELECT is granted. USAGE on the schema is a prerequisite for any object-level access within it.

GRANT SELECT ON ALL TABLES

1GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO frisco;

Grants SELECT on every table that currently exists in the public schema in one statement. This covers all tables at the time the command is run.

ALTER DEFAULT PRIVILEGES

1ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT ON TABLES TO frisco;

This is the critical statement for long-term maintenance. It instructs PostgreSQL to automatically grant SELECT to frisco on any table created in the public schema in the future. Without this, new tables would be inaccessible to the user until privileges were manually re-granted.

Practical Applications

ETL and Data Pipeline Tools

Tools such as Stitch, Airbyte, and similar connectors replicate data out of PostgreSQL. They require a dedicated read-only database user. This script satisfies that requirement while ensuring newly added tables are replicated automatically without manual intervention.

Reporting and Analytics Access

BI platforms like Metabase, Tableau, and Redash benefit from a dedicated read-only user that cannot accidentally modify production data. This script creates the minimal permission set required.

Auditing and Compliance

Providing SELECT-only access to auditors or third-party reviewers limits exposure. The user can inspect data without any risk of writes, schema changes, or deletions.

Customization

Different Schema

Replace "public" with the target schema name if your tables live in a non-default schema:

1GRANT USAGE ON SCHEMA "myschema" TO frisco;
2GRANT SELECT ON ALL TABLES IN SCHEMA "myschema" TO frisco;
3ALTER DEFAULT PRIVILEGES IN SCHEMA "myschema" GRANT SELECT ON TABLES TO frisco;

Multiple Schemas

Run the three GRANT statements once per schema for each schema the user needs to access.

Adding a Password

1CREATE USER frisco WITH PASSWORD 'strongpassword';

Revoking Access

To remove access completely:

1REVOKE SELECT ON ALL TABLES IN SCHEMA "public" FROM frisco;
2REVOKE USAGE ON SCHEMA "public" FROM frisco;
3ALTER DEFAULT PRIVILEGES IN SCHEMA "public" REVOKE SELECT ON TABLES FROM frisco;
4DROP USER frisco;

Version Compatibility

Requires PostgreSQL 9.0 or later. Tested on PostgreSQL 9.x, 10.x, 11.x, 12.x, and 13.0. ALTER DEFAULT PRIVILEGES was introduced in PostgreSQL 9.0 and is available in all modern versions.

Best Practices

Use a dedicated user per tool: Avoid sharing credentials between ETL tools and reporting tools so access can be revoked independently.

Avoid granting to PUBLIC: Never grant broad privileges to the PUBLIC role. Always target a specific named user.

Document default privilege changes: ALTER DEFAULT PRIVILEGES affects future object creation by the current role. If multiple superusers create tables, each one may need to run the ALTER DEFAULT PRIVILEGES statement.

Review periodically: Use \ddp in psql or query pg_default_acl to audit default privilege settings on the schema.

References

• PostgreSQL Documentation - GRANT - Full reference for the GRANT command and privilege types
• PostgreSQL Documentation - ALTER DEFAULT PRIVILEGES - How default privileges work and how to configure them
• PostgreSQL Documentation - CREATE USER - Reference for creating database user accounts
• Source SQL Script - postgres_grant_select_all_tables.sql - Original script by Hari Sekhon