Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qLQOD-003553-Sc for pgsql-general@arkaria.postgresql.org; Mon, 17 Jul 2023 15:43:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qLQOC-00BIwT-EI for pgsql-general@arkaria.postgresql.org; Mon, 17 Jul 2023 15:43:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qLQOC-00BIwL-3j for pgsql-general@lists.postgresql.org; Mon, 17 Jul 2023 15:43:56 +0000 Received: from mail-wr1-x42d.google.com ([2a00:1450:4864:20::42d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qLQO9-0015X0-2g for pgsql-general@lists.postgresql.org; Mon, 17 Jul 2023 15:43:55 +0000 Received: by mail-wr1-x42d.google.com with SMTP id ffacd0b85a97d-3143798f542so4986676f8f.2 for ; Mon, 17 Jul 2023 08:43:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1689608630; x=1692200630; h=content-transfer-encoding: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=dQqDy4baaN+vNImtx4yJgjxEF+WvdoeMWSH8ocdYzsE=; b=NnVAGSBhr335BSxhKww7wdyuMDBzCnKafxH0hc8WJ9bA78pkA2scSfys9IXzYyF/7d H7BhEvikGrORRuktcvz0Ma43zNJK9+UOAxE8VpkZ2saJwY2wzDn2zSyxkAd5p2oAiizb p4a/c4JuwEAsjJhpnuHreQlY8bRc7Z3uzMJyc9XVOuP0lC0TqMgG65iV9zp8PjVIjHC+ oLv+qAwQ2WxB4IMDwHFTLRFWt+bc52Pnbm39m3aM0ebu9VNMu2oE4+g6CwRc5CGguT+R 5UKxi9DphYuhIfZkdXG2Ayovmu1pL3qRv7YZSfMJX+b4UwXOBeWWrbn+sj1BW3MysizI BKmw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1689608630; x=1692200630; h=content-transfer-encoding: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=dQqDy4baaN+vNImtx4yJgjxEF+WvdoeMWSH8ocdYzsE=; b=ZMSCQBdJD4vcEESBNTcEAyg7bFp/j5peETzzqQzS7gXJXjwklao/h1uwcsVFIZ+h3p O5UsqSjK9gHTbbiAR50K9zKd1gTfLx9XwBcvZbfhVfH5TvRBN+HN4BgJ6ulmKZauAczq Di0BKyLkO7HMA8vaPIiGuZJSSp21zVtbWilaxK4FGTN8OYJ2H/B4h4/LKmcyypBW28Lb 0FDQYvW7zL0A5bhqpSHaZkElfDaHL+/XeiKcZOFrXpcjVzfdPzOmlix8zERYUoQfZxw4 NnI75lLLWnZSE/M7F0S3te9/qwfa3+ec5p3fEBQmY/byvaNzHwVX0r65iM0iVQ1+JgHc xeyQ== X-Gm-Message-State: ABy/qLZOwh6phzpVajtTgUdi6XIHvdM++fGq5nVYsW+EYvbj0zTX5ubp 1/5uGB8RK0/+W2inJTpC0fzMwQ== X-Google-Smtp-Source: APBJJlHfYRuUHZ2gGCjuHtMZUqQqeLUqJyKjOfTyfGL/MVJdul9CcbExHwo8KgdS15Ix2i64K6UR4w== X-Received: by 2002:a05:6000:1050:b0:314:3c84:4da2 with SMTP id c16-20020a056000105000b003143c844da2mr10896295wrx.13.1689608630412; Mon, 17 Jul 2023 08:43:50 -0700 (PDT) Received: from [10.137.0.17] (ip-86-49-228-220.bb.vodafone.cz. [86.49.228.220]) by smtp.gmail.com with ESMTPSA id b5-20020a5d4b85000000b003143add4396sm19490433wrt.22.2023.07.17.08.43.49 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 17 Jul 2023 08:43:49 -0700 (PDT) Message-ID: Date: Mon, 17 Jul 2023 17:43:48 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.12.0 Subject: Re: How to grant read only functions execute permission to read only user Content-Language: en-US To: Yi Sun , PostgreSQL General References: From: Tomas Vondra In-Reply-To: Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 7/17/23 16:11, Yi Sun wrote: > Hello guys, > > Our read only user is okay to be granted read only permission of tables. > > How to grant read only functions execute permission to read only user, > is there a simple way to do it please? If not, how to get the list of > read only functions please? Then can grant one by one based on the list, > thanks What is read-only function? I don't think Postgres has anything like that. Functions inherit the privileges of the user that executes them by default. So if the user is read-only (i.e. has just SELECT privilege), then the function can't do any writes either. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company