User Administration under PostgreSQL 8.3

A while ago I published an article on PostgreSQL
user administration
. Typically, things have changed since I wrote
that article. I thought I’d detail a couple of the differences since
I wrote that guide.

The major difference is that you now have roles rather than users and
you use the CREATE ROLE command to create them instead of
CREATE USER, although the latter command still works. The
command line options for the createuser command have changed as
a result too. Before superuser and the ability to create new users were
the same thing. Now you can give a role permissions to create new roles
without giving them superuser powers. The options are now -s for
superuser and -S for not superuser, -d to allow them to create
databases and -D to disallow database creation and -r to allow the new
role to create other roles and -R to prevent them. for a standard user
you probably want somethig like:

createuser -S -D -R -P user

The -P makes createuser ask you for a password for
the new role.

You can find out more information about the new role system in
PostgreSQL in the user
management
and CREATE
ROLE
reference
sections of the manual.

3 thoughts on “User Administration under PostgreSQL 8.3

  1. Hi David,

    > […] you use the CREATE ROLE command to create them instead of CREATE USER, although the latter command still works.

    The documentation is unfortunately a bit misleading here – CREATE USER is still the proper command to create users, because CREATE ROLE will not grant LOGIN to the new role. I’ll try prodding the PostgreSQL guys in our company to reword the documentation there so this is made more explicit.

  2. osman mohamed
    on said:

    in postgre and phpPgAdmin, why each role(user) can accesses to all the databases after logon?

    how can I prevent that?
    note:
    I use PgAdmin III as an interface.

  3. I have an application which uses Postgres.
    The application periodically drop tables and the recreates
    them. I have to manage the users privileges
    on these tables. I can NOT change anything in the application code. When the application drops and recreates
    tables all GRANTS are dropped and I have to recreate
    GRANTS from the scratch. Any approach not to repeat such job every time?

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>