Postgresql User Management in Nutshell

In this article, I am going to discuss about creating users, assigning role permissions, database ownership and granting/revoking privileges in Postgresql using console-based psql client.

First to login to psql :

$ psql -U postgres

You can’t login as postgres user if it’s your first time, if so you have to set the password of ‘postgres’ user. Run the following command and enter password twice for the ‘postgres’ user :

$ sudo passwd postgres

Also if it’s the fresh installation of postgresql, you need to create a directory for postgres to store the data :

$ su — postgres -c "initdb — locale en_US.UTF-8 — D 'var/lib/postgres/data'"

Also, run following commands to start the postgres server, and enable to auto-start during PC startups.

$ sudo systemctl start postgresql

$ sudo systemctl enable postgresql

Creating/Drop Role

Note: role and user means the same in postgresql.

> CREATE ROLE jenny WITH NOINHERIT LOGIN PASSWORD 'jenny_password'; Role permissions choices are : SUPERUSER, NOSUPERUSER, CREATEROLE, NOCRATEROLE, CREATEDB, LOGIN, NOLOGIN.

> DROP ROLE jenny; Delete user ‘jenny’

Altering Role Permissions

> ALTER ROLE jenny CREATEROLE CREATEDB; Allow jenny to create other users and create databases.

> GRANT jenny to rachael; Grant all role permissions of user ‘jenny’ to user ‘rachael’

Creating Database and Database Ownerships and Privileges

Note: privileges and restrictions can be set on database entities like table, column, views, function or database as a whole too

> CREATE DATABASE my_db [OWNER rachael]; Create database, and optionally set owner of database

> ALTER DATABASE my_db OWNER TO jenny; Change database owner

> REASSIGN OWNED BY jenny TO jericho; Everything (including all DBs) that was owned by ‘jenny’ is not owned by ‘jericho’

> GRANT ALL PRIVILEGES ON DATABASE my_db TO jericho; Another way to grant privileges

> GRANT SELECT (id, first_name) ON TABLE employee_record TO jericho; Grant limited privileges

> ALTER ROLE jericho SET client_encoding TO 'utf8'; Set data encoding format

> ALTER ROLE jericho SET timezone TO 'UTC'; Set timezone

> ALTER ROLE jericho SET default_transaction_isolation TO 'read_committed'; Set transaction isolation policy

> \c database_name role_name; Connect to database ‘database_name’ as ‘role_name’ user.

Login To psql Console Without $ psql -U postgres

You need to create a database in Postgres with the same name you are logged into the OS.

Create postgres user interactively :
First login as ‘postgres’ user:

$ su — postgres Password prompt will appear, type password of postgres user that you have set up earlier, if not the default password is ‘postgres’.

$ createuser --interactive --pwprompt In the same terminal, type this command and hit ‘Enter’, then you can type role name and password. Note: role name and your OS logged-in user name must be same.

$ CREATE DATABASE role_name; Create database with the same name of Postgres role that you created in previous step

Now, you can quit the console and you can now login to psql by just typing $ psql in terminal without taking help of ‘postgres’ user. Now, you can repeat the above steps for other system users too.