GRANT SELECT on All Tables in PostgreSQL — with Examples

Grant SELECT on All Tables in PostgreSQL

Third-party ETL connectors, BI platforms, and audit users all need read access to PostgreSQL tables — without write permissions that could corrupt production data. Granting SELECT table by table works until someone adds a new table and the connector silently skips it; ALTER DEFAULT PRIVILEGES is the statement that closes that gap, applying SELECT to every future table the moment it is created.

Purpose and Overview

The standard approach when standing up a read-only database account is to grant SELECT on each table individually. That works on day one. By day thirty, after a developer adds five new tables, the ETL user is silently missing data from those tables — no error, no warning, just invisible gaps in the pipeline. The three-statement pattern in this script solves both the present and the future: GRANT SELECT ON ALL TABLES covers everything that exists now, and ALTER DEFAULT PRIVILEGES handles everything created afterward.

PostgreSQL's privilege model is layered. Schema-level USAGE does not give a user access to the data in the schema's objects; it allows the grantee to look up objects within the schema, provided each object's own privilege requirements are also met. A user with only USAGE cannot SELECT from a table that has no grant for them. Conversely, table-level SELECT without schema USAGE produces a "permission denied for schema" error. Both grants are needed for a working read-only setup.

The ALTER DEFAULT PRIVILEGES statement is tied to a specific grantor — the role that runs it, unless FOR ROLE names another one. The rule only affects tables that role creates later. On a team where several roles create tables, each creator needs a default-privilege rule of its own, or coverage will be incomplete. PostgreSQL 14 introduced the pg_read_all_data built-in role as a shortcut for cluster-wide read access, but ALTER DEFAULT PRIVILEGES remains the correct tool when access must be scoped to a specific schema or subset of objects.

The four-statement script below covers the full setup: user creation, schema permission, table access for all existing objects, and automatic table access for all future objects.

Sample Code

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;

Notes: Requires PostgreSQL 9.0 or later. ALTER DEFAULT PRIVILEGES was introduced in PostgreSQL 9.0 and is available in all currently supported versions. Replace "public" with the target schema name and frisco with the required account name.

Code Breakdown

The script builds access in four distinct layers: account creation, schema visibility, object access, and future-proof automation.

CREATE USER

1CREATE USER frisco;

Creates a new PostgreSQL login role. CREATE USER is an alias for CREATE ROLE; the only difference is that CREATE USER assumes LOGIN, whereas CREATE ROLE assumes NOLOGIN. Replace frisco with the service-account name required by your tool. Without a WITH PASSWORD clause the password is set to null and password authentication will always fail for that user — add WITH PASSWORD 'yourpassword' for password-based connections. A more explicit alternative is CREATE ROLE frisco LOGIN, which makes the login attribute visible in the DDL.

GRANT USAGE ON SCHEMA

1GRANT USAGE ON SCHEMA "public" TO frisco;

USAGE on a schema grants the ability to resolve object names within it. Without this, table-level SELECT grants are unreachable — PostgreSQL enforces the permission check at the schema level first, then the object level. A missing schema grant shows up as a "permission denied for schema" error. USAGE does not allow object creation, and withholding it does not hide object names; without it, the names can still be seen by querying the system catalogs.

GRANT SELECT ON ALL TABLES

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

This single statement grants SELECT on every table and view that currently exists in the named schema. The PostgreSQL documentation states that ALL TABLES also affects views and foreign tables. It does not cover sequences or functions; permissions on those are set separately. This is a one-time catch-up for existing objects; ALTER DEFAULT PRIVILEGES handles everything created afterward.

ALTER DEFAULT PRIVILEGES

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

This is the statement most often omitted. Without it, any table created after this script runs is invisible to frisco until privileges are manually re-granted. ALTER DEFAULT PRIVILEGES instructs PostgreSQL to apply the specified grant automatically whenever the current role creates a new table in the named schema. The grant fires at object-creation time, not lazily on first access, so a newly created table is accessible immediately. The resulting rule is stored in pg_default_acl, and psql's \ddp command shows existing default-privilege assignments.

Using a Different Schema

Replace "public" with the target schema name:

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;

Covering Multiple Schemas with a DO Block

