Postgres limitation in user management

[Kar, Swapnil (TR Technology)]

Hello Team,

I am facing a limitation with Postgres user management and require your 
assistance or input around it. Let me brief you the scenario here -

We have 2 sets of database user groups -


  1.  App - who owns the application schemas (and tables)
  2.  Support - who provides db support

We want Support users to have no SELECT or DML privilege but only ALTER TABLE 
to perform any troubleshooting in the database.

In Postgres, to have alter system privilege one should be the owner of the 
schema/table but App users are not keen to make them temporarily as owner of 
the schema during the investigation time. Because they loose the ownership and 
can't perform ALTER table commands.

Now another option 2 is to - grant app_user to support_user;
This way ownership is not transferred but support is able to perform select and 
DML.

Option 3 is to grant rds_superuser privilege to support and in this case they 
will become more powerful superuser in the DB. This is also not a solution for 
our requirement.

Do you think there is a way to deal with this situation ?

Any help and guidance here is highly appreciated.

Regards,
Swapnil
This e-mail is for the sole use of the intended recipient and contains 
information that may be privileged and/or confidential. If you are not an 
intended recipient, please notify the sender by return e-mail and delete this 
e-mail and any attachments. Certain required legal entity disclosures can be 
accessed on our website: 
https://www.thomsonreuters.com/en/resources/disclosures.html

Re: Postgres limitation in user management

[Ron]

How can you /practically/ support a database without being able to look at a 
table?


On 11/3/23 01:26, Kar, Swapnil (TR Technology) wrote:


Hello Team,

I am facing a limitation with Postgres user management and require your 
assistance or input around it. Let me brief you the scenario here –


We have 2 sets of database user groups –

 1. *App*– who owns the application schemas (and tables)
 2. *Support*– who provides db support

We want Support users to have no SELECT or DML privilege but only ALTER 
TABLE to perform any troubleshooting in the database.


In Postgres, to have alter system privilege one should be the owner of the 
schema/table but App users are not keen to make them temporarily as owner 
of the schema during the investigation time. Because they loose the 
ownership and can’t perform ALTER table commands.


Now another option 2 is to – grant app_user to support_user;

This way ownership is not transferred but support is able to perform 
select and DML.


Option 3 is to grant rds_superuser privilege to support and in this case 
they will become more powerful superuser in the DB. This is also not a 
solution for our requirement.


Do you think there is a way to deal with this situation ?

Any help and guidance here is highly appreciated.

Regards,

Swapnil

This e-mail is for the sole use of the intended recipient and contains 
information that may be privileged and/or confidential. If you are not an 
intended recipient, please notify the sender by return e-mail and delete 
this e-mail and any attachments. Certain required legal entity disclosures 
can be accessed on our website: 
https://www.thomsonreuters.com/en/resources/disclosures.html 


--
Born in Arizona, moved to Babylonia.

Re: Postgres limitation in user management

[Peter J. Holzer]

On 2023-11-03 06:26:21 +, Kar, Swapnil (TR Technology) wrote:
> We have 2 sets of database user groups –
> 
>  1. App – who owns the application schemas (and tables)
>  2. Support – who provides db support
> 
> We want Support users to have no SELECT or DML privilege but only ALTER TABLE
> to perform any troubleshooting in the database.

This seems strange to me. What kind of troubleshooting requires to
ability to ALTER TABLE but not to do DML?

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Postgres limitation in user management

[Brent Wood]

>> We have 2 sets of database user groups –
>>
>>  1. App – who owns the application schemas (and tables)
>>  2. Support – who provides db support
>>
>> We want Support users to have no SELECT or DML privilege but only ALTER TABLE
>> to perform any troubleshooting in the database.

>This seems strange to me. What kind of troubleshooting requires to
>ability to ALTER TABLE but not to do DML?

Where your db admin & data admin are separated. Data security issues can 
require minimal access to data, which a dba does not necessarily require. 
Especially when the DBA role is contracted out.

Sort of along this line, we have offloaded user management to AD, so our DB 
user management is now carried out via in-house IT, who are not DBA's and have 
no access to data.

Brent Wood

Principal Technician, Fisheries
NIWA
DDI:  +64 (4) 3860529


From: Peter J. Holzer
Sent: Sunday, November 05, 2023 10:33
To: pgsql-general@lists.postgresql.org
Subject: Re: Postgres limitation in user management

On 2023-11-03 06:26:21 +, Kar, Swapnil (TR Technology) wrote:
> We have 2 sets of database user groups –
>
>  1. App – who owns the application schemas (and tables)
>  2. Support – who provides db support
>
> We want Support users to have no SELECT or DML privilege but only ALTER TABLE
> to perform any troubleshooting in the database.

This seems strange to me. What kind of troubleshooting requires to
ability to ALTER TABLE but not to do DML?

hp

--
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"
[https://www.niwa.co.nz/static/niwa-2018-horizontal-180.png] 

Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529

National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade Hataitai Wellington New Zealand
Connect with NIWA: niwa.co.nz 
Facebook 
LinkedIn 
Twitter 
Instagram 
YouTube
To ensure compliance with legal requirements and to maintain cyber security 
standards, NIWA's IT systems are subject to ongoing monitoring, activity 
logging and auditing. This monitoring and auditing service may be provided by 
third parties. Such third parties can access information transmitted to, 
processed by and stored on NIWA's IT systems.
Note: This email is intended solely for the use of the addressee and may 
contain information that is confidential or subject to legal professional 
privilege. If you receive this email in error please immediately notify the 
sender and delete the email.

Re: Postgres limitation in user management

[Peter J. Holzer]

On 2023-11-04 21:42:34 +, Brent Wood wrote:
> >> We have 2 sets of database user groups –
> >>
> >>  1. App – who owns the application schemas (and tables)
> >>  2. Support – who provides db support
> >>
> >> We want Support users to have no SELECT or DML privilege but only ALTER
> TABLE
> >> to perform any troubleshooting in the database.
> 
> >This seems strange to me. What kind of troubleshooting requires to
> >ability to ALTER TABLE but not to do DML?
> 
> Where your db admin & data admin are separated. Data security issues can
> require minimal access to data, which a dba does not necessarily require.
> Especially when the DBA role is contracted out. 
> 
> Sort of along this line, we have offloaded user management to AD, so our DB
> user management is now carried out via in-house IT, who are not DBA's and have
> no access to data.

This doesn't answer the question why ALTER TABLE privilege would be
required.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Postgres limitation in user management

[Christophe Pettus]

> On Nov 2, 2023, at 23:26, Kar, Swapnil (TR Technology) 
>  wrote:
> We want Support users to have no SELECT or DML privilege but only ALTER TABLE 
> to perform any troubleshooting in the database.

If a user has no ability to do SELECT or DML, they won't be able to 
"troubleshoot" the database.  They may be able to apply migrations, but if 
essentially any problem arises in the database, at least SELECT will be 
required.

Re: Postgres limitation in user management

[Ron]

On 11/4/23 16:53, Peter J. Holzer wrote:

On 2023-11-04 21:42:34 +, Brent Wood wrote:

We have 2 sets of database user groups –

  1. App – who owns the application schemas (and tables)
  2. Support – who provides db support

We want Support users to have no SELECT or DML privilege but only ALTER

TABLE

to perform any troubleshooting in the database.

This seems strange to me. What kind of troubleshooting requires to
ability to ALTER TABLE but not to do DML?

Where your db admin & data admin are separated. Data security issues can
require minimal access to data, which a dba does not necessarily require.
Especially when the DBA role is contracted out.

Sort of along this line, we have offloaded user management to AD, so our DB
user management is now carried out via in-house IT, who are not DBA's and have
no access to data.

This doesn't answer the question why ALTER TABLE privilege would be
required.


I bet the Good Idea Fairy whispered something into the CISO's ear.

--
Born in Arizona, moved to Babylonia.

Re: Postgres limitation in user management

[Tom Lane]

Ron  writes:
> On 11/4/23 16:53, Peter J. Holzer wrote:
>> This doesn't answer the question why ALTER TABLE privilege would be
>> required.

> I bet the Good Idea Fairy whispered something into the CISO's ear.

Yeah.  This is blatantly obviously the brainchild of some person
with no actual experience in fulfilling the roles they want to
circumscribe.

Having said that, maybe:

* Role foo_owner actually owns the tables, but revokes its own
DML privileges (select etc)

* Role foo_app is granted foo_owner so it can do DDL on the
tables, and is also granted DML privileges on the tables

* Role foo_dba is granted foo_owner but not DML privileges.

This is, of course, trivially breakable by any foo_dba who
doesn't want to play by the rules, but as long as you log
DDL there will at least be log traces that she did so.

regards, tom lane