PostgreSQL create user

Corporate applications are being built with the idea of being able to control the access to the content, to structure and manage the information easily. As in most databases, the user's policy plays an important role in PgSQL as well. Used correctly, it allows you to have all-round control over the access to the objects in the database.

Postgre saves user information in its own catalogue different from that of the operation system where PgSQL is installed. This means that all connections to PgSQL have to be performed by the user of the respective database who gives authorization rights via a username and a password. PostgreSQL offers two methods of creation of database users, both of which demand a superuser authorization, because only superusers can create new user accounts.

Postgre saves user information in its own catalogue different from that of the operation system where PgSQL is installed. This means that all connections to PgSQL have to be performed by the user of the respective database who gives authorization rights via a username and a password. PostgreSQL offers two methods of creation of database users, both of which demand a superuser authorization, because only superusers can create new user accounts.

Create a user with a web interface

The first method is by using a web based interface. There are several web based interfaces for PostgreSQL - pSQL, pgAdmin, phpPgAdmin. Here, at NTC Hosting, we use the stable and proven phpPgAdmin, which is very similar to the phpMyAdmin interface, used for managing MySQL databases.

In order to access the phpPgAdmin, simply go to the "PostgreSQL Databases" menu in the Web Hosting Control Panel. In there, you will see a list of all the databases created so far. To go into the phpPgAdmin interface, simply click on the respective icon next to the database.

Create a user with the command line

You can also create a user by using the command-line wrapper called createuser - this method is more convenient for programmers and administrators who have access to the console of the respective PgSQL server and only have to execute a single command instead of logging in and using the interface of the respective PostgreSQL client.

An example of how to Create User in PostgreSQL

Add privileges to a user

Just like other SQL languages, in PostgreSQL you will have to grant the user privileges to manage a database. Without them, he will not be able to do anything. Possible privileges, which can be given to a user, are SELECT, INSERT, UPDATE, DELETE, RULE or ALL PRIVILEGES. This way, if you have several users working on the same database, you can define their access level. The syntax for granting privileges is the following one: GRANT [the privileges you want to grant] ON [the name of the database] TO [the user].

An example of how to Grant Privileges in PostgreSQL

Revoke privileges from a user

Once you have added privileges to a certain user, you can also revoke them. Again, just like with adding privileges, you can revoke just certain privileges from a user, or you can also revoke all privileges. The syntax for the REVOKE command is the same as for the GRANT one.

An example of how to Revoke Privileges in PostgreSQL

Delete a user

You can also delete the whole user, instead of revoking his privileges. Deleting the users will not affect the actual database in any way. To delete a user, you must first make sure he is now the owner of the database, otherwise you will receive an error message. The command for deleting the user is: DROP USER. The syntax for it is: DROP USER [user]

An example of how to Delete a User in PostgreSQL

PostgreSQL users with NTC Hosting

To facilitate its clients, with each database NTC Hosting automatically creates a username with all privileges, once the database is created. The user will have the same name as the database. This way, you can create a new database in less than 10 seconds, entering just a username and password.

Resources:

• PostgreSQL create user
• PostgreSQL grant