How to create/drop user in PostgreSQL DB
Create Non Super User DB1
C:\Users\amits>createuser -U postgres -P -S db1
Enter password for new role:
Enter it again:
Password:
C:\Users\amits>psql -U postgres
Password for user postgres:
psql (16.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
amit | Superuser
amit2 |
db1 |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
postgres=# exit
Create Super User DB2
C:\Users\amits>createuser -U postgres -P -s db2
Enter password for new role:
Enter it again:
Password:
C:\Users\amits>psql -U postgres
Password for user postgres:
psql (16.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
amit | Superuser
amit2 |
db1 |
db2 | Superuser, Create role, Create DB
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
postgres=# exit
Super user db2 able to connect with DB
C:\Users\amits>psql -U db2 -d amit
Password for user db2:
psql (16.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
amit=# exit
Non Super user db2 able to connect with DB
C:\Users\amits>psql -U db1 -d amit
Password for user db1:
psql (16.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
amit=> exit
Super User able to connect with Postgres Database
C:\Users\amits>psql -U db2 -d postgres
Password for user db2:
psql (16.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# exit
Non Super User able to connect with Postgres Database
C:\Users\amits>psql -U db1 -d postgres
Password for user db1:
psql (16.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=> exit
Non Super User not able to create user in Postgres Database
C:\Users\amits>psql -U db1 -d postgres
Password for user db1:
psql (16.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=> create user user1 login superuser password 'Oracle';
ERROR: permission denied to create role
DETAIL: Only roles with the CREATEROLE attribute may create roles.
postgres=> exit
Super User able to create user in Postgres Database
C:\Users\amits>psql -U db2 -d postgres
Password for user db2:
psql (16.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# create user user1 login superuser password 'Oracle';
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
amit | Superuser
amit2 |
db1 |
db2 | Superuser, Create role, Create DB
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
user1 | Superuser
postgres=# exit
Create User in interactive way Postgres Database
C:\Users\amits>createuser -U postgres --interactive
Enter name of role to add: user2
Shall the new role be a superuser? (y/n) y
Password:
C:\Users\amits>createuser -U postgres --interactive
Enter name of role to add: user3
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y
Password:
C:\Users\amits>psql -U postgres -P
psql: option requires an argument -- P
psql: hint: Try "psql --help" for more information.
C:\Users\amits>psql -U postgres
Password for user postgres:
psql (16.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+----------------------------+----------------------------+------------+-----------+-----------------------
amit | postgres | UTF8 | libc | English_United States.1252 | English_United States.1252 | | |
postgres | postgres | UTF8 | libc | English_United States.1252 | English_United States.1252 | | |
template0 | postgres | UTF8 | libc | English_United States.1252 | English_United States.1252 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | English_United States.1252 | English_United States.1252 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(4 rows)
postgres=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
amit | Superuser
amit2 |
db1 |
db2 | Superuser, Create role, Create DB
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
user1 | Superuser
user2 | Superuser, Create role, Create DB
user3 | Create role, Create DB
postgres=# psql -U postgres
postgres-# Oracle
postgres-# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+----------------------------+----------------------------+------------+-----------+-----------------------
amit | postgres | UTF8 | libc | English_United States.1252 | English_United States.1252 | | |
postgres | postgres | UTF8 | libc | English_United States.1252 | English_United States.1252 | | |
template0 | postgres | UTF8 | libc | English_United States.1252 | English_United States.1252 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | English_United States.1252 | English_United States.1252 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(4 rows)
Revoking public connection in DB and testing Connection
postgres=# revoke connect on database amit from public;
REVOKE
postgres=# \q
C:\Users\amits>psql -U postgres -d amit
Password for user postgres:
psql (16.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
amit=# exit
C:\Users\amits>psql -U user3 -d amit
Password for user user3:
psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL: password authentication failed for user "user3"
Drope User in PostgreSQL using dropuser utility and CLI
C:\Users\amits>dropuser -U postgres user1
Password:
C:\Users\amits>psql -U postgres
Password for user postgres:
psql (16.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
amit | Superuser
amit2 |
db1 |
db2 | Superuser, Create role, Create DB
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
user2 | Superuser, Create role, Create DB
user3 | Create role, Create DB
postgres=# exit
C:\Users\amits>psql -U postgres
Password for user postgres:
psql (16.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# drop user user2;
DROP ROLE
postgres=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
amit | Superuser
amit2 |
db1 |
db2 | Superuser, Create role, Create DB
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
user3 | Create role, Create DB
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
amit | Superuser
amit2 |
db1 |
db2 | Superuser, Create role, Create DB
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
user3 | Create role, Create DB
postgres=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
amit | Superuser
amit2 |
db1 |
db2 | Superuser, Create role, Create DB
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
user3 | Create role, Create DB
postgres=# drop user user3;
DROP ROLE
postgres=# drop user db1;
DROP ROLE
postgres=# drop user db2;
DROP ROLE
postgres=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
amit | Superuser
amit2 |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
postgres=# exit
Comments
Post a Comment