When the user needs access to several schemas, a DO block avoids repeating the three-statement pattern manually:

 1DO $$
 2DECLARE
 3    s text;
 4BEGIN
 5    FOR s IN
 6        SELECT schema_name
 7        FROM information_schema.schemata
 8        WHERE schema_name NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
 9    LOOP
10        EXECUTE format('GRANT USAGE ON SCHEMA %I TO frisco', s);
11        EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO frisco', s);
12        EXECUTE format(
13            'ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO frisco', s);
14    END LOOP;
15END;
16$$;

The %I format specifier in format() handles quoting correctly for schema names that contain uppercase letters, spaces, or reserved words. Adjust the exclusion list to match your cluster's system schemas. This block is not part of the original seed script; it is a loop over the same three statements.

Adding a Password

1CREATE USER frisco WITH PASSWORD 'strongpassword';

Check password_encryption before creating the password. The PostgreSQL 14 release notes changed its default to scram-sha-256; earlier versions defaulted to md5.

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;

Key Permission Concepts

Object Ownership and Grantor Scope

Default-privilege rules belong to the role that creates the objects. Per the PostgreSQL documentation, new object permissions are only affected by the default privileges of the current role, and are not inherited from any roles the current role is a member of. A second DBA who creates tables under a different login does not trigger the rule set up by the first. On schemas with several table creators, each creator needs its own rule — run by that creator, or by a member of that role using ALTER DEFAULT PRIVILEGES FOR ROLE creator. Querying pg_default_acl confirms which grantors have active rules:

1SELECT pg_get_userbyid(defaclrole) AS grantor, defaclacl
2FROM pg_default_acl
3WHERE defaclobjtype = 'r';

Object type r is relations (tables and views). The ACL entry will include frisco=r/postgres (or the relevant creating role), where r is the SELECT privilege and the name after the slash is the grantor. If no row exists for the role that creates your tables, those tables will not be automatically accessible to frisco.

The Role-Based Alternative

PostgreSQL roles can be members of other roles. A group-role pattern reduces ongoing maintenance:

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

Adding a second read-only user later requires only GRANT readonly_group TO new_user — no repeat of the schema and table grants.

Scope: TABLES vs SEQUENCES vs FUNCTIONS

GRANT SELECT ON ALL TABLES does not cover sequences: the documentation says granting on a table does not automatically extend permissions to the sequences the table uses, including sequences behind SERIAL columns. For sequences, the SELECT privilege allows currval, and USAGE allows both currval and nextval. A reporting tool that only reads therefore needs GRANT SELECT ON ALL SEQUENCES IN SCHEMA "public" TO frisco; USAGE would also permit nextval, which advances the sequence.

Functions are a separate class too. By default PUBLIC already holds EXECUTE on functions and procedures, so GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA "public" TO frisco matters only after that default has been revoked. Note that ALL FUNCTIONS does not include procedures; the ALL ROUTINES form does.

Column-Level Privileges

PostgreSQL supports granting SELECT at the column level, not just the table level. For tables with sensitive columns — internal cost figures, payment data, or identifiers — this allows read access to be shaped more precisely:

1GRANT SELECT (order_id, customer_name, order_total) ON orders TO frisco;

Column-level grants coexist with table-level grants. A user may SELECT a column if they hold the privilege on either the column or the whole table, so if frisco has SELECT on the full table, a column-level grant adds nothing for that user, and a column-level revoke does not remove the table-level grant.

Row-Level Security Interaction

GRANT SELECT and ALTER DEFAULT PRIVILEGES control whether a role is permitted to execute a query against a table. Row-Level Security (RLS) controls which rows are returned when the query runs. The two mechanisms are independent layers.

A read-only user created with this script sees all rows unless the target table has RLS enabled with a policy that restricts the role. Per the documentation, if RLS is enabled and no policy exists for the table, a default-deny policy is used and no rows are visible, so the query returns an empty result rather than an error. Table owners normally bypass row security. Check SELECT relrowsecurity FROM pg_class WHERE relname = 'your_table' to confirm RLS status before assuming a missing privilege is the cause.

Practical Applications

ETL and Data Pipeline Tools

Replication and ETL connectors read PostgreSQL tables as a dedicated service-account user. Fivetran's PostgreSQL setup guide, for example, uses ALTER DEFAULT PRIVILEGES so that future tables in the schema are also accessible to the Fivetran user. This script follows the same pattern, so newly added source tables are included without manual re-grant work after each schema change.

