Oracle vs PostgreSQL: Schema-Level Privileges and Access to Future Tables

Managing privileges across multiple database objects can become challenging, especially when users need access to all tables within a particular schema.

For example, suppose we have a schema named MAZANDARAN and want to grant a user named VAHID read access to all its tables.

There are different approaches to achieving this in Oracle and PostgreSQL.

Starting with Oracle AI Database 26ai, schema-level privileges provide a convenient way to grant system privileges on a specific schema without having to grant privileges individually on each object.

PostgreSQL provides several mechanisms for this purpose, including GRANT … ON ALL TABLES IN SCHEMA, ALTER DEFAULT PRIVILEGES, and the predefined role pg_read_all_data.

Let’s examine these approaches.

1. Oracle : Schema-Level Privileges

Starting with Oracle AI Database 26ai, we can grant certain system privileges on a specific schema.

For example, suppose we want to grant VAHID read access to all existing and future tables in the MAZANDARAN schema.

First, create the user and grant the required connection privilege:

SQL> create user vahid identified by vahid;

User created.

SQL> grant create session to vahid;

Grant succeeded.

Now, grant the schema-level privilege:

SQL> GRANT SELECT ANY TABLE ON SCHEMA MAZANDARAN TO VAHID;

Grant succeeded.

Unlike the traditional SELECT ANY TABLE system privilege, this grant is restricted to the specified schema.

Let’s test it:

SQL>  conn vahid/vahid@192.168.1.40:1521/vahiddb
Connected.

SQL> select * from MAZANDARAN.person;

  ID  FNAME     LNAME           SAL
----  --------- -------------- ----
   1  Vahid     Yousefzadeh       5
   2  Nader     DastNeshan        6
   3  Kourosh   Barmak            6

Access to future tables

One of the most useful aspects of schema-level privileges is that they apply to both existing and future objects.

For example, if the MAZANDARAN user creates another table:

SQL> conn mazandaran/a@192.168.1.40:1521/vahiddb

Connected.

SQL> create table mazandaran.person99 as select * from mazandaran.person;

Table created.

The VAHID user can immediately query the new table without requiring another GRANT statement:

SQL> conn vahid/vahid@192.168.1.40:1521/vahiddb
Connected.
SQL> select * from mazandaran.person99;

  ID  FNAME     LNAME           SAL
----  --------- -------------- ----
   1  Vahid     Yousefzadeh       5
   2  Nader     DastNeshan        6
   3  Kourosh   Barmak            6

This functionality is not dependent on the user who creates the table.

For example, even if SYS creates a new table in the MAZANDARAN schema, VAHID can still access it:

SQL> show user;
USER is "SYS"
SQL> create table mazandaran.person100 as select * from mazandaran.person;

Table created.

SQL> conn vahid/vahid@192.168.1.40:1521/vahiddb
Connected.
SQL> select * from mazandaran.person100;

  ID  FNAME     LNAME           SAL
----  --------- -------------- ----
   1  Vahid     Yousefzadeh       5
   2  Nader     DastNeshan        6
   3  Kourosh   Barmak            6

As demonstrated, Oracle AI database 26ai schema-level privileges simplify privilege management because the grant automatically covers new objects created within the specified schema.

2. PostgreSQL: Granting Access to Existing Tables

Let’s implement a similar scenario in PostgreSQL.

Suppose we have two schemas in the vahiddb database:

vahiddb=# \dt mazandaran.*

   Schema   |  Name  | Type  |  Owner
------------+--------+-------+----------
 mazandaran | person | table | postgres
(1 row)
vahiddb=# \dt gilan.*

           List of tables
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 gilan  | person | table | postgres
(1 row)

First, create a user named vahid:

vahiddb=# CREATE USER vahid WITH PASSWORD 'vahid';
CREATE ROLE

Let’s connect using this account and try to query the existing table:

vahiddb=# \c vahiddb vahid
You are now connected to database "vahiddb" as user "vahid".

vahiddb=> select * from mazandaran.person;
ERROR:  permission denied for schema mazandaran
LINE 1: select * from mazandaran.person;

In PostgreSQL, accessing a table requires appropriate privileges on both the schema and the table.

Therefore, we need to grant USAGE on the schema and SELECT on its existing tables.

As the postgres user:

vahiddb=> \c vahiddb postgres
You are now connected to database "vahiddb" as user "postgres".

vahiddb=# GRANT USAGE ON SCHEMA mazandaran TO vahid;
GRANT
vahiddb=# GRANT SELECT ON ALL TABLES IN SCHEMA mazandaran TO vahid;
GRANT

Now, VAHID can query the existing table:

vahiddb=# \c vahiddb vahid
You are now connected to database "vahiddb" as user "vahid".
vahiddb=>  select * from mazandaran.person;
 id |  fname  |    lname    | sal
----+---------+-------------+-----
  1 | Vahid   | Yousefzadeh |   5
  2 | Nader   | DastNeshan  |   6
  3 | Kourosh | Barmak      |   6
(3 rows)

However, there is an important difference between PostgreSQL and Oracle 26ai.

GRANT SELECT ON ALL TABLES IN SCHEMA applies to existing tables. It does not automatically grant access to tables created in the future.

Let’s demonstrate this behavior.

3. PostgreSQL: Access to Future Tables Using ALTER DEFAULT PRIVILEGES

Suppose the postgres user creates another table:

