MIME-Version: 1.0
From: sreekanta reddy <sreekantareddy18@gmail.com>
Date: Fri, 18 Oct 2024 15:41:04 +0530
Message-ID: <CAK5dmvD-63hFPBZ+GnuZEO9Knabpw1wnrWnrHuADjWPFF-Pgvw@mail.gmail.com>
Subject: Permissions for Newly Created User
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000066b28a0624bd8628"
Archived-At: <https://www.postgresql.org/message-id/CAK5dmvD-63hFPBZ%2BGnuZEO9Knabpw1wnrWnrHuADjWPFF-Pgvw%40mail.gmail.com>
Precedence: bulk

--00000000000066b28a0624bd8628
Content-Type: text/plain; charset="UTF-8"

Dear PostgreSQL Support Team,

I would also like to suggest an enhancement to the default behavior for
newly created users in PostgreSQL.


*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 intended restricted permissions. Specifically:
The user was able to connect to the database and see all schemas, including
those they should not have visibility into.
Even when permissions were revoked for specific schemas, the user could
still list available tables and view their structures.


*Suggested Privileges for Newly Created Normal Users:*I would like to
suggest enhancements to the default behavior for newly created normal users
in PostgreSQL to improve data security:

*Database Connection:* The user should have the ability to connect only to
postgres  databases by default

*Schema and Table Access:* If the public schema contains 100 tables, the
newly created user should not be able to list or view the structure of any
table unless at least one specific privilege has been granted on those
tables.
Ideally, the system should provide a hint like "user has insufficient
privilege to view schema or table details" when access is restricted.
Restricted Visibility: The user should not have access to list schemas,
tables, or any non-system-related objects unless explicitly authorized.

*Read-Only Configurations:* The user should have read-only access to view
database configuration parameters.
*Privileges:* Additional by default privileges provided if necessary


*Additional Suggestion:*I would also like to highlight a security concern
regarding password handling:

When creating or altering a user's password, the log file captures the
password 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.
These suggestions aim to strengthen PostgreSQL's security by minimizing
unnecessary access to sensitive data and improving password handling.

Thank you in advance for considering these.

Best regards,
Sreekanta Reddy

--00000000000066b28a0624bd8628
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><br>Dear PostgreSQL Support Team,<br><br>I would also like=
 to suggest an enhancement to the default behavior for newly created users =
in PostgreSQL.<br><br><b>Observed Issue:<br></b>User Created: testdb<br>Com=
mand used: CREATE USER testdb WITH PASSWORD &#39;dhsfjobodjjbsdj&#39;;<br>A=
fter creating the user testdb, I observed that the user could still view ob=
jects, schemas, and their structures, as well as system tables and views, w=
hich contradicts the intended restricted permissions. Specifically:<br>The =
user was able to connect to the database and see all schemas, including tho=
se they should not have visibility into.<br>Even when permissions were revo=
ked for specific schemas, the user could still list available tables and vi=
ew their structures.<br><br><b>Suggested Privileges for Newly Created Norma=
l Users:<br></b>I would like to suggest enhancements to the default behavio=
r for newly created normal users in PostgreSQL to improve data security:<br=
><br><b>Database Connection:</b>=C2=A0The user should have the ability to c=
onnect only to postgres =C2=A0databases by default<br><br><b>Schema and Tab=
le Access:</b>=C2=A0If the public schema contains 100 tables, the newly cre=
ated user should not be able to list or view the structure of any table unl=
ess at least one specific privilege has been granted on those tables.<br>Id=
eally, the system should provide a hint like &quot;user has insufficient pr=
ivilege to view schema or table details&quot; when access is restricted.<br=
>Restricted Visibility: The user should not have access to list schemas, ta=
bles, or any non-system-related objects unless explicitly authorized.<br><b=
r><b>Read-Only Configurations:</b>=C2=A0The user should have read-only acce=
ss to view database configuration parameters.<br><b>Privileges:</b>=C2=A0Ad=
ditional by default privileges provided if necessary<br><br><b>Additional S=
uggestion:<br></b>I would also like to highlight a security concern regardi=
ng password handling:<br><br>When creating or altering a user&#39;s passwor=
d, the log file captures the password in plain text format, which could be =
a potential security risk.<br>However, when using the \password command in =
psql, the password is logged in its hashed format (SHA-256), which is a mor=
e secure practice. I recommend extending this hashed logging format to all =
password creation and modification operations.<br>These suggestions aim to =
strengthen PostgreSQL&#39;s security by minimizing unnecessary access to se=
nsitive data and improving password handling.<br><br>Thank you in advance f=
or considering these.<br><br>Best regards,<br>Sreekanta Reddy<br></div>

--00000000000066b28a0624bd8628--