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 1rbkZJ-00GoJT-8n for pgsql-general@arkaria.postgresql.org; Sun, 18 Feb 2024 17:03:10 +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 1rbkZH-00APLk-GS for pgsql-general@arkaria.postgresql.org; Sun, 18 Feb 2024 17:03:07 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rbkZF-00APLV-PB for pgsql-general@lists.postgresql.org; Sun, 18 Feb 2024 17:03:07 +0000 Received: from wout4-smtp.messagingengine.com ([64.147.123.20]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rbkZB-007OL2-JT for pgsql-general@lists.postgresql.org; Sun, 18 Feb 2024 17:03:04 +0000 Received: from compute6.internal (compute6.nyi.internal [10.202.2.47]) by mailout.west.internal (Postfix) with ESMTP id 585B6320077A; Sun, 18 Feb 2024 12:02:59 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute6.internal (MEProxy); Sun, 18 Feb 2024 12:02:59 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:content-type:date :date:from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm3; t=1708275778; x=1708362178; bh=fKA7TBeKvz3GOUauWMlkpwzZ6k76F2HOPL99gJEkt0k=; b= dYCbz/5JTWPY+Ot8qvahPLVvotAGH17QXsTOCW365wvvu9Pb8VILIuUDfPMrwwjb d1DbX5Vy/9QgUPcKJ4QRNckKiOVZunI4uZSf7iRgsbuVk01h2s4F/sGUeIAwpYpm T/b7PcGvYw54MgRbU3SmsNgI/2yrRsIcc1UmNaVz+degOKJpuaas2BpVRq5s8YeJ WFFBuS8CWquWl8GFZNf15oJa7HiXZLNn2GAVimcnufanK5l2mkK3gsAkyauB3XQM hdnIiNN8jVAXmeGHjnLVST5YrDiGC90d3mLHSSMFTtGZBHSrT4QPlfoJFJfW4rGG OFc9lS/N8beuFLbUk41XHg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1708275778; x= 1708362178; bh=fKA7TBeKvz3GOUauWMlkpwzZ6k76F2HOPL99gJEkt0k=; b=T 4NiMx2dpnoax4k61W3nKGyLyIiZlwlinYum4ppRZrXw0DZusBIwKIyCkfJSnvkqQ nVZ+gxqe6hq7GjuPy0ZQ0PeagqXP/30qshqJMQI47ookcz+44G+RRUnsYOrKjDPv HEk85FD2XpI2QnPDE1b2rhuSl59uRLuoEYbCdJiNLhisbse6/E7TjwsnNcvrUbdL jE/e4gcVElFDentel8A/j/j7+z5jTTR2ysqT76UiZ0n/9iz46SpxRl7nx6WTWJLS fFrKjkeDJmiBUFjeTDKAJxCnd7erE7Jq+Dh+t57OK7X0DmaE6wkQLkwlM3+pAFay PJKh+rsO5dXQh9/k/D5aQ== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrvdeigdelkecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpefkffggfgfuvfevfhfhjggtgfesthekredttddvjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpefgudefkefhveefleevieeuveehvdduudekuddvvdel hfeuueeijedtuedvvedvueenucffohhmrghinhepphhoshhtghhrvghsqhhlrdhorhhgne cuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhi rghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Sun, 18 Feb 2024 12:02:58 -0500 (EST) Message-ID: Date: Sun, 18 Feb 2024 09:02:57 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Users and object privileges maintenance To: Dominique Devienne , Lok P Cc: pgsql-general References: Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 2/18/24 02:12, Dominique Devienne wrote: > On Sat, Feb 17, 2024 at 10:50 PM Lok P > wrote: > > We were having past experience in Oracle and are newly getting moved > to postgres database. [...] > So I just wanted to understand if these grants and privileges for > objects/users are given and maintained in a similar way in postgres > database > > > Yes they are. Having done that move from Oracle to PostgreSQL, here are > a few pointers. > More information on a couple of points I have found trips people up on occasion. > > "DBAs" in PostgreSQL are ROLEs with the SUPERUSER privilege. (e.g. the > built-in postgres ROLE) This is a convention not a rule. More specifically it is the name of the OS user that runs initdb. It can be overridden by using -U > USERs are ROLEs with the LOGIN privilege. Again a convention not a rule. This harks back to the days(v 8.0-) when Postgres had groups and users. Per https://www.postgresql.org/docs/current/sql-createuser.html "CREATE USER is now an alias for CREATE ROLE. The only difference is that when the command is spelled CREATE USER, LOGIN is assumed by default, whereas NOLOGIN is assumed when the command is spelled CREATE ROLE." It is entirely possible to CREATE USER ... WITH NOLOGIN ... -- Adrian Klaver adrian.klaver@aklaver.com