vahiddb=> \c vahiddb postgres
You are now connected to database "vahiddb" as user "postgres".

vahiddb=# create table mazandaran.newtbl as select * from mazandaran.person;
SELECT 3

Now, connect as vahid:

vahiddb=>  \c vahiddb vahid
You are now connected to database "vahiddb" as user "vahid".

vahiddb=> select * from mazandaran.newtbl;
ERROR:  permission denied for table newtbl

As expected, VAHID cannot access the newly created table.

To solve this problem, PostgreSQL provides the ALTER DEFAULT PRIVILEGES command.

For example:

vahiddb=>  \c vahiddb postgres
You are now connected to database "vahiddb" as user "postgres".

vahiddb=# ALTER DEFAULT PRIVILEGES
FOR ROLE postgres
IN SCHEMA mazandaran
GRANT SELECT ON TABLES TO vahid;
ALTER DEFAULT PRIVILEGES

This command configures default privileges for tables created by the postgres role in the mazandaran schema.

Important: ALTER DEFAULT PRIVILEGES does not modify privileges on existing objects. It only affects objects created after the command is executed.

Therefore, VAHID still cannot access the previously created newtbl.

We need to grant access to that table separately:

vahiddb=> \c vahiddb postgres
You are now connected to database "vahiddb" as user "postgres".

vahiddb=# GRANT SELECT ON mazandaran.newtbl TO vahid;
GRANT


vahiddb=#  \c vahiddb vahid
You are now connected to database "vahiddb" as user "vahid".

vahiddb=> select * from mazandaran.newtbl;
 id |  fname  |    lname    | sal
----+---------+-------------+-----
  1 | Vahid   | Yousefzadeh |   5
  2 | Nader   | DastNeshan  |   6
  3 | Kourosh | Barmak      |   6
(3 rows)

Let’s create another table to verify that the default privileges work:

vahiddb=>  \c vahiddb postgres
You are now connected to database "vahiddb" as user "postgres".

vahiddb=# create table mazandaran.newtbl2 as select * from mazandaran.person;
SELECT 3


vahiddb=# \c vahiddb vahid
You are now connected to database "vahiddb" as user "vahid".

vahiddb=> select * from mazandaran.newtbl2;
 id |  fname  |    lname    | sal
----+---------+-------------+-----
  1 | Vahid   | Yousefzadeh |   5
  2 | Nader   | DastNeshan  |   6
  3 | Kourosh | Barmak      |   6
(3 rows)

An important limitation of ALTER DEFAULT PRIVILEGES

There is another important consideration.

Default privileges are associated with the role that creates the object, not simply the schema in which the object is created.

For example, suppose another superuser named usef creates a table in the mazandaran schema:

vahiddb=> \c vahiddb usef
You are now connected to database "vahiddb" as user "usef".

vahiddb=# create table mazandaran.newtbl99  as select * from mazandaran.person;
SELECT 3

Although the table belongs to the mazandaran schema, it is owned by usef.

The default privileges configured for postgres do not automatically apply to objects created by usef.

Consequently, vahid cannot query this table:

vahiddb=#  \c vahiddb vahid
You are now connected to database "vahiddb" as user "vahid".

vahiddb=> select * from mazandaran.newtbl99;
ERROR:  permission denied for table newtbl99

We could configure default privileges separately for every role that creates objects in this schema.

However, managing this configuration can become complicated in environments where multiple users or administrators create database objects.

Is there a simpler solution?

4. PostgreSQL: Using the pg_read_all_data Predefined Role

PostgreSQL provides a predefined role named pg_read_all_data that can simplify this requirement.

This role allows reading all data!!!(tables, views, and sequences), as if the user had the corresponding SELECT privileges on those objects and USAGE privileges on all schemas.

It also applies to objects created in the future.

Let’s grant this role to vahid.

As a superuser:

postgres=# \c vahiddb usef
You are now connected to database "vahiddb" as user "usef".

vahiddb=# grant pg_read_all_data  to vahid;
GRANT ROLE


vahiddb=# create table mazandaran.newtbl100 as select * from mazandaran.person;
SELECT 3

vahiddb=# \c vahiddb vahid
You are now connected to database "vahiddb" as user "vahid".

vahiddb=> select * from mazandaran.newtbl100;
 id |  fname  |    lname    | sal
----+---------+-------------+-----
  1 | Vahid   | Yousefzadeh |   5
  2 | Nader   | DastNeshan  |   6
  3 | Kourosh | Barmak      |   6
(3 rows)

But there is something else worth mentioning.

Unlike the previous approaches, pg_read_all_data is not restricted to a particular schema.

For example, VAHID can also access tables in the GILAN schema without receiving any additional privileges:

vahiddb=# \c vahiddb vahid
You are now connected to database "vahiddb" as user "vahid".

vahiddb=> select * from gilan.person;
 id |  fname  |    lname    | sal
----+---------+-------------+-----
  1 | Vahid   | Yousefzadeh |   5
  2 | Nader   | DastNeshan  |   6
  3 | Kourosh | Barmak      |   6
(3 rows)

This is because pg_read_all_data provides read access across all schemas, rather than restricting access to a specific schema.

One important security consideration: pg_read_all_data does not bypass Row-Level Security (RLS) policies. If RLS is enabled, the applicable policies still control which rows the user can see.

As you can see, the pg_read_all_data predefined role is similar to Oracle’s SELECT ANY TABLE system privilege because both provide read access to tables across the database.