Restricting user to see schema structure

On 5/12/22 03:04, Neeraj M R wrote:

Hi,

Is there anyway that we can restrict a user from seeing the schema
structure. I can restrict the user from accessing the data in the schema
but the user is still able to see the table names and what all columns
are present in them.

No.

Thanks & Regards
Neeraj

--
Adrian Klaver
adrian.klaver@aklaver.com

adrian.klaver@aklaver.com wrote:

neerajmr12219@gmail.com wrote:

Is there anyway that we can restrict a user from seeing the schema structure. I can restrict the user from accessing the data in the schema but the user is still able to see the table names and what all columns are present in them.

No.

Here’s something that you can do, Neeraj. But you have to design your app this way from the start. It'd be hard to retrofit without a re-write.

Design (and document the practice) to encapsulate the database functionality (i.e. the business functions that the client side app must perform) behind an API exposed as user-defined functions that return query results for SELECT operations and outcome statuses (e.g. "success", "This nickname is taken. Try a different one", "Unexpected error. Report incident ID NNNNN to Support"). JSON is a convenient representation for all possible return values.

Use a regime of users, schemas, and privilege grants (functions having "security definer" mode) to implement the functionality. Create a dedicated user-and-schema to expose the API and nothing else. This will own only functions that are thin jackets to invoke the real work-doing functions that are hidden from the client. Allow clients to authorize ONLY as the API-owning user. Grant "execute" on its functions to what's needed elsewhere.

I've prototyped this scheme. It seems to work as designed. A client that connects with psql (or any other tool) can list the API functions and whatever \df and \sf show. (notice that \d will reveal nothing.)But doing this reveals only the names of the functions that are called (which will be identical to the jacket names—so no risk here) and the name(s) of the schema(s) where they live (so a minor theoretical risk here).

Full disclosure: I've never done this in anger.

