Обсуждение: Postgres limitation in user management

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

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

@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; font-size:11.0pt; font-family:"Calibri",sans-serif; mso-ligatures:standardcontextual; mso-fareast-language:EN-US;}p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph {mso-style-priority:34; margin-top:0cm; margin-right:0cm; margin-bottom:0cm; margin-left:36.0pt; font-size:11.0pt; font-family:"Calibri",sans-serif; mso-ligatures:standardcontextual; mso-fareast-language:EN-US;}span.EmailStyle17 {mso-style-type:personal-compose; font-family:"Calibri",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif; mso-fareast-language:EN-US;}div.WordSection1 {page:WordSection1;}ol {margin-bottom:0cm;}ul {margin-bottom:0cm;}

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

On 2023-11-03 06:26:21 +0000, 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.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

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.

On 2023-11-04 21:42:34 +0000, 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.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

> On Nov 2, 2023, at 23:26, Kar, Swapnil (TR Technology) <Swapnil.Kar@thomsonreuters.com> 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
applymigrations, but if essentially any problem arises in the database, at least SELECT will be required. 

On 11/4/23 16:53, Peter J. Holzer wrote:
> On 2023-11-04 21:42:34 +0000, 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.

Ron <ronljohnsonjr@gmail.com> 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