Skip to content

Instantly share code, notes, and snippets.

@khushalbokadey
Last active May 20, 2021 15:20
Show Gist options
  • Select an option

  • Save khushalbokadey/873f96fc93ff345863eea7aec53d53b1 to your computer and use it in GitHub Desktop.

Select an option

Save khushalbokadey/873f96fc93ff345863eea7aec53d53b1 to your computer and use it in GitHub Desktop.
Postgres basic user operations

After installation of postgres, login using superuser.

sudo su - postgres

List all the databases" \l

    postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_IN.UTF-8 | en_IN.UTF-8 | 
 template0 | postgres | UTF8     | en_IN.UTF-8 | en_IN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_IN.UTF-8 | en_IN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

List all the users: \du

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Create user

postgres=# CREATE USER root;
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 root      |                                                            | {}

Alter user

postgres=# Alter user root with Superuser;
ALTER ROLE

Update config: sudo vim /etc/postgresql/12/main/pg_hba.conf

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment