Thread: Restricting user to see schema structure
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.
Thanks & Regards
Neeraj
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 thedata 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 retrofitwithout a re-write. Design (and document the practice) to encapsulate the database functionality (i.e. the business functions that the clientside app must perform) behind an API exposed as user-defined functions that return query results for SELECT operationsand outcome statuses (e.g. "success", "This nickname is taken. Try a different one", "Unexpected error. Reportincident 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 arethin jackets to invoke the real work-doing functions that are hidden from the client. Allow clients to authorize ONLYas 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 listthe API functions and whatever \df and \sf show. (notice that \d will reveal nothing.)But doing this reveals only thenames of the functions that are called (which will be identical to the jacket names—so no risk here) and the name(s) ofthe 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 modularsoftware construction (where the entire database is a top-level module in the over all app's decomposition). It bringsthe security benefit that I sketched along with all the other famous benefits of modular programming—esp. e.g. theclient 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 listthe API functions and whatever \df and \sf show. (notice that \d will reveal nothing.)But doing this reveals only thenames of the functions that are called (which will be identical to the jacket names—so no risk here) and the name(s) ofthe 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
-------+-------------+-------------+-------------
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'
));
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$ +
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 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.
any developer can run this check before an attacker.
plpgsql_check https://github.com/okbob/plpgsql_check does this check.
Regards
Pavel
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.
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:
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.
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 pgAdminIs 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:
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.
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
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 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>;
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 joeIt 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.
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
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 | |
-------------+-------+---------------+-----------+-----------+-----------------------------------------+-------------+---------+-------
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
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
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
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?
On 5/16/22 2:04 PM, Bryn Llewellyn wrote: >> /adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> wrote:/ >> >>> /bryn@yugabyte.com <mailto:bryn@yugabyte.com> 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, 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: > > 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" ». So it worked you could not connect. > > 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 none is not going to work per: https://www.postgresql.org/docs/current/auth-pg-hba-conf.html "user Specifies which database user name(s) this record matches. The value all specifies that it matches all users. Otherwise, this is either the name of a specific database user, or a group name preceded by +. (Recall that there is no real distinction between users and groups in PostgreSQL; a + mark really means “match any of the roles that are directly or indirectly members of this role”, while a name without a + mark matches only that specific role.) For this purpose, a superuser is only considered to be a member of a role if they are explicitly a member of the role, directly or indirectly, and not just by virtue of being a superuser. Multiple user names can be supplied by separating them with commas. A separate file containing user names can be specified by preceding the file name with @. " none is not listed as a special name. > But this goes against what the tip says. Anyway, after "revoke connect > on database postgres from joe", my "\c postgres joe" succeeded. Because as mentioned previously you did not "revoke connect on database postgres from public". -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, May 16, 2022 at 2:04 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
«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.
You are failing to grasp the concept of "additive permissions" (ignoring auth-method reject for now). The idea of a literal "none" makes no sense - the absence of something is nothing, you do not say "none" explicitly.
But this goes against what the tip says. Anyway, after "revoke connect on database postgres from joe", my "\c postgres joe" succeeded.
See my answer, with link, from Friday. Joe's ability to connect is inherited through PUBLIC. There is no privilege directly on Joe to revoke.
I don't quite know how to address your random experimentation with pg_hba.conf. None of the things you showed are surprising though - were you expecting different?
David J.
adrian.klaver@aklaver.com wrote:bryn@yugabyte.com wrote:adrian.klaver@aklaver.com wrote:bryn@yugabyte.com <mailto:bryn@yugabyte.com> wrote:
What exactly do you mean by "have created a new user and granted connection access to database"?
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... [But thing don't work as I want.]Because as mentioned previously you did not "revoke connect on database postgres from public".
Right, I see the importance of this now. I now realize that when a database is created, CONNECT on it is automatically granted to PUBLIC. But there's no mention of this (or what to read to learn that this is the case) in the "pg_hba.conf" chapter. Nor does the section on the CREATE DATABASE statement mention this. How is the neophyte supposed to know about this behavior?
Another thing that confused me was the significance of the lines for the database "replication" in the "pg_hba.conf" that came with my installation. Add to this the mutually exclusive keywords "REPLICATION" and "NO REPLICATION" in the CREATE ROLE statement. ("These clauses determine whether a role is a replication role.") So this seems to be a distinct use of the word from how it's used in "pg_hba.conf" as the name of a database (that might well not exist). Strangely, the CREATE ROLE doc says that you don't need either of "REPLICATION" or "NO REPLICATION" but it doesn't say what the default is.
David Johnston wrote this in a separate thread:
I don't quite know how to address your random experimentation with pg_hba.conf. None of the things you showed are surprising though - were you expecting different?
My reports of my random experimentation were the email equivalent of the "think aloud" approach to usability testing. That paradigm has the creators of a system observe a new user trying to get things done (using any appropriate doc that's available). Sometimes, the user appears to be trying things randomly. Then the creators ask "why did you do that"—and they learn what faulty mental model the user has formed. And then they try to find out how the new user came to acquire that model. Often, the problem is that the doc (or the UI, when it's meant to me self-evident) suffers from what Steven Pinker calls the "curse of knowledge" in his book "The sense of style".
Anyway, with my experimentation and with the clues that you two (Adrian and David) have given me, I arrived that the following practice. It seems to give me what I want—i.e. a regime where ordinary new users that I create can operate without me needing to change the "pg_hba.conf" file and where they can connect to the one-and-only database that I intend and then perform exactly and only the tasks that I intend—in other words a regime that honors the principle of least privilege. (We've discussed the caveat that I can't prevent them from reading all of the metadata across all databases earlier.)
* I use this bare bones "pg_hba.conf" file.
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
* I say "\c postgres postgres" and use a script to strip the cluster done to its bare minimum—in my case: the users "Bllewell" and postgres; and the databases postgres, template0, and template1.
I say "revoke connect on database postgres from public" and "drop schema if exists public". (And I drop any other schemas that might have been created in the database postgres).
* When I create a database, I immediately drop its public schema and revoke connect on it from public.
* When I create a user, I say NOREPLICATION and grant it CONNECT on just the one database (it's always one) that I intend. However, when I create a superuser, I cannot prevent it from connecting to *any* database (present or future).
On Mon, May 16, 2022 at 10:21 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
Right, I see the importance of this now. I now realize that when a database is created, CONNECT on it is automatically granted to PUBLIC. But there's no mention of this (or what to read to learn that this is the case) in the "pg_hba.conf" chapter. Nor does the section on the CREATE DATABASE statement mention this. How is the neophyte supposed to know about this behavior?
By reading the documentation, specifically the chapter linked before, where this is discussed.
Not saying there isn't room for improvement here, I tend to agree that the SQL Command Reference section should be considered a primary jumping off point. But the chapter on Data Definition is basically mandatory reading once a DBA wants to do any non-trivial modifications to their cluster.
David J.
On 5/16/22 22:21, Bryn Llewellyn wrote:
>> Because as mentioned previously you did not "revoke connect on
>> database postgres from public".
>
> Right, I see the importance of this now. I now realize that when a
> database is created, CONNECT on it is automatically granted to PUBLIC.
> But there's no mention of this (or what to read to learn that this is
> the case) in the "pg_hba.conf" chapter. Nor does the section on the
> CREATE DATABASE statement mention this. How is the neophyte supposed to
> know about this behavior?
CREATE DATABASE is an object creation command and what you are concerned
with is not that but the privileges associated with the object. As David
pointed out that is spelled out in the section on privileges. To me it
makes sense to aggregate privilege information rather then have to walk
through all the object creation sections.
>
> Another thing that confused me was the significance of the lines for the
> database "replication" in the "pg_hba.conf" that came with my
> installation. Add to this the mutually exclusive keywords "REPLICATION"
> and "NO REPLICATION" in the CREATE ROLE statement. ("These clauses
> determine whether a role is a replication role.") So this seems to be a
> distinct use of the word from how it's used in "pg_hba.conf" as the name
> of a database (that might well not exist). Strangely, the CREATE ROLE
> doc says that you don't need either of "REPLICATION" or "NO REPLICATION"
> but it doesn't say what the default is.
From here:
https://www.postgresql.org/docs/current/auth-pg-hba-conf.html
"The value replication specifies that the record matches if a physical
replication connection is requested, however, it doesn't match with
logical replication connections. Note that physical replication
connections do not specify any particular database whereas logical
replication connections do specify it."
Makes sense to me, it is a 'dummy' value for binary replication
connections as they do not actually connect to a database but to a cluster.
As to CREATE ROLE:
https://www.postgresql.org/docs/current/sql-createrole.html
REPLICATION
NOREPLICATION
" These clauses determine whether a role is a replication role. A
role must have this attribute (or be a superuser) in order to be able to
connect to the server in replication mode (physical or logical
replication) and in order to be able to create or drop replication
slots. A role having the REPLICATION attribute is a very highly
privileged role, and should only be used on roles actually used for
replication. If not specified, NOREPLICATION is the default. You must be
a superuser to create a new role having the REPLICATION attribute.
"
So the default of NOREPLICATION is mentioned.
replication(pg_hba.conf 'dummy' value) and REPLICATION/NOREPLICATION
roles are referring to different aspects of the same process. Honestly
I'm not seeing how this is any different from database postgres and role
postgres.
--
Adrian Klaver
adrian.klaver@aklaver.com
david.g.johnston@gmail.com wrote:bryn@yugabyte.com wrote:
Right, I see the importance of this now. I now realize that when a database is created, CONNECT on it is automatically granted to PUBLIC. But there's no mention of this (or what to read to learn that this is the case) in the "pg_hba.conf" chapter. Nor does the section on the CREATE DATABASE statement mention this. How is the neophyte supposed to know about this behavior?
By reading the documentation, specifically the chapter linked before, where this is discussed.
https://www.postgresql.org/docs/current/ddl-priv.html
Not saying there isn't room for improvement here, I tend to agree that the SQL Command Reference section should be considered a primary jumping off point. But the chapter on Data Definition is basically mandatory reading once a DBA wants to do any non-trivial modifications to their cluster.
Thanks again, David. Yes... the section "5.7. Privileges" is essential reading. And I do see what I'd missed now. I broke it into bullets to make it (very much) easier for me at least to parse.
«
PostgreSQL grants privileges on some types of objects to PUBLIC by default when the objects are created.
* No privileges are granted to PUBLIC by default on tables, table columns, sequences, foreign data wrappers, foreign servers, large objects, schemas, or tablespaces.
For other types of objects, the default privileges granted to PUBLIC are as follows:
* CONNECT and TEMPORARY (create temporary tables) privileges for databases;
* EXECUTE privilege for functions and procedures;
* USAGE privilege for languages and data types (including domains).
The object owner can, of course, REVOKE both default and expressly granted privileges. (For maximum security, issue the REVOKE in the same transaction that creates the object; then there is no window in which another user can use the object.) Also, these default privilege settings can be overridden using the ALTER DEFAULT PRIVILEGES command.
»
(I'd already been burned by the fact that EXECUTE is granted to PUBLIC on newly-created functions and procedures.)
The paragraph describes very surprising behavior in the present era of "secure by default". The sentence "For maximum security..." at the end emphasizes this and has you go to some effort (CREATE and REVOKE in the same txn) to undo the "insecurity by default" paradigm. I s'pose that compatibility on upgrade means that nothing can change here.
Might it be possible to give the paragraph more prominence (like make it a note and start it with "WARNING" in large letters). And to x-ref it from the CREATE DATABASE section? And from the "pg_hba.conf" section? And correspondingly from the CREATE accounts for the objects of the other types?
On Tuesday, May 17, 2022, Bryn Llewellyn <bryn@yugabyte.com> wrote:
Might it be possible to give the paragraph more prominence (like make it a note and start it with "WARNING" in large letters). And to x-ref it from the CREATE DATABASE section? And from the "pg_hba.conf" section? And correspondingly from the CREATE accounts for the objects of the other types?
While I agree with the general premise that there is room for improvement here, the degree of problem and manner of solution presented here doesn’t seem like a specific solution I would endorse.
David J.
On Tuesday, May 17, 2022, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tuesday, May 17, 2022, Bryn Llewellyn <bryn@yugabyte.com> wrote:Might it be possible to give the paragraph more prominence (like make it a note and start it with "WARNING" in large letters). And to x-ref it from the CREATE DATABASE section? And from the "pg_hba.conf" section? And correspondingly from the CREATE accounts for the objects of the other types?While I agree with the general premise that there is room for improvement here, the degree of problem and manner of solution presented here doesn’t seem like a specific solution I would endorse.
It is also only barely related to thread topic.
David J.
On 5/17/22 13:21, Bryn Llewellyn wrote: > > The paragraph describes very surprising behavior in the present era of > "secure by default". The sentence "For maximum security..." at the end > emphasizes this and has you go to some effort (CREATE and REVOKE in the > same txn) to undo the "insecurity by default" paradigm. I s'pose that > compatibility on upgrade means that nothing can change here. There is movement on this front coming in Postgres 15: https://www.postgresql.org/docs/devel/release-15.html#id-1.11.6.5.3 -- Adrian Klaver adrian.klaver@aklaver.com
adrian.klaver@aklaver.com wrote:bryn@yugabyte.com wrote:The paragraph describes very surprising behavior in the present era of "secure by default". The sentence "For maximum security..." at the end emphasizes this and has you go to some effort (CREATE and REVOKE in the same txn) to undo the "insecurity by default" paradigm. I s'pose that compatibility on upgrade means that nothing can change here.
There is movement on this front coming in Postgres 15:
https://www.postgresql.org/docs/devel/release-15.html
Do you mean that, for example, "create database x" will no longer imply "grant connect on database x to public" and "create function f()" will no longer imply "grant execute on function f() to public"? That would be good. But I can't find wording to that effect on the page.
On Tue, May 17, 2022 at 6:47 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
adrian.klaver@aklaver.com wrote:bryn@yugabyte.com wrote:The paragraph describes very surprising behavior in the present era of "secure by default". The sentence "For maximum security..." at the end emphasizes this and has you go to some effort (CREATE and REVOKE in the same txn) to undo the "insecurity by default" paradigm. I s'pose that compatibility on upgrade means that nothing can change here.
There is movement on this front coming in Postgres 15:
https://www.postgresql.org/docs/devel/release-15.html
Do you mean that, for example, "create database x" will no longer imply "grant connect on database x to public" and "create function f()" will no longer imply "grant execute on function f() to public"? That would be good. But I can't find wording to that effect on the page.
No, the changes are to the defaults for the public schema - which makes actually removing it from the database post-creation less necessary.
David J.