From hs@cybertec.at Fri Apr 13 22:32:01 2001 Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f3DMW1837421 for ; Fri, 13 Apr 2001 18:32:01 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3CBPE848656 for ; Thu, 12 Apr 2001 07:25:14 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f3CB08i56481 for pgsql-sql@postgresql.org; Thu, 12 Apr 2001 07:00:09 -0400 (EDT) (envelope-from news) From: =?iso-8859-1?Q?Hans=2DJ=FCrgen=20Sch=F6nig?= X-Newsgroups: comp.databases.postgresql.sql Subject: Bug in user management? Date: Thu, 12 Apr 2001 13:03:17 +0200 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 100 Message-ID: <3AD58B75.D5911B57@cybertec.at> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="------------3D019E226ABB11304C7D3D88" X-Complaints-To: scrappy@hub.org X-Mailer: Mozilla 4.76 [de] (X11; U; Linux 2.2.16-22 i686) X-Accept-Language: en To: pgsql-sql@postgresql.org X-Archive-Number: 200104/212 X-Sequence-Number: 2562 --------------3D019E226ABB11304C7D3D88 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit I need some information about PostgreSQL user management. I have created a user called epi who is not allowed to create database but allowed to create users. I have connected as user epi and have then created user kertal with the following command: shop=# CREATE USER kertal WITH PASSWORD 'anypasswd' CREATEDB CREATEUSER IN GROUP cybertec; According to the system table kertal is allowed to create databases - I have also tried this as user kertal and it works. In my opinion a user that has not the right to create database MUST NOT create user that are allowed to do it. Have I done something wrong or is it a bug? Hans persons=# SELECT * FROM pg_user ; usename | usesysid | usecreatedb | usetrace | usesuper | usecatupd | passwd | valuntil ----------+----------+-------------+----------+----------+-----------+----------+---------- postgres | 26 | t | t | t | t | ******** | hans | 27 | f | f | f | f | ******** | epi | 28 | f | f | t | t | ******** | kertal | 29 | t | f | t | t | ******** | (4 rows) -- ------------------------------------------------------------------- Cybertec Geschwinde &. Schönig OEG; Schönbrunnerstraße 133/18 A-1050 Wien; http://postgres.cybertec.at; Fax.: +43/1/961 71 58 Tel.: +43/664/233 90 75 --------------3D019E226ABB11304C7D3D88 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit I need some information about PostgreSQL user management.
I have created a user called epi who is not allowed to create database but allowed to create users.
I have connected as user epi and have then created user kertal with the following command:

shop=# CREATE USER kertal WITH PASSWORD 'anypasswd' CREATEDB CREATEUSER IN GROUP cybertec;

According to the system table kertal is allowed to create databases - I have also tried this as user kertal and it works.
In my opinion a user that has not the right to create database MUST NOT create user that are allowed to do it.

Have I done something wrong or is it a bug?

    Hans
 

persons=# SELECT * FROM pg_user ;
 usename  | usesysid | usecreatedb | usetrace | usesuper | usecatupd |  passwd  |
valuntil
----------+----------+-------------+----------+----------+-----------+----------+----------
 postgres |       26 | t           | t        | t        | t         | ******** |
 hans     |       27 | f           | f        | f        | f         | ******** |
 epi      |       28 | f           | f        | t        | t         | ******** |
 kertal   |       29 | t           | f        | t        | t         | ******** |
(4 rows)
 

-- 
-------------------------------------------------------------------
Cybertec Geschwinde &. Schönig OEG; Schönbrunnerstraße 133/18
A-1050 Wien; http://postgres.cybertec.at; Fax.: +43/1/961 71 58
Tel.: +43/664/233 90 75
  --------------3D019E226ABB11304C7D3D88-- From tgl@sss.pgh.pa.us Fri Apr 13 23:10:38 2001 Received: from sss.pgh.pa.us ([216.151.103.158]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3DNAc844520 for ; Fri, 13 Apr 2001 19:10:38 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f3DNAUR08619; Fri, 13 Apr 2001 19:10:30 -0400 (EDT) To: =?iso-8859-1?Q?Hans=2DJ=FCrgen=20Sch=F6nig?= cc: pgsql-sql@postgresql.org Subject: Re: Bug in user management? In-reply-to: <3AD58B75.D5911B57@cybertec.at> References: <3AD58B75.D5911B57@cybertec.at> Comments: In-reply-to =?iso-8859-1?Q?Hans=2DJ=FCrgen=20Sch=F6nig?= message dated "Thu, 12 Apr 2001 13:03:17 +0200" Date: Fri, 13 Apr 2001 19:10:30 -0400 Message-ID: <8616.987203430@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200104/219 X-Sequence-Number: 2569 =?iso-8859-1?Q?Hans=2DJ=FCrgen=20Sch=F6nig?= writes: > I have created a user called epi who is not allowed to create database > but allowed to create users. The "CREATEUSER" option is rather badly mislabeled: the privilege it actually grants is superuser status. You are laboring under a severe misapprehension if you think that epi has ANY restrictions on what he can do ... regards, tom lane From cedarc@visionforisrael.com Sat Apr 14 20:09:34 2001 Received: from alpha.netvision.net.il (alpha.netvision.net.il [194.90.1.13]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3EK9Yu29487 for ; Sat, 14 Apr 2001 16:09:34 -0400 (EDT) (envelope-from cedarc@visionforisrael.com) Received: from nanu.visionforisrael.com (ras3-p25.jlm.netvision.net.il [62.0.146.25]) by alpha.netvision.net.il (8.9.3/8.8.6) with ESMTP id XAA27436 for ; Sat, 14 Apr 2001 23:09:27 +0300 (IDT) Received: from localhost (cedarc@localhost) by nanu.visionforisrael.com (8.9.3/8.9.3) with ESMTP id AAA13249 for ; Sun, 15 Apr 2001 00:09:16 +0300 Date: Sun, 15 Apr 2001 00:09:16 +0300 (IDT) From: Cedar Cox To: pgsql-sql@postgresql.org Subject: Re: Bug in user management? In-Reply-To: <8616.987203430@sss.pgh.pa.us> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200104/226 X-Sequence-Number: 2576 While we're on this subject, where is the documentation on pg_shadow? Specifically, what it 'usetrace' for? -Cedar On Fri, 13 Apr 2001, Tom Lane wrote: > =?iso-8859-1?Q?Hans=2DJ=FCrgen=20Sch=F6nig?= writes: > > I have created a user called epi who is not allowed to create database > > but allowed to create users. > > The "CREATEUSER" option is rather badly mislabeled: the privilege it > actually grants is superuser status. You are laboring under a severe > misapprehension if you think that epi has ANY restrictions on what he > can do ... > > regards, tom lane