Message-ID: <4d672de040dd54b1dd687d20da2eb291f3324fd7.camel@cybertec.at>
Subject: Re: Permissions for Newly Created User
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: sreekanta reddy <sreekantareddy18@gmail.com>, 
	pgsql-general@lists.postgresql.org
Date: Fri, 18 Oct 2024 16:45:32 +0200
In-Reply-To: <CAK5dmvD-63hFPBZ+GnuZEO9Knabpw1wnrWnrHuADjWPFF-Pgvw@mail.gmail.com>
References: 
	<CAK5dmvD-63hFPBZ+GnuZEO9Knabpw1wnrWnrHuADjWPFF-Pgvw@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-1.fc40) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/4d672de040dd54b1dd687d20da2eb291f3324fd7.camel%40cybertec.at>
Precedence: bulk

On Fri, 2024-10-18 at 15:41 +0530, sreekanta reddy wrote:
> I would also like to suggest an enhancement to the default behavior for n=
ewly created users in PostgreSQL.
>=20
> Observed Issue:
> User Created: testdb
> Command used: CREATE USER testdb WITH PASSWORD 'dhsfjobodjjbsdj';
> After creating the user testdb, I observed that the user could still view=
 objects, schemas, and their
> structures, as well as system tables and views, which contradicts the int=
ended restricted permissions.

People have complained about that before, but that's working as designed: m=
ost metadata are visible
to everybody.  Perhaps we should add that as "feature we don't want" to the=
 TODO list.

The standard suggestion is to use different databases if users shouldn't se=
e each other's
objects' metadata.

> Suggested Privileges for Newly Created Normal Users:
> I would like to suggest enhancements to the default behavior for newly cr=
eated normal users in PostgreSQL to improve data security:
>=20
> Database Connection:=C2=A0The user should have the ability to connect onl=
y to postgres =C2=A0databases by default

I am not fundamentally against that, but it would be a painful compatibilit=
y break, and the
gain is small.  After all, the default "pg_hba.conf" file forbids remote co=
nnections, and
you can get the same effect with the right entries in "pg_hba.conf".

> Read-Only Configurations:=C2=A0The user should have read-only access to v=
iew database configuration parameters.

Why?  The ability to change certain parameters on the fly in your session i=
s a feature.

> I would also like to highlight a security concern regarding password hand=
ling:
>=20
> When creating or altering a user's password, the log file captures the pa=
ssword in plain text format, which could be a potential security risk.
> However, when using the \password command in psql, the password is logged=
 in its hashed format (SHA-256), which is a more secure practice.
> I recommend extending this hashed logging format to all password creation=
 and modification operations.

You mean to hash it just for logging?

After you sent it to the server in clear text, so that the DBA could captur=
e it with an
event trigger?  Where is the point?  The log file is to be treated as sensi=
tive data.

Yours,
Laurenz Albe