Note: I believe this approach to be nothing other than the application of the time-honored principles (decades old) of modular software construction (where the entire database is a top-level module in the over all app's decomposition). It brings the security benefit that I sketched along with all the other famous benefits of modular programming—esp. e.g. the client is shielded from table design changes.

On 5/12/22 11:29, Bryn Llewellyn wrote:

I've prototyped this scheme. It seems to work as designed. A client that connects with psql (or any other tool) can list the API functions and whatever \df and \sf show. (notice that \d will reveal nothing.)But doing this reveals only the names of the functions that are called (which will be identical to the jacket names—so no risk here) and the name(s) of the schema(s) where they live (so a minor theoretical risk here).

Full disclosure: I've never done this in anger.

Try select * from pg_class or select * from pg_attribute or any of the
other system catalogs.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, May 12, 2022 at 11:44 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 5/12/22 11:29, Bryn Llewellyn wrote:

I've prototyped this scheme. It seems to work as designed. A client that

connects with psql (or any other tool) can list the API functions and
whatever \df and \sf show. (notice that \d will reveal nothing.)But doing
this reveals only the names of the functions that are called (which will be
identical to the jacket names—so no risk here) and the name(s) of the
schema(s) where they live (so a minor theoretical risk here).

Full disclosure: I've never done this in anger.

Try select * from pg_class or select * from pg_attribute or any of the
other system catalogs.

Which is exactly what most GUI applications that provide object browsing
and viewing are going to use.

David J.

david.g.johnston@gmail.com wrote:

adrian.klaver@aklaver.com wrote:

bryn@yugabyte.com wrote:

I've prototyped this scheme. It seems to work as designed. A client that connects with psql (or any other tool) can list the API functions and whatever \df and \sf show. (notice that \d will reveal nothing.)But doing this reveals only the names of the functions that are called (which will be identical to the jacket names—so no risk here) and the name(s) of the schema(s) where they live (so a minor theoretical risk here).

Full disclosure: I've never done this in anger.

Try select * from pg_class or select * from pg_attribute or any of the other system catalogs.

Which is exactly what most GUI applications that provide object browsing and viewing are going to use.

Oops. I made the unforgivable mistake of saying something without first having run a script to demonstrate what I'd planned to say. I'm embarrassed (again). I confused my memory of the proof-of-concept demo that I'd coded in PG with what, back in the day, I'd coded in Oracle Database. (The visibility notions in ORCL are very much more granular than in PG.)

I re-coded and re-ran my PG proof-of-concept demo. It creates a dedicated database "app" and dedicated users "data", "code", and "api" to own the application objects, each in a schema with the same name as the owning user. These have the purposes that their names suggest. As it progresses, it creates the table "data.t", the function "code.f", and the function "api.f" (as a minimal jacket to invoke "code.f"). Finally, it creates the user "client" with no schema but with "usage" on the schema "api" and "execute" on (in general) each of its functions. The idea is that "client" has been explicitly given only the privileges that are necessary to expose the functionality that has been designed for use by connecting client sessions.

When the setup is done, and when connected as "client". it runs a UNION query using "pg_class", "pg_proc", and "pg_namespace". I restricted it to exclude all the owned by the installation (in my case, an MacOS, "Bllewell").

As you'd all expect, this is the result:

owner | schema_name | object_kind | object_name
-------+-------------+-------------+-------------
api | api | function | f
code | code | function | f
data | data | index | t_pkey
data | data | sequence | t_k_seq
data | data | table | t

Without the restriction, and again as you'd all expect, the query shows every single schema object in the entire database. Other queries show all the users in the cluster. Queries like the ones I used here allow "\d", "\df", and the like to show lots of the facts about each kind of object in the entire database. And, yes, I did know this.

However, the design decision that, way back when, leads to this outcome does surprise me. The principle of least privilege insists that (in the database regime) you can create users that can do exactly and only what they need to do. This implies that my "client" should not be able to list all the objects in the database (and all the users in the cluster).

Here's what the exercise taught me: When connected in psql as "client", and with "\set VERBOSITY verbose", this:

select * from data.t;

causes this expected error:

ERROR: 42501: permission denied for schema data

But this:

sf code.f

causes this unexpectedly spelled error (with no error code):

ERROR: permission denied for schema code

Nevertheless, this:

select pg_catalog.pg_get_functiondef((
select p.oid
from pg_catalog.pg_proc p
join pg_catalog.pg_namespace n
on p.pronamespace = n.oid
where
p.proowner::regrole::text = 'code' and
n.nspname::text = 'code' and
p.prokind = 'f' and
p.proname::text = 'f'
));

sidesteps the check that "\sf" uses, runs without error and produces this result:

CREATE OR REPLACE FUNCTION code.f() +
RETURNS integer +
LANGUAGE plpgsql +
SECURITY DEFINER +
AS $function$ +
begin +
return (select count(*) from data.t);+
end; +
$function$ +

So it seems that the implementation of "\sf" adds its own ad hoc privilege checks and, when needed, outputs an error message that its own code generates. Strange.

I see now that my quest to handle, and sanitize, unexpected errors in PL/pgSQL exception sections has only rather limited value. It can aid usability, for example by changing "unique_violation" (with all sorts of stuff about line numbers and the like) to "This nickname is taken". However, in the case of errors like this:

22001: value too long for type character varying(8)

while again the sanitized "Nickname must be no more than eight characters" is nice, it doesn't prevent the patient hacker who connects as "client" from studying all the application's code, looking at all the table definitions, and working out the scenarios that would lead to this raw error if it weren't prevented from leaking to the client program.

Maybe this entire discussion is moot when hackers can read the C code of PG's implementation…

On Thu, May 12, 2022 at 7:35 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:

However, the design decision that, way back when, leads to this outcome
does surprise me. The principle of least privilege insists that (in the
database regime) you can create users that can do exactly and only what
they need to do. This implies that my "client" should not be able to list
all the objects in the database (and all the users in the cluster).

While I tend to agree, there is a degree of symmetry in this Open Source
Database having a catalog that itself is basically Open Source.

I find it telling that the cryptography field believes it is a net positive
for their algorithms to be published, eschewing security by obscurity.
Only (some of) the input data, private key or otherwise, has to have a
private component.

If there was any motivation to improve PostgreSQL on this front I'd like
them to start with "routine bodies" being hidden away from inspection. I'm
much less concerned about pg_class or even knowing the names of things.

This has been discussed a number of times, probably every few years or so.
My quick search failed to find any relevant links/threads in the archives,
though I didn't try that hard.

David J.

Bryn Llewellyn <bryn@yugabyte.com> writes:

Maybe this entire discussion is moot when hackers can read the C code of PG's implementation…

Hmm ... in one way that's unrelated, but in another way perhaps it is.
Postgres' system catalogs have always been user-readable as much as
possible, excepting only cases that clearly might contain private data
such as pg_statistic or pg_user_mapping.umoptions. We have pretty much
no interest in revisiting that design choice, even if doing so wouldn't
likely break a couple decades' worth of client-side software development.
It's not very hard to draw a line connecting that design choice to our
open-source ethos.

Anyway, if you feel a need to prevent user A from seeing the catalog
entries for user B's database objects, the only answer we have is to
put A and B into separate databases. If despite that you want A and
B to be able to share some data, you can probably build the connections
you need using foreign tables or logical replication; but there's not
a lot of pre-fab infrastructure for that, AFAIK.

regards, tom lane

david.g.johnston@gmail.com wrote:

bryn@yugabyte.com wrote:

However, the design decision that, way back when, leads to this outcome does surprise me. The principle of least privilege insists that (in the database regime) you can create users that can do exactly and only what they need to do. This implies that my "client" should not be able to list all the objects in the database (and all the users in the cluster).

If there was any motivation to improve PostgreSQL on this front I'd like them to start with "routine bodies" being hidden away from inspection. I'm much less concerned about pg_class or even knowing the names of things.

This has been discussed a number of times, probably every few years or so. My quick search failed to find any relevant links/threads in the archives, though I didn't try that hard.

Thanks (again) David. Yes, there is an argument that when app developers know that hackers can read every minute detail of their implementation (but, with a sound user/schema/privileges discipline cannot change any of this), it cautions them to be extra scrupulous. SQL injection is maybe a good example. It's probably easier and quicker to scan PL/pgSQL source code looking for obvious patterns (like "any use of dynamic SQL?", "If yes, any concatenation of literals into the to-be-executed statement?", and so on) than it is to send robotically generated values via browser-UI screens in the hope of provoking tell-tale errors.

It certainly helps to know that nothing in how PG works in the space that's relevant here is going to change in my lifetime.

pá 13. 5. 2022 v 5:42 odesílatel Bryn Llewellyn <bryn@yugabyte.com> napsal:

*david.g.johnston@gmail.com <david.g.johnston@gmail.com> wrote:*

*bryn@yugabyte.com <bryn@yugabyte.com> wrote:*

However, the design decision that, way back when, leads to this outcome
does surprise me. The principle of least privilege insists that (in the
database regime) you can create users that can do exactly and only what
they need to do. This implies that my "client" should not be able to list
all the objects in the database (and all the users in the cluster).

If there was any motivation to improve PostgreSQL on this front I'd like
them to start with "routine bodies" being hidden away from inspection. I'm
much less concerned about pg_class or even knowing the names of things.

This has been discussed a number of times, probably every few years or so.
My quick search failed to find any relevant links/threads in the archives,
though I didn't try that hard.

Thanks (again) David. Yes, there is an argument that when app developers
know that hackers can read every minute detail of their implementation
(but, with a sound user/schema/privileges discipline cannot change any of
this), it cautions them to be extra scrupulous. SQL injection is maybe a
good example. It's probably easier and quicker to scan PL/pgSQL source code
looking for obvious patterns (like "any use of dynamic SQL?", "If yes, any
concatenation of literals into the to-be-executed statement?", and so on)
than it is to send robotically generated values via browser-UI screens in
the hope of provoking tell-tale errors.

any developer can run this check before an attacker.

plpgsql_check https://github.com/okbob/plpgsql_check does this check.

Regards

Pavel

tgl@sss.pgh.pa.us wrote:

bryn@yugabyte.com writes:

Maybe this entire discussion is moot when hackers can read the C code of PG's implementation…

We have pretty much no interest in revisiting that design choice, even if doing so wouldn't likely break a couple decades' worth of client-side software development.

Anyway, if you feel a need to prevent user A from seeing the catalog entries for user B's database objects, the only answer we have is to put A and B into separate databases. If despite that you want A and B to be able to share some data, you can probably build the connections you need using foreign tables or logical replication; but there's not a lot of pre-fab infrastructure for that, AFAIK.

Thanks Tom. It certainly helps to know that nothing in how PG works in the space that's relevant here is going to change in my lifetime. (I just wrote exactly the same in reply to David Johnston.)

My sense is that the database is intended to be a hermetic unit of encapsulation and provides some of the features that multi-tenancy requires. But there's the caveat that users are defined, and operate, cluster-wide.

If a cluster has two databases, "app_1" and "app_2", each populated using the general scheme that I sketched, then users "client_1" and "client_2" (designed, respectively to let them operate as intended in their corresponding databases) could always connect each to the other's database. They couldn't do much in the "wrong" database. But they could certainly list out all the application's objects and the source code of all the application's user-defined subprograms.

In general, it's best to use any system in the way that it was designed to be used. And PG was designed to allow all users to see the metadata account of all of the content of every database in the cluster—but not to use any of the content unless this has been specifically catered for.

Hi all,

Thanks for your suggestions, I would like to define my problem a little
more.

I am using pgAdmin . I have a database 'db' and it has got 2 schemas
'schema1' and 'schema2', I have created some views in schema2 from tables
of schema1. I have created a new user and granted connection access to
database and granted usage on tables and views of schema2 only. But now the
problem is that the new user is able to see the table names of schema1 even
though the user cannot see the data present in them they can see the table
names.Is there any way I can completely hide schema1 from the new user.

Thanks & Regards
Neeraj

On Fri, May 13, 2022, 09:40 Bryn Llewellyn <bryn@yugabyte.com> wrote:

On Thursday, May 12, 2022, Neeraj M R <neerajmr12219@gmail.com> wrote:

Thanks for your suggestions, I would like to define my problem a little
more.

I am using pgAdmin

Is there any way I can completely hide schema1 from the new user.

pgAdmin is a separate project. You may get a response here but they do
have their own list as well as documentation. It would be a purely UX
thing though, a user who wants to see the object in schema1 can choose to
do so manually.

David J.

neerajmr12219@gmail.com wrote:

I am using pgAdmin . I have a database 'db' and it has got 2 schemas 'schema1' and 'schema2', I have created some views in schema2 from tables of schema1. I have created a new user and granted connection access to database and granted usage on tables and views of schema2 only. But now the problem is that the new user is able to see the table names of schema1 even though the user cannot see the data present in them they can see the table names. Is there any way I can completely hide schema1 from the new user.

What exactly do you mean by "have created a new user and granted connection access to database"? As I understand it, there's no such thing. I mentioned a simple test in my earlier email that showed that any user (with no schema of its own and no granted privileges) can connect to any database—and see the full metadata account of all its content. I'm teaching myself to live with this.

Hi Bryn,

What I meant by 'created a new user' is that I have used the following
commands.

CREATE USER <user_name> WITH ENCRYPTED PASSWORD '<password>';
GRANT CONNECT ON DATABASE <database> TO <user_name>;
GRANT USAGE ON SCHEMA <schema> TO <user_name>;

Thanks & Regards
Neeraj

On Fri, May 13, 2022, 10:43 Bryn Llewellyn <bryn@yugabyte.com> wrote:

neerajmr12219@gmail.com wrote:

bryn@yugabyte.com wrote:

What exactly do you mean by "have created a new user and granted connection access to database"? As I understand it, there's no such thing. I mentioned a simple test in my earlier email that showed that any user (with no schema of its own and no granted privileges) can connect to any database—and see the full metadata account of all its content. I'm teaching myself to live with this.

What I meant by 'created a new user' is that I have used the following commands.

CREATE USER <user_name> WITH ENCRYPTED PASSWORD '<password>';
GRANT CONNECT ON DATABASE <database> TO <user_name>;
GRANT USAGE ON SCHEMA <schema> TO <user_name>;

Ah… there's obviously something I don't understand here. I've never used "grant connect on database"—and not experience an ensuing problem. I just tried this:

\c postgres postgres
create user joe login password 'joe';
revoke connect on database postgres from joe;
\c postgres joe

It all ran without error. (I've turned off the password challenge in my MacBook PG cluster.) I don't have a mental model that accommodates this. And a quick skim for this variant in the "GRANT" section of the PG doc didn't (immediately) help me. I obviously need to do more study. I'll shut up until I have.

On Thu, May 12, 2022 at 11:37 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:

*neerajmr12219@gmail.com <neerajmr12219@gmail.com> wrote:*

*bryn@yugabyte.com <bryn@yugabyte.com> wrote:*

What exactly do you mean by "have created a new user and granted
connection access to database"? As I understand it, there's no such thing.
I mentioned a simple test in my earlier email that showed that any user
(with no schema of its own and no granted privileges) can connect to any
database—and see the full metadata account of all its content. I'm teaching
myself to live with this.

What I meant by 'created a new user' is that I have used the following
commands.

CREATE USER <user_name> WITH ENCRYPTED PASSWORD '<password>';
GRANT CONNECT ON DATABASE <database> TO <user_name>;
GRANT USAGE ON SCHEMA <schema> TO <user_name>;

In a freshly initialized cluster the newly created user will have already

inherited the necessary connect privilege making this one redundant (though
that property can be considered useful here).

Ah… there's obviously something I don't understand here. I've never used

"grant connect on database"—and not experience an ensuing problem. I just
tried this:

\c postgres postgres
create user joe login password 'joe';
revoke connect on database postgres from joe;
\c postgres joe

It all ran without error. (I've turned off the password challenge in my
MacBook PG cluster.) I don't have a mental model that accommodates this.
And a quick skim for this variant in the "GRANT" section of the PG doc
didn't (immediately) help me. I obviously need to do more study. I'll shut
up until I have.

It's because joe hasn't been granted connect on the database directly. It
is through their mandatory membership in the PUBLIC pseudo-role, and that
role's default grant of connect on all newly created databases, that joe
receives permission to connect. You can only revoke what has been
explicitly granted so one must revoke the grant from PUBLIC - then
re-assign it to the subset of roles that require it.

https://www.postgresql.org/docs/current/ddl-priv.html

David J.

On 5/12/22 22:03, Neeraj M R wrote:

Hi all,

Thanks for your suggestions, I would like to define my problem a little
more.

 I am using pgAdmin . I have a database 'db' and it has got 2 schemas
'schema1' and 'schema2', I have created some views in schema2  from
tables of schema1. I have created a new user and granted connection
access to database and granted usage on tables and views of schema2
only. But now the problem is that the new user is able to see the table
names of schema1 even though the user cannot see the data present in
them they can see the table names.Is there any way I can completely hide
schema1 from the new user.

AFAIK, you can't change that display in pgAdmin4. It would not help in
any case as long as the Query Tool is available as a user can get the
information the same way pgAdmin4 does, by querying the system catalogs.

Thanks & Regards
Neeraj

--
Adrian Klaver
adrian.klaver@aklaver.com

On 5/12/22 22:13, Bryn Llewellyn wrote:

/neerajmr12219@gmail.com <mailto:neerajmr12219@gmail.com> wrote:/

What exactly do you mean by "have created a new user and granted
connection access to database"? As I understand it, there's no such
thing. I mentioned a simple test in my earlier email that showed that
any user (with no schema of its own and no granted privileges) can
connect to any database—and see the full metadata account of all its
content. I'm teaching myself to live with this.

Besides the REVOKE CONNECT, it is also possible to prevent connections
to a given database by a particular user by using settings in pg_hba.conf.

--
Adrian Klaver
adrian.klaver@aklaver.com

adrian.klaver@aklaver.com wrote:

bryn@yugabyte.com wrote:

neerajmr12219@gmail.com wrote:

...

What exactly do you mean by "have created a new user and granted connection access to database"? As I understand it, there's no such thing. I mentioned a simple test in my earlier email that showed that any user (with no schema of its own and no granted privileges) can connect to any database—and see the full metadata account of all its content. I'm teaching myself to live with this.

Besides the REVOKE CONNECT, it is also possible to prevent connections to a given database by a particular user by using settings in pg_hba.conf.

Adrian, I have the "pg_hba.conf" unmodified that came with the "brew" PG installation of PG Version 14.2 on my MacOS Big Sur laptop. It has just six non-comment lines, thus:

# TYPE DATABASE USER ADDRESS METHOD
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust

This lines up with what "select * from pg_hba_file_rules" gets, thus:

line_number | type | database | user_name | address | netmask | auth_method | options | error
-------------+-------+---------------+-----------+-----------+-----------------------------------------+-------------+---------+-------
89 | local | {all} | {all} | | | trust | |
91 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | trust | |
93 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | |
96 | local | {replication} | {all} | | | trust | |
97 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | trust | |
98 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | |

I read the Current "21.1. The pg_hba.conf File" section and noted this tip:

«
To connect to a particular database, a user must not only pass the pg_hba.conf checks, but must have the CONNECT privilege for the database. If you wish to restrict which users can connect to which databases, it's usually easier to control this by granting/revoking CONNECT privilege than to put the rules in pg_hba.conf entries.
»

I'd like to do what this tip says. But the regime that I have allows any non-super user to connect to any database.

I just re-tested this with a brand-new user "joe"—and after doing "revoke connect on database postgres from joe".

I'm obviously missing critical "pg_hba.conf" line(s). But I can't see what to add from the section that I mentioned. There must be some keyword, like "none", meaning the opposite of "all" for users.

I tried this. (I don't have a database called "replication" so I removed those lines.)

local postgres postgres trust
host postgres postgres 127.0.0.1/32 trust
host postgres postgres ::1/128 trust

But that idea didn't work because, with my newly created user "joe", my "\c postgres joe" failed with a complaint that my "pg_hba.conf" had no entry for « user "joe", database "postgres" ».

I discovered (by "drop user") that « role name "none" is reserved ». So I added these lines:

local postgres none trust
host postgres none 127.0.0.1/32 trust
host postgres none ::1/128 trust

But even after "grant connect on database postgres to joe", my "\c postgres joe" still failed just as I described above. For sport, I tried this instead:

local postgres joe trust
host postgres joe 127.0.0.1/32 trust
host postgres joe ::1/128 trust

But this goes against what the tip says. Anyway, after "revoke connect on database postgres from joe", my "\c postgres joe" succeeded.

I tried Googling. But all the hits that I found were about controlling which remote hosts can connect at all and what password security is to be used.

What must I do? And where is this described in the PG doc?