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 1qZXOi-005mgc-GD for pgsql-general@arkaria.postgresql.org; Fri, 25 Aug 2023 14:02:48 +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 1qZXOh-003UTi-Bc for pgsql-general@arkaria.postgresql.org; Fri, 25 Aug 2023 14:02:47 +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 1qZXOg-003UTX-W0 for pgsql-general@lists.postgresql.org; Fri, 25 Aug 2023 14:02:46 +0000 Received: from mout.gmx.net ([212.227.15.15]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qZXOc-000u45-Lu for pgsql-general@lists.postgresql.org; Fri, 25 Aug 2023 14:02:45 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1692972160; x=1693576960; i=shammat@gmx.net; bh=ZDhrYTVJiI8bOwV/4M4CeLrZNA0TP1ZoVaH1b2kZRX8=; h=X-UI-Sender-Class:Date:From:Subject:To:References:In-Reply-To; b=JQkzdNfPwFAmmScEQNK9W1o2BFW/+WNS+bkSlMkFnytGTM9JHOVNj2psUfLueq+kqxRc6KS hNhh9jOuHYCeuhfUgHJao0k1jrKxDCxyqndLgyYaDLYUnZ7nOZmn04OqMo3ETq89AB+y2/NYM AqEgpxu1pKxPg6GskXyWuinz3CdZN/faQUqewdyNC3xkoT1qELz7GG26hz41QrA5jZUw5XkLM MYerGZgfPkPKJQ9xBRQheqrPPXZIBMcogX+thb/mv6IfzXepEeYHM1KDCA3qfood+L6Qv0rIw WUTeEzdW1DuDae5cWMVO9jKIqIf71qJQ+sJi90cIzwUhxWxIh7VQ== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [10.8.0.19] ([185.40.248.10]) by mail.gmx.net (mrgmx005 [212.227.17.190]) with ESMTPSA (Nemesis) id 1MKsjH-1qHBjZ2dJd-00LIX0 for ; Fri, 25 Aug 2023 16:02:40 +0200 Message-ID: <5c41503d-ff8e-483a-9736-4a36fa64e8a1@gmx.net> Date: Fri, 25 Aug 2023 16:02:40 +0200 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird From: Thomas Kellerer Subject: Re: Role for just read the data + avoid CREATE / ALTER / DROP To: pgsql-general@lists.postgresql.org References: Content-Language: de-DE In-Reply-To: Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Provags-ID: V03:K1:PBBbqcXrgkNZ02Nf/2Rm6htjnB5UaaxbK9yuySfOJVUQLAYqlJd AvKztGIXVKdJ3V8xGz1GEkVZAyaww8oy4EChC9oQm5hZRkKEYPsD3HUpe+dFy+1pt3Fr2G9 DHiekA5jPAC/ldjRG7myodpV84pVzbbX/Jgk4h0BJdvm3JLthzsbyuTizHMxBOM9Rkyk2sk eNRfYKeKZfq5IAzbKoaeA== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:2IBM9NjErdw=;E1JAbC5PTwIspVHCGkKRr+7oCEg S7/188ejP5ElnyLGZn2jv00zxoDqpPHzVj48fZsSOO9OHvO9Mj6vXCQmBmxUx0g38r2vNluQn G7YPvVaHFhVQ7ryvNnupLvEExMHQGIOWpihwTxIKo1rpzLBEIwpzRO3AnymVm79BwvyCw8gf6 Z15BCTTiMW6/dz07ofQrh7KfEF5WrBUc3xRdorkQ888T74HdNZZcNi4+AUX1TnlFdgrdT5JjO bNGmSgGKnslDExcBRMLdJxK7o5l8iflgnUSnrKuZX4Ig2WrPIKMNcIiJYZVNHe3UNklnb6jCk 2j94FUqT6dzbB4zBtVdKkQGhzSg9Kp1q7P3gACltUvDwbyGPnRzMh4jZzzjXLdDa2bc3jx33J +EZrQYLsCXlH/kCsvkBIA0JmT22Q/tD1+Pqnj4io6vPEb0kBGTsZ35/OQbaB88s1sVNIGrmKA 7yzHWkUEsztUQv1rWfVvVhic3MOQ4z6KvxrH9bAMltZQg1xHcdqAN5tf4NZcY6YbClikg0YMi y+dk+gKWN0jiFITszwwRLklqbMWrZqCYTjMKrpBp+RT0Ac2JWJ9vgwA7xKZQPJEtigIi+dVmF lZuTHFCgww+0kVCOsTuUgiWhicFM6vLuCDOCR301C6T0+jDtr/QFdy99T7Nhbh8sk7eF3v3H6 2MF0TULCJdCBvaCjsQ/1lqbehnspRGjdbXT5Pjfi+IzGwzzBJI3Nk6VHGwdOrL01MY3GtAc4D +Mzz9FcgHwIpRLQdJaFOAeBSsP1jPkZJjZIbAnR6rlMnQkNJtTVG6er89HTh5p7lIINvxX//C /mPJaIsMG4JfBIRyk71Zzn3tXfQerdDvFT6lAXFfN6xddai/RX45Kn9iN/mdb6J8qNQHEqJPR yZ1UymO69HB0qs1A8MHGLfrMGs1sJk+PnHObfK5IER5yfKAYYxZ0HOtJh6mVSwwSeP4VWi6DG XNqU+w== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Durumdara schrieb am 25.08.2023 um 14:38: > Normally we use the "db owner" role for the connection, but this can do = everything (DDL-DML). > Somewhere they want to access a DB through a Read Only connection. > > In MS-SQL Server it is simple, but in PG it seems to be some kind of "he= ll". > Formerly we tried to use multiple roles with lower rights, but we had > wrong experiences with them, so we stopped very soon.> > So: is there any easier way to make ReadOnly access to a database? With Postgres 15 it should be as simple as: CREATE ROLE CREATE ROLE u_tdb_ro WITH LOGIN; GRANT pg_read_all_data TO u_tdb_ro; In previous versions the PUBLIC (pseudo) role was granted the CREATE privi= lege on the public schema which is no longer the case since Postgres 15 For previous versions it's highly recommended to do this as well: revoke create on schema public from public; > But: I can't avoid that the user can execute a CREATE TABLE command! > > set role to u_tdb_ro; > drop table if exists test_rororo; > create table if not exists test_rororo (roro int primary key); > select * from test_rororo; Most likey you are indeed using an older Postgres version that still granted USAGE on the public schema to the role public and therefor the CREATE TABLE succeeded. Given the grants you have shown, the DROP TABLE could only succeed if u_td= b_ro was the owner of the table. > I read that the magic command is: > > REVOKE CREATE ON SCHEMA public FROM PUBLIC; > > Why does this work, and why are the 3 above not??? > What is the meaning of these 3 if they don't work? A GRANT given to the public role will always be available to all roles in the system regardles of the grants to that specific role.