CATALOG/NOCATALOG for new users

From: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>
To: pgsql-patches(at)postgresql(dot)org
Subject: CATALOG/NOCATALOG for new users
Date: 2003-12-24 12:51:07
Message-ID: 20031224204157.D50432-200000@houston.familyhealth.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

This is a preliminary patch - don't commit it.

What this patch adds are the CATALOG and NOCATALOG clauses to the CREATE
USER and ALTER USER commands.

These clauses affect the usecatupd column. This makes it easy to create
superusers who cannot manually modify columns (a very nasty power...)

These days, Postgres's built-in command set can do everything you need to
to (except disable triggers and delete all the users...), so I don't see
why people who have the power to create users should also have the power
to munge your entire db server.

There are a few problems that need thinking about, and I would like
comments on how to address them:

1. Should we only allow users who currently hold the catalog perm to grant
it to others? I think yes, since otherwise a regular superuser can create
themselves another account with the catalog priv.

2. Restoring a dump (or dumpall more specifically perhaps) now requires
that the restoring user is more than just a superuser, they must also hold
the catalog priv. This is why:

DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database
WHERE datname = 'template0');

And also this:

-X disable-triggers

3. Upgrading from previous postgres will not give their old superusers
back their catalog privilege, unless they dump with 7.5's pg_dump.

Comments?

Chris

Attachment Content-Type Size
user.txt text/plain 24.4 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2003-12-24 15:21:45 Re: CATALOG/NOCATALOG for new users
Previous Message Tom Lane 2003-12-24 05:16:58 Re: Bug in new buffer freelist code

Browse pgsql-patches by date

  From Date Subject
Next Message Tom Lane 2003-12-24 15:21:45 Re: CATALOG/NOCATALOG for new users
Previous Message Jan Wieck 2003-12-24 12:22:13 Re: Doc patch--clarifying $1 in PL/PgSQL