As promised in one of the previous posts on “Install PostgreSQL Database on CentOS 6” here is a post describing how to manage PostgreSQL users (PostgreSQL create user, delete user, …).

By now you have probably successfully installed and started PostgreSQL server on your linux machine, but empty PostgreSQL server is not useful in any way. The next step is to create users, databases, assign ownership and access rights and other cool stuff PostgreSQL server supports.

In this how to we will describe how to create and delete users on PostgreSQL and also how to change user permissions and privileges.

PostgreSQL Create User

There are two ways of creating a user on PostgreSQL. First one is to create user via console command “createuser” and the second one is to create a user via “psql” SQL command.

We are assuming you are running a fresh and clean install of PostgreSQL server and did not touch “ph_dba.conf” file yet. This is important since by default authentication for local users is set to peer and enables “postgres” user to login via “psql” without any password prompt.

Console Command (PostgreSQL Create User)

We can add a new PostgreSQL user by running a “createuser” console command as “postgres” user. By default the following rules apply for users created with “createuser” command with no extra options used:

• Has no connection limit
• Can’t create databases
• Can’t create new roles
• Is not a superuser

In most cases all of these default settings are OK for a normal PostgreSQL database user, so the only parameter we want to add to the “createuser” command is the “-P” option which prompts us to define a password for newly created user:

[root@geekpeek ~]# su - postgres
 -bash-4.1$ createuser -P geek01
 Enter password for new role:
 Enter it again:

Voila, geek01 user has been created.

Of course you can add users with different rights by running the same command but adding additional options.

If we want our user “geek02” to be able to create new databases and new roles we must run “createuser” command with the following options:

-bash-4.1$ createuser -Pdr geek02
 Enter password for new role:
 Enter it again:

Check “createuser –help” for more information and options.

Psql SQL Command (PostgreSQL Create User)

We can also add new PostgreSQL users and roles via “psql” interface. In order to do this we run “psql” command in console as “postgres” user:

[root@geekpeek1 ~]# su - postgres
 -bash-4.1$ psql
 psql (9.3.5)
 Type "help" for help.

postgres=# CREATE USER geek01 PASSWORD 'geek01';
 CREATE ROLE
 postgres=#

Voila, user created!

If we want our user to be able to create new databases and new roles we must run the following syntax:

postgres=# CREATE USER geek02 PASSWORD 'geek02' CREATEDB CREATEROLE CREATEUSER;
 CREATE ROLE
 postgres=#

Please note that in “psql” CREATEUSER is an alias of CREATEROLE but with the difference that when using CREATEUSER, LOGIN is assumed by default.

PostgreSQL Delete User

We can delete existing PostgreSQL user via “psql” by issuing a DROP USER command. We will not be able to DROP an existing user if this user is the owner on an existing database and objects depend on it. In this case you will first have to change the owner of the database or drop the database.

[root@geekpeek1 ~]# su - postgres
 -bash-4.1$ psql
 psql (9.3.5)
 Type "help" for help.

postgres=# DROP USER geek01;
 DROP ROLE
 postgres=# DROP USER geek02;
 ERROR: role "geek02" cannot be dropped because some objects depend on it
 DETAIL: owner of database geek02
 postgres=# DROP DATABASE geek02;
 DROP DATABASE
 postgres=# DROP USER geek02;
 DROP ROLE
 postgres=#

PostgreSQLChange User Password

We can change PostgreSQL user password via “psql” with a simple ALTER USER command:

[root@geekpeek1 ~]# su - postgres
 -bash-4.1$ psql
 psql (9.3.5)
 Type "help" for help.

postgres=# ALTER USER geek01 PASSWORD 'changed';
 ALTER ROLE
 postgres=#

PostgreSQLChange User Privileges

We can also change PostgreSQL user privileges with the same ALTER USER command. If we want to give the user “geek01” the privileges to create new databases and create new roles we should run the following command:

postgres=# ALTER USER geek01 CREATEDB CREATEROLE CREATEUSER;
 ALTER ROLE

Another useful feature is limiting user connections to the server. We can achieve this with the following command:

postgres=# ALTER USER geek01 CONNECTION LIMIT 20;
ALTER ROLE

Additional Posts on PostgreSQL comming up! Stay tuned!