Postgres – Basic administrative tasks – user, database, privilege and roles


These titbits will help you get up and running quickly with postgres. They include often used commands for performing basic administrative tasks.

1. login to postgres as superuser (below command is for Ubuntu. Command will be different for other OS platforms)

sudo -u postgres psql postgres



2. Database administration

create Database

postgres=# create database dbname;

delete database

postgres=# drop database dbname



3. User administration

create user

postgres=# create user testuser with password 'mypassword';

verify user is successfully created

postgres=# select usename from pg_user;



4. Privileges

make user a superuser

postgres=# alter user testuser superuser;

–NOTE that a superuser has all the privilege, so don’t grant the privilege unless it is really needed.

grant user privilege to create database

postgres=# alter user testuser createdb;

Other privilege include:
– createrole
– replication

view user privileges

postgres=# \du

delete user

postgres=# drop user testuser;



5. Roles

create role

postgres=# create role role_name;

delete role

postgres=# drop role role_name;
Reviews
Sending
User Review
0 (0 votes)


About Matthias 33 Articles
I am a Software Engineer from Houston, TX who love to write codes that brings great ideas to live. In my professional life, I have created software for different industries including Oil & Gas, Finance, Service Provider, Cloud Computing and Embedded Systems. When not writting codes, i enjoy travelling, good music and photography. You can reach me at me@matthiasomisore.com.

Be the first to comment

Leave a Reply

Your email address will not be published.


*