Обсуждение: User with "almost" superuser privileges

A SUPERUSER will bypass every database privileges (if it have a right entry in pg_hba), so if you want to create an "almost superuser" you'll have to look at GRANT and REVOKE (that have no effect to superusers). You may also create a role with CREATEDB then create the database you want. Take a look at: http://www.postgresql.org/docs/9.2/static/ddl-priv.html.

I hope this has helped, sorry for my bad english.

brno

2013/7/18 Daniel Gomez Blanco <nanodgb@gmail.com>

Hello,

I'd like to create a user that is almost a superuser, but without some privileges. I cannot create a superuser and then revoke privileges from them, because by definition nothing can be revoked from superusers. So how can I know what a superuser is able to execute compared to a user with the rest of attributes, but without superuser? If I knew this I could grant those privileges after I create the user.

Cheers,

Daniel

On Thu, Jul 18, 2013 at 3:51 PM, Daniel Gomez Blanco <nanodgb@gmail.com> wrote:
> I'm grating all the functionality this "almost superuser" needs, expect the
> functions I disallow (like pg_ls_dir for example). But I still don't know if
> I'm granting all the functionality a superuser has. What would be great is
> some documentation explaining a bit more what a superuser is able to do.
> Unfortunately, I haven't found any. All I have found is some random "you
> need to be superuser to do this", but not a complete list of what a
> superuser can do...
>

A superuser is a user to which security restrictions are not applied
and that has a set of attributes like those you can set using a CREATE
ROLE. On the other hand, you can see what operations require to be a
superuser. I did the following (not an accurate way, but give you an
idea):

% grep  "must be superuser" backend/po/es.po
/mnt/postgresql/src/postgresql-9.2.4.src/src
msgid "must be superuser or replication role to run a backup"
msgid "must be superuser to switch transaction log files"
msgid "must be superuser to create a restore point"
msgid "must be superuser to control recovery"
msgid "must be superuser"
msgid "must be superuser to set schema of %s"
msgid "must be superuser to COPY to or from a file"
msgid "must be superuser to create a cast WITHOUT FUNCTION"
msgid "must be superuser to create an operator class"
msgid "must be superuser to create an operator family"
msgid "must be superuser to alter an operator family"
msgid "must be superuser to create procedural language \"%s\""
msgid "must be superuser to create custom procedural language"
msgid "must be superuser to create text search parsers"
msgid "must be superuser to rename text search parsers"
msgid "must be superuser to create text search templates"
msgid "must be superuser to rename text search templates"
msgid "must be superuser to create a base type"
msgid "must be superuser to create superusers"
msgid "must be superuser to create replication users"
msgid "must be superuser to alter superusers"
msgid "must be superuser to alter replication users"
msgid "must be superuser to drop superusers"
msgid "must be superuser to rename superusers"
msgid "must be superuser to set grantor"
msgid "must be superuser to use server-side lo_import()"
msgid "must be superuser to use server-side lo_export()"
msgid "must be superuser to reset statistics counters"
msgid "must be superuser to do CHECKPOINT"
msgid "must be superuser to read files"
msgid "must be superuser to get file information"
msgid "must be superuser to get directory listings"
msgid "must be superuser or have the same role to cancel queries
running in other server processes"
msgid "must be superuser or have the same role to terminate other
server processes"
msgid "must be superuser to signal the postmaster"
msgid "must be superuser to rotate log files"
msgid "must be superuser to connect during database shutdown"
msgid "must be superuser to connect in binary upgrade mode"
msgid "must be superuser or replication role to start walsender"
msgid "must be superuser to examine \"%s\""


Hope this helps.
Luca