Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pRiS2-0008Dv-G3 for pgsql-general@arkaria.postgresql.org; Mon, 13 Feb 2023 23:41:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pRiS1-0005l0-CD for pgsql-general@arkaria.postgresql.org; Mon, 13 Feb 2023 23:41:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pRiS0-0005kq-Rx for pgsql-general@lists.postgresql.org; Mon, 13 Feb 2023 23:41:37 +0000 Received: from mail-pj1-x1035.google.com ([2607:f8b0:4864:20::1035]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pRiRu-0007V2-C1 for pgsql-general@lists.postgresql.org; Mon, 13 Feb 2023 23:41:35 +0000 Received: by mail-pj1-x1035.google.com with SMTP id oa11-20020a17090b1bcb00b002341a2656e5so2294843pjb.1 for ; Mon, 13 Feb 2023 15:41:30 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=5BBuopGGv4pzFmwlMm3ds0558bCRBiB3rM8TFWkwyqE=; b=cAm3dtVHzs8vNFjqnuGYA+hTKA/tH8QlnVnyuOut/OIAbcENHURR3JX/VHoyoPXMI8 60E7/Qnhl+B74MhITBgTHxc9fBrbUDCf31TwbtpmAkwdrBN6AV6VQmQ7fH7EjHI3bGyN Wy/FbVFIAefEnmRi8U4OPnvW7M0hkv1Zet+r4rD5jY+Dsv0pTed1nqZtkk9v9mxm4BAf /nEB6N9SU9fjmxPsM6SocStWKQ76XPprpx0rSGGEEZ8vwD4BsqH9HvL9Z/OZUYxXObrJ hp7adtNVhmwJid4XnA9JJ/+dBCPb2p6H4A1ZDsv9oa00fIBEuSgt9EVuso8AJ/AzzVfJ /lfw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=5BBuopGGv4pzFmwlMm3ds0558bCRBiB3rM8TFWkwyqE=; b=0q61oirLumRsMltugl5oaK51goDMJQNhz2rqsQB51mvdTz26PLbvJBxnYwV+2KRMvI 7GlaWCseVPj2eTYzJ3N0FT2U3WFT0vq+H4R1mt9W3hlKi0WZbV2elLl4IxK6hQN0fTV9 U97afNpM0nbQ+TBQ/26AGGduHJBvUH0x8KmjT7M1JEKSjHbKWYIVJDmaehX+wTczUdkz EGsAMkDH28EJlUcFyb/yVyUDtJ32FxtFE8s5tVr3ANWfizO1MjydjG/iTyC+IkBoZ7Hx nlu/nePyWevj3NRNM9EDnsCfSDjvJfFO8qiqqgMLK86RU/aSNFzQxtBlroxHYq3cVwkF tNIQ== X-Gm-Message-State: AO0yUKWoKuAlJn959/aW0fubC6KNbr5Am50m+Yl9GhR4hC/Hx/yjcnPh OhTj0mLP+Burk6boDofESj/O0ANy3Mo= X-Google-Smtp-Source: AK7set8EZCCp9OHwZakNUCUOxHkwqzdU2EV+ionxvRibrMmezewJVN+uGEiSYJJSZNSpJf1Md8FPiQ== X-Received: by 2002:a17:903:11c7:b0:19a:7a02:7954 with SMTP id q7-20020a17090311c700b0019a7a027954mr678681plh.24.1676331688811; Mon, 13 Feb 2023 15:41:28 -0800 (PST) Received: from ?IPV6:2601:681:5a00:f790:bea3:2a94:46b5:7f1d? ([2601:681:5a00:f790:bea3:2a94:46b5:7f1d]) by smtp.gmail.com with ESMTPSA id l5-20020a170902d34500b0019a6fe0f975sm1423248plk.267.2023.02.13.15.41.27 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 13 Feb 2023 15:41:28 -0800 (PST) Content-Type: multipart/alternative; boundary="------------30ssri0PuyZ0etSfbOlOAFnF" Message-ID: <9fbaac46-18cd-1913-7828-a819e9b69d55@gmail.com> Date: Mon, 13 Feb 2023 16:41:26 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.7.1 Subject: Re: HOWTO? Permissions for user to access a single db Content-Language: en-CA To: pgsql-general@lists.postgresql.org References: <1510083.1676325588@sss.pgh.pa.us> <1515580.1676328872@sss.pgh.pa.us> From: Rob Sargent In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------30ssri0PuyZ0etSfbOlOAFnF Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 2/13/23 16:14, Damian Carey wrote: > Thx Tom > > Fine advice that I will follow up. > > One tiny thing without wasting (too much) more of your time. > > In the working "promiscuous" version they get access the VPS as the > same linux user that my product is running on, and superuser PG access. > > In the failed version their SSH login is as a different and very > limited linux user, as well as their own postgres user name. > > Still on a "permissions" theme ... is their any glaring issues that > are required to provide a random linux user with permissions to access > a DB? > > (FYI every few years you graciously help me like this and I'm well > aware of our skill difference and the vague questions I regurgitate. > Kudos.) > I should wait for Tom to respond... What was the create user command? Or are you talking about the following? GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] } ON DATABASE/|database_name|/ [, ...] TO/|role_specification|/ [, ...] [ WITH GRANT OPTION ] [ GRANTED BY/|role_specification|/ ] --------------30ssri0PuyZ0etSfbOlOAFnF Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit
On 2/13/23 16:14, Damian Carey wrote:
Thx Tom

Fine advice that I will follow up.

One tiny thing without wasting (too much) more of your time.

In the working "promiscuous" version they get access the VPS as the same linux user that my product is running on, and superuser PG access.

In the failed version their SSH login is as a different and very limited linux user, as well as their own postgres user name.

Still on a "permissions" theme ... is their any glaring issues that are required to provide a random linux user with permissions to access a DB?

(FYI every few years you graciously help me like this and I'm well aware of our skill difference and the vague questions I regurgitate. Kudos.)


I should wait for Tom to respond...

What was the create user command?

Or are you talking about the following?

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]


--------------30ssri0PuyZ0etSfbOlOAFnF--