Thread: Permissions for Newly Created User
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
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 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. People have complained about that before, but that's working as designed: most 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 see each other's objects' metadata. > 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 datasecurity: > > Database Connection: The user should have the ability to connect only to postgres databases by default I am not fundamentally against that, but it would be a painful compatibility break, and the gain is small. After all, the default "pg_hba.conf" file forbids remote connections, and you can get the same effect with the right entries in "pg_hba.conf". > Read-Only Configurations: The user should have read-only access to view database configuration parameters. Why? The ability to change certain parameters on the fly in your session is a feature. > 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 apotential security risk. > However, when using the \password command in psql, the password is logged in its hashed format (SHA-256), which is a moresecure 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 capture it with an event trigger? Where is the point? The log file is to be treated as sensitive data. Yours, Laurenz Albe
On 10/18/24 03:11, sreekanta reddy wrote: > > 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. What restrictions? The user has what is specified here: https://www.postgresql.org/docs/current/ddl-priv.html Pay particular attention to what is granted to the PUBLIC role. If you want the role to have less privilges that what the defaults are then you will need to explicitly revoke them. -- Adrian Klaver adrian.klaver@aklaver.com
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Fri, 2024-10-18 at 15:41 +0530, sreekanta reddy wrote:
>> When creating or altering a user's password, the log file captures the password in plain text format, which could be
apotential security risk.
>> However, when using the \password command in psql, the password is logged in its hashed format (SHA-256), which is a
moresecure 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 capture it with an
> event trigger? Where is the point? The log file is to be treated as sensitive data.
Yeah. To enlarge on that: if you are capturing SQL commands in the
log file, they most likely contain all kinds of sensitive data ---
think credit card numbers, bank account numbers, HIPAA-protected
medical details, yadda yadda. Most of that, the database has no idea
whether it's sensitive, so "please hide sensitive data in the log" is a
non-starter. You have to guard the postmaster log about as carefully
as you guard the database contents.
regards, tom lane