PostgreSQL 13 - User without assign any role

PostgreSQL
1

Hi,

I have a client using PostgreSQL 13, he complaint that he created a user which is not superuser and did not assign any role to the user, then this user can access to any schema of data, but it sudden cant access from Sep 2022. Is this true that a user without superuser and role assign can access all schema inside the database?

I try research on the user and role, but only found PostgreSQL 14 have the default role pg_read_all_data or pg_write_all_data. But the client does not accept, and he confirm that he didn’t enable the superuser.

Thanks in advance.

1 Like
2

Hi,

Let me see if I understand the question: you are asking if the privileges of a ROLE allows it to access the schema and query those tables found in them.

The answer is no: without granting permissions the ROLE can only access the PUBLIC schema, in postgres 13.

Recall: checking permissions for a given user account can be obtained by logging into the database using psql and executing the meta-command “\dp”. Until permissions have been assigned no privileges will be shown, the assumption being that only the superuser and the owner of the schema and table can access them.

In regards to postgres ROLES, pg_read_all_data and pg_write_all_data for example, using GRANT will make an ordinary user member to them allowing access to the the various metrics but not necessarily user-defined tables.

Hope this helps

1 Like
3

Hi Sir,

From your reply, so it is true that a user only allows to access any tables in the public schema, but NOT other schema, right? The user only allows to access the specific schema while a grant permission given. Thank you for your reply.

Cheers n have a good day. Thank you.

1 Like
4

Your understanding is correct that the owner of an object controls its access by other uses in the PUBLIC SCHEMA.
In regards to tables, indexes, sequences etc that are located in other SCHEMA that is not in PUBLIC, additional permissions are required granting access by either the owner of the schema or the superuser.

The following code snippet demonstrates how to set the permissions for a user to access a table in a schema that is owned by another ROLE, which in this case is postgres:

EXAMPLE:

tmp=# create role usr1 with login password 'usr1';
CREATE ROLE
tmp=# 
tmp=# create schema myschema
tmp-#     create table t1(id int);
CREATE SCHEMA
tmp=# 
tmp=# set role usr1;
SET
tmp=> select * from myschema.t1;
ERROR:  permission denied for schema myschema
LINE 1: select * from myschema.t1;
                      ^
tmp=> 
tmp=> reset role;
RESET
tmp=# grant all privileges on all tables in schema myschema to usr1;
GRANT
tmp=# 
tmp=# set role usr1;
SET
tmp=> select * from myschema.t1;
ERROR:  permission denied for schema myschema
LINE 1: select * from myschema.t1;
                      ^
tmp=> 
tmp=> reset role;
RESET
tmp=# grant usage on schema myschema to usr1;
GRANT
tmp=# 
tmp=# set role usr1;
SET
tmp=> select * from myschema.t1;
 id 
----
(0 rows)

Hope this helps.

2 Likes