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.
on said:
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.
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.
on said:
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?