GRANT SELECT on All Tables in PostgreSQL — with Examples

calendar Mar 27, 2026 / · 11 min read · postgresql database administration sql queries security access control permissions etl postgres dba pg_default_acl  ·
Share on: linkedin copy

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 objects inside the schema; it only gives them permission to resolve names within it. A user with only USAGE can see that a table named orders exists but cannot SELECT from it. Conversely, table-level SELECT without schema USAGE produces a "permission denied for schema" error. Both grants are required for a working read-only setup.

The ALTER DEFAULT PRIVILEGES statement is tied to a specific grantor — the role that runs it, which is assumed to be the same role that will create future tables. On a team where multiple superusers create tables, each creator may need to run their own ALTER DEFAULT PRIVILEGES statement, 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 stable across all currently supported versions through 17. 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 syntactic sugar for CREATE ROLE ... WITH LOGIN — the difference being that a plain ROLE without the LOGIN attribute cannot authenticate. Replace frisco with the service-account name required by your tool. Without a WITH PASSWORD clause the account has no password and can only authenticate via peer or trust — 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. This is the most common source of "permission denied for schema public" errors when a DBA grants table privileges and forgets the schema prerequisite. USAGE does not allow object creation or enumeration beyond objects the user already has explicit access to.

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 phrase ALL TABLES also covers views, so the user can query view definitions without additional grants. It does not cover sequences, functions, or other object classes — those require their own GRANT statements when needed. 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 can be confirmed with \ddp in psql.

Key Permission Concepts

Object Ownership and Grantor Scope

Every PostgreSQL object has an owner, and default-privilege rules are per-owner. When a superuser runs ALTER DEFAULT PRIVILEGES, the rule applies only to tables that same superuser creates. A second DBA who creates tables under a different login does not trigger the same rule. On schemas with multiple table creators, each creator must run their own ALTER DEFAULT PRIVILEGES statement. 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 tables (relations). The ACL entry will include frisco=r/postgres (or the relevant owner). If this row is absent, future 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. The group role also makes audit easier: one \du check shows who belongs to the read-only group rather than reviewing per-user grants against individual tables.

Scope: TABLES vs SEQUENCES vs FUNCTIONS

GRANT SELECT ON ALL TABLES does not cover sequences. If a reporting tool calls currval() or reads sequence state directly, a separate GRANT USAGE ON ALL SEQUENCES IN SCHEMA "public" TO frisco is required. Function execution requires GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA "public". Each object class is its own GRANT family — a fact that trips up scripts copied from MySQL environments, where a single ALL PRIVILEGES covers most objects.

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. If frisco has SELECT on the full table, column-level restrictions are irrelevant for that user — column grants matter only when the user has no table-level SELECT. Column grants are visible in information_schema.role_column_grants.

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. Enabling RLS without a permissive policy produces an empty result — not an error — which is a common source of "my SELECT returns zero rows" confusion on tables where RLS was added after initial setup. 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

Tools such as Stitch, Airbyte, and Fivetran replicate data out of PostgreSQL by querying tables as a dedicated service-account user. This script satisfies that requirement while ensuring newly added source tables are included automatically without manual re-grant work after each schema change.

Reporting and Analytics Access

BI platforms like Metabase, Tableau, and Redash connect as a dedicated user. Configuring that user as read-only before connecting the tool prevents the common mistake of using a superuser or table-owner account for reporting queries — a mistake that allows an accidental UPDATE from inside a BI UI to commit to production.

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. For regulatory environments, the read-only account also cannot create transactions that circumvent audit logs.

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.

Customization

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;

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.

Adding a Password

1CREATE USER frisco WITH PASSWORD 'strongpassword';

Set password_encryption = scram-sha-256 in postgresql.conf before creating the password — SCRAM is the default in PostgreSQL 14+ and is more resistant to offline cracking than 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;

Version Compatibility

ALTER DEFAULT PRIVILEGES and GRANT SELECT ON ALL TABLES IN SCHEMA were both introduced in PostgreSQL 9.0 and are stable across all currently supported versions through PostgreSQL 17.

PostgreSQL 14 added the pg_read_all_data built-in role, which grants SELECT on all tables, views, and sequences in all schemas without per-schema grant work. It is useful for cluster-wide monitoring accounts but provides no schema-level scoping — a user with pg_read_all_data can read every schema. 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 changed the default privilege on the public schema: the implicit CREATE grant to all users that existed in earlier versions was removed. This does not affect the SELECT-grant pattern, but it means PostgreSQL 15+ environments start with a stricter baseline — new users cannot create objects in public without an explicit GRANT CREATE ON SCHEMA public. If migrating from PostgreSQL 14 or earlier and your application relied on that implicit CREATE grant, it must now be made explicit.

PostgreSQL 17 deprecated MD5 password hashing. The password_encryption parameter still accepts md5 on 17 for backward compatibility, but existing MD5-hashed passwords generate deprecation log warnings. Rotate service-account passwords to SCRAM-SHA-256 on any cluster running PostgreSQL 17 or later.

Best Practices

  • Use a dedicated user per tool — revoking one tool's access becomes a targeted DROP USER without affecting others; shared credentials make selective revocation impossible.
  • 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 — broad grants to the PUBLIC pseudo-role apply to every user including future accounts; always target a named role.
  • Audit pg_default_acl after schema changes — a schema rename or a new DBA creating tables can silently break default-privilege coverage; run the audit query after any structural change.
  • 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.
  • Pair with pg_hba.conf restrictions — a read-only database role is one layer of protection; restricting the account to specific source IPs adds a network-layer guard against credential misuse.
  • Use SCRAM-SHA-256 for all new accounts — configure password_encryption = scram-sha-256 in postgresql.conf before creating service-account passwords; MD5 is deprecated in PostgreSQL 17.
  • 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

Posts in this series