Reporting and Analytics Access

BI and reporting tools connect as a dedicated database user. Making that user read-only before connecting the tool avoids using a superuser or table-owner account for reporting queries, which would let a write statement run from the reporting tool.

Auditing and Compliance

Providing SELECT-only access to auditors or third-party reviewers limits exposure. The account can read the granted tables but holds no privilege to write to them or change the schema.

Developer Sandbox Access on Staging

On staging databases loaded from a production snapshot, granting developers a read-only account against the production schema copy lets them write exploratory queries without risk of accidentally committing a write transaction in the wrong environment.

Version Compatibility

The PostgreSQL 9.0 release notes list both ALTER DEFAULT PRIVILEGES and the GRANT/REVOKE IN SCHEMA clause as new, and both are available in all currently supported versions.

PostgreSQL 14 added the pg_read_all_data predefined role. The documentation describes it as allowing reading of all data (tables, views, sequences), as if having SELECT rights on those objects and USAGE rights on all schemas. It does not bypass row-level security policies. It suits cluster-wide read access, but it has no schema-level scoping. The script above remains the correct approach when access must be restricted to specific schemas or when different tools require different schema-level boundaries.

PostgreSQL 15 removed the PUBLIC creation permission on the public schema. This does not affect the SELECT-grant pattern. The release notes say the change applies to new database clusters and to newly created databases in existing clusters, and that upgrading a cluster or restoring a dump preserves public's existing permissions. So an application that relied on the old CREATE grant needs an explicit GRANT CREATE ON SCHEMA public only in a new cluster or new database.

PostgreSQL 18 deprecated MD5 password authentication: support for MD5 passwords will be removed in a future major version, and CREATE ROLE and ALTER ROLE now emit deprecation warnings when setting MD5 passwords. The same notes say the deprecation warnings can be disabled with the md5_password_warnings parameter.

Best Practices

• Use a dedicated user per tool — revoking one tool's access becomes a targeted DROP USER without affecting others.
• Prefer a read-only group role — add users to a group role rather than granting directly to each username; onboarding a second read-only user becomes a single GRANT role TO user statement.
• Avoid granting to PUBLIC — grants to the PUBLIC pseudo-role apply to every role, including ones created later; target a named role.
• Audit pg_default_acl when a new role starts creating tables — a new table creator has no default-privilege rule until one is set; run the audit query after any change in who creates tables.
• Run ALTER DEFAULT PRIVILEGES as each table-creating role — default-privilege rules are per-grantor; every DBA who creates tables in the schema needs their own active rule.
• Use SCRAM-SHA-256 for new accounts — check password_encryption before creating service-account passwords; MD5 passwords are deprecated in PostgreSQL 18.
• Check for RLS before assuming missing privilege — a read-only user returning zero rows may be hitting a restrictive Row-Level Security policy rather than an absent grant; verify relrowsecurity in pg_class for the relevant tables.

References

• PostgreSQL Documentation — GRANT — states that ALL TABLES also affects views and foreign tables, and that table permissions do not extend to sequences.
• PostgreSQL Documentation — ALTER DEFAULT PRIVILEGES — new object permissions are affected only by the current role's default privileges; FOR ROLE and \ddp.
• PostgreSQL Documentation — Privileges — what SELECT, USAGE and EXECUTE mean for sequences, schemas and functions, and the defaults held by PUBLIC.
• PostgreSQL Documentation — CREATE USER — CREATE USER is an alias for CREATE ROLE that assumes LOGIN.
• PostgreSQL Documentation — Row Security Policies — with no policy on a table, a default-deny policy applies; owners normally bypass it.
• PostgreSQL Documentation — Predefined Roles — describes pg_read_all_data.
• PostgreSQL 15 Release Notes — removal of PUBLIC creation permission on the public schema and which clusters it affects.
• Fivetran — PostgreSQL setup guide — example of a connector setup that uses ALTER DEFAULT PRIVILEGES for future tables.
• HariSekhon/SQL-scripts: postgres_grant_select_all_tables.sql — original seed script covering read-only user creation and schema privilege grants in one reusable block.
• HariSekhon/SQL-scripts LICENSE (MIT) — Copyright 2020 Hari Sekhon. The script in this post is used under the MIT License.