• Home
  • Services
    • DBA Support
    • DBA Consultancy Services
    • PostgreSQL Support
    • Website Maintenance
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum Database

    Greenplum Database

    $2,000.00 $1,500.00
    Read More
  • Company
    • FAQs
    • About Us
    • Contact
  • Events
  • Portfolio
  • Blogs
    • Blog – RayaFeeL
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
    • Blog – Medical Coding
      • Cart

        0

    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    RegisterLogin
    RayaFeeL
    • Home
    • Services
      • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
      • Website Maintenance
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum Database

      Greenplum Database

      $2,000.00 $1,500.00
      Read More
    • Company
      • FAQs
      • About Us
      • Contact
    • Events
    • Portfolio
    • Blogs
      • Blog – RayaFeeL
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
      • Blog – Medical Coding
        • Cart

          0

      Databases

      • Home
      • All courses
      • Databases
      • PostgreSQL Database
      CoursesIT & SoftwareDatabasesPostgreSQL Database
      • BASIC POSTGRESQL
        6
        • Lecture1.1
          PostgreSQL Brief History 30 min
        • Lecture1.2
          PostgreSQL Features 20 min
        • Lecture1.3
          PostgreSQL Monitoring Tools Overview 30 min
        • Lecture1.4
          PostgreSQL – Oracle Vs PostgreSQL 30 min
        • Lecture1.5
          PostgreSQL Data Types 30 min
        • Lecture1.6
          PostgreSQL Maximum Objects Size 30 min
      • VMWARE & POSTGRESQL INSTALLATION
        9
        • Lecture2.1
          Vmware Installation 30 min
        • Lecture2.2
          Creating Linux Virtual Machine 01 hour
        • Lecture2.3
          PostgreSQL Installation Types 30 min
        • Lecture2.4
          PostgreSQL GUI Installation 30 min
        • Lecture2.5
          PostgreSQL Text Mode Installation 30 min
        • Lecture2.6
          PostgreSQL Unattended Mode Installation 30 min
        • Lecture2.7
          Configure the network & Disk Partition 30 min
        • Lecture2.8
          How to install PostgreSQL10 On Linux Server ? 20 min
        • Lecture2.9
          PostgreSQL -11 Installation (rpm & source code) 30 min
      • POSTGRESQL DATABASE
        6
        • Lecture3.1
          Connect Postgres Server 10 min
        • Lecture3.2
          PostgreSQL startup / shutdown /restart the postgresql server 30 min
        • Lecture3.3
          PostgreSQL .bash_profile Set Up 30 min
        • Lecture3.4
          PostgreSQL Database Creation 30 min
        • Lecture3.5
          PostgreSQL Connect Database 30 min
        • Lecture3.6
          PostgreSQL Drop Database 30 min
      • POSTGRESQL TABLE
        16
        • Lecture4.1
          PostgreSQL Languages 30 min
        • Lecture4.2
          PostgreSQL Create Table 30 min
        • Lecture4.3
          PostgreSQL Select Table 30 min
        • Lecture4.4
          PostgreSQL Alter Table 30 min
        • Lecture4.5
          PostgreSQL Drop Table 30 min
        • Lecture4.6
          PostgreSQL Truncate Table 30 min
        • Lecture4.7
          PostgreSQL Rename 30 min
        • Lecture4.8
          PostgreSQL Comment 30 min
        • Lecture4.9
          PostgreSQL Insert 30 min
        • Lecture4.10
          PostgreSQL Update 30 min
        • Lecture4.11
          PostgreSQL Table Delete 30 min
        • Lecture4.12
          PostgreSQL Merge Table 30 min
        • Lecture4.13
          PostgreSQL UNION 30 min
        • Lecture4.14
          PostgreSQL UNION ALL 30 min
        • Lecture4.15
          PostgreSQL INTERSECT 30 min
        • Lecture4.16
          PostgreSQL DISTINCT 30 min
      • USER/OBJECTS PRIVILEGE AND ROLES ASIGNING
        3
        • Lecture5.1
          PostgreSQL Grant 30 min
        • Lecture5.2
          PostgreSQL Revoke 30 min
        • Lecture5.3
          Postgres user creation and restrict DDL statement & other database access 30 min
      • TRANSACTIONS - MVCC
        3
        • Lecture6.1
          PostgreSQL Commit 30 min
        • Lecture6.2
          PostgreSQL Rollback 30 min
        • Lecture6.3
          PostgreSQL Savepoint 30 min
      • POSTGRESQL USER/SCHEMA MANAGEMENT
        9
        • Lecture7.1
          PostgreSQL User Creation 30 min
        • Lecture7.2
          PostgreSQL User Creation Using Utility 30 min
        • Lecture7.3
          PostgreSQL Drop user 30 min
        • Lecture7.4
          PostgreSQL Drop User Using Utility 30 min
        • Lecture7.5
          PostgreSQL Password Changing and Views 30 min
        • Lecture7.6
          PostgreSQL Group/role 30 min
        • Lecture7.7
          Alter PostgreSQL User/Role/group 30 min
        • Lecture7.8
          PostgreSQL Schema 30 min
        • Lecture7.9
          PostgreSQL user creation and restrict DDL & database access 30 min
      • POSTGRESQL CONSTRAINTS
        6
        • Lecture8.1
          PostgreSQL Constraints 30 min
        • Lecture8.2
          PostgreSQL Primary Key 30 min
        • Lecture8.3
          PostgreSQL UNIQUE Constraint 30 min
        • Lecture8.4
          PostgreSQL CHECK Constraint 30 min
        • Lecture8.5
          PostgreSQL NOT NULL Constraint 30 min
        • Lecture8.6
          PostgreSQL Foreign Key 30 min
      • POSTGRESQL ADVANCE DATA TYPE
        5
        • Lecture9.1
          PostgreSQL DOMAIN Data Type 30 min
        • Lecture9.2
          PostgreSQL Alter Domain 30 min
        • Lecture9.3
          PostgreSQL Drop DOMAIN 30 min
        • Lecture9.4
          PostgreSQL Json Data Type 30 min
        • Lecture9.5
          PostgreSQL Sequence 30 min
      • POSTGRESQL VIEWS
        1
        • Lecture10.1
          How to Create PostgreSQL View 30 min
      • POSTGRESQL MONITORING OBJECT USUAGE/SIZE
        1
        • Lecture11.1
          How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database 30 min
      • POSTGRESQL DATABASE ARCHITECTURE
        4
        • Lecture12.1
          PostgreSQL Tablespace 30 min
        • Lecture12.2
          PostgreSQL UnderStanding Of Objects 30 min
        • Lecture12.3
          PostgreSQL Architecture 30 min
        • Lecture12.4
          PostgreSQL Query Flow 30 min
      • POSTGRESQL BACKUP AND RECOVERY
        13
        • Lecture13.1
          PostgreSQL Backup & Recovery Overview 30 min
        • Lecture13.2
          PostgreSQL Database Backup Tools – Pg_dump 30 min
        • Lecture13.3
          PostgreSQL Database Backup Tools – Pg_dumpall 30 min
        • Lecture13.4
          PostgreSQL Database Backup Tools – Pg_basebackup 30 min
        • Lecture13.5
          PostgreSQL COPY data From Text File 30 min
        • Lecture13.6
          PostgreSQL COPY data to Text File 30 min
        • Lecture13.7
          PostgreSQL Clone Table 20 min
        • Lecture13.8
          Postgres Database Schema Migration 30 min
        • Lecture13.9
          PostgreSQL Database clone/migration 30 min
        • Lecture13.10
          PostgreSQL Compress backup format 30 min
        • Lecture13.11
          PostgreSQL Archivelog 30 min
        • Lecture13.12
          PostgreSQL Point In Time Recovery 30 min
        • Lecture13.13
          Taking Backup On Postgres Slave (Standby) Server 30 min
      • POSTGRESQL PERFORMANCE TUNING
        5
        • Lecture14.1
          PostgreSQL Index 30 min
        • Lecture14.2
          PostgreSQL Reindex 30 min
        • Lecture14.3
          PostgreSQL PerformanceTuning 30 min
        • Lecture14.4
          Understanding Postgres VACUUM | VACUUM FULL | VACUUM ANALYZE 30 min
        • Lecture14.5
          Postgres Autovacuum Configuration/Setup 30 min
      • HIGH AVAILABILITY, LOAD BALANCING, AND REPLICATION
        11
        • Lecture15.1
          POSTGRESQL STREAMING REPLICATION 30 min
        • Lecture15.2
          SSL Streaming replication with PostgreSQL 10 30 min
        • Lecture15.3
          How to make Linux partition and configure the network on Redhat Linux7.3 30 min
        • Lecture15.4
          How To Fix Firewall Issues while connecting the other server? 30 min
        • Lecture15.5
          How to install the PostgreSQL10 On Linux ? 30 min
        • Lecture15.6
          How to Configure the cascade replication On PostgreSQL 10.3 ? 30 min
        • Lecture15.7
          How to add extra one slave an existing PostgreSQL cascade replication without down time ? 30 min
        • Lecture15.8
          PostgreSQL Switchover 30 min
        • Lecture15.9
          Postgres Failover 30 min
        • Lecture15.10
          Postgres Upgrade 30 min
        • Lecture15.11
          PostgreSQL Upgrade 9.5 to 11.3 . 30 min

        Alter PostgreSQL User/Role/group

        • ALTER ROLE changes the attributes of a PostgreSQL role.
        • If you want to change a user you can use the ALTER USER SQL command, which is similar to the CREATE USER command except you can’t change the sysid

        Syntax:

        ALTER USER role_specification [ WITH ] option [ ... ]
        
        where option can be:
        
              SUPERUSER | NOSUPERUSER
            | CREATEDB | NOCREATEDB
            | CREATEROLE | NOCREATEROLE
            | CREATEUSER | NOCREATEUSER
            | INHERIT | NOINHERIT
            | LOGIN | NOLOGIN
            | REPLICATION | NOREPLICATION
            | BYPASSRLS | NOBYPASSRLS
            | CONNECTION LIMIT connlimit
            | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
            | VALID UNTIL 'timestamp'
        
        ALTER USER name RENAME TO new_name
        
        ALTER USER role_specification SET configuration_parameter { TO | = } { value | DEFAULT }
        ALTER USER role_specification SET configuration_parameter FROM CURRENT
        ALTER USER role_specification RESET configuration_parameter
        ALTER USER role_specification RESET ALL
        
        where role_specification can be:
        
            [ GROUP ] role_name
          | CURRENT_USER
          | SESSION_USER

        Example:
        practical 1.Listing users

        • You can see the users on the server by selecting from the pg_shadow & pg_authid system table. If you are not a super user, you will not have permission to access this table and will have to access the pg_user view instead, which is identical, but displays the password as stars.
        db2=# select * from pg_shadow;                                                                                           
         usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |               passwd                | valuntil             | useconfig 
        ----------+----------+-------------+----------+-----------+---------+-------------------------------------+-----------------------------------+---
         postgres |       10 | t           | t        | t         | t       | md505ea766c2bc9e19f34b66114ace97598 |                      | 
         rep      |    24576 | f           | f        | f         | t       | md5df2c887bcb2c49b903aa33bdbc5c2984 |                      | 
         nijam    |    24586 | f           | f        | f         | f       |                                     |                      | 
         u8       |    24591 | t           | t        | t         | f       | md5b9f930ae0484417a1883fd3f7cdb490e |2015-05-09 12:00:00-04| 
         u9       |    24640 | f           | f        | f         | f       | md531f95351422eab63b8b270c140f60c2a |2015-05-04 12:00:00-04| 
         u1       |    24648 | f           | f        | f         | f       | md58026a39c502750413402a90d9d8bae3c |                      | 
         u2       |    24649 | f           | f        | f         | f       | md5a76d8c8015643c6a837661a10142016e |                      |
         u4       |    24657 | f           | f        | f         | f       | md54af10c3137cf79c12265e8d288070711 |                      | 
         u5       |    24658 | f           | f        | f         | f       | md507a832ae72c9e818c5297f366284fb8a |                      | 
         u3       |    24660 | f           | f        | f         | f       |                                     |                      | 
        (10 rows)
        db2=# select * from pg_user;
         usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  | valuntil             | useconfig 
        ----------+----------+-------------+----------+-----------+---------+----------+----------------------+-----------
         postgres |       10 | t           | t        | t         | t       | ******** |                      | 
         rep      |    24576 | f           | f        | f         | t       | ******** |                      | 
         nijam    |    24586 | f           | f        | f         | f       | ******** |                      | 
         u8       |    24591 | t           | t        | t         | f       | ******** |2015-05-09 12:00:00-04| 
         u9       |    24640 | f           | f        | f         | f       | ******** |2015-05-04 12:00:00-04| 
         u1       |    24648 | f           | f        | f         | f       | ******** |                      | 
         u2       |    24649 | f           | f        | f         | f       | ******** |                      | 
         u4       |    24657 | f           | f        | f         | f       | ******** |                      | 
         u5       |    24658 | f           | f        | f         | f       | ******** |                      | 
         u3       |    24660 | f           | f        | f         | f       | ******** |                      | 
        (10 rows)
        db2=# select * from pg_authid;
         rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit |             rolpassword             | rolvaliduntil 
        ----------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------------------------------+---------------
         postgres | t        | t          | t             | t           | t            | t           | t              |           -1 | md505ea766c2bc9e19f34b66114ace97598 | 
         rep      | f        | t          | f             | f           | f            | t           | t              |            1 | md5df2c887bcb2c49b903aa33bdbc5c2984 | 
         nijam    | f        | t          | f             | f           | f            | t           | f              |           -1 |                                     | 
         u8       | t        | t          | t             | t           | t            | t           | f              |           -1 | md5b9f930ae0484417a1883fd3f7cdb490e |2015-05-09 12:00:00-04
         u9       | f        | t          | f             | f           | f            | t           | f              |           -1 | md531f95351422eab63b8b270c140f60c2a |2015-05-04 12:00:00-04 
         u1       | f        | t          | f             | f           | f            | t           | f              |           -1 | md58026a39c502750413402a90d9d8bae3c | 
         u2       | f        | t          | f             | f           | f            | t           | f              |           -1 | md5a76d8c8015643c6a837661a10142016e | 
         u4       | f        | t          | f             | f           | f            | t           | f              |           -1 | md54af10c3137cf79c12265e8d288070711 | 
         u5       | f        | t          | f             | f           | f            | t           | f              |           -1 | md507a832ae72c9e818c5297f366284fb8a | 
         u3       | f        | t          | f             | f           | f            | t           | f              |           -1 |                                     | 
        (10 rows)
        –Changing a user password
        One of the most common reasons for wanting to alter a user is to change the user’s password
        db2=# alter user u2 with password 'u23'; 
        ALTER ROLE
        –Remove a role’s password
        db2=# ALTER ROLE u5 WITH PASSWORD NULL;
        ALTER ROLE
        –Change a password expiration date from  “May 4 12:00:00 2015” to “May 8 12:00:00 2018”
        db2=# ALTER ROLE u9 VALID UNTIL 'May 8 12:00:00 2018';
        ALTER ROLE
        –Make a password valid forever:
        db2=# ALTER ROLE u8 VALID UNTIL 'infinity';
        ALTER ROLE
        –Say we wanted to allow u1 user  to create databases and create role
        db2=# ALTER ROLE u1 CREATEROLE CREATEDB;    
        ALTER ROLE
        –Checking pg_shadow again, we can see the changes highligted in red color
        db2=# select * from pg_shadow;             
         usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |               passwd                |        valuntil        | useconfig 
        ----------+----------+-------------+----------+-----------+---------+-------------------------------------+------------------------+-----------
         postgres |       10 | t           | t        | t         | t       | md505ea766c2bc9e19f34b66114ace97598 |                        | 
         rep      |    24576 | f           | f        | f         | t       | md5df2c887bcb2c49b903aa33bdbc5c2984 |                        | 
         nijam    |    24586 | f           | f        | f         | f       |                                     |                        | 
         u4       |    24657 | f           | f        | f         | f       | md54af10c3137cf79c12265e8d288070711 |                        | 
         u1       |    24648 | t           | f        | f         | f       | md53ac33e1b7b89b332aef1b757828dc8eb |                        | 
         u2       |    24649 | f           | f        | f         | f       | md57a7c3259d1ebc74d9119be56686b591a |                        | 
         u3       |    24660 | f           | f        | f         | f       |                                     |                        | 
         u5       |    24658 | f           | f        | f         | f       |                                     |                        | 
         u9       |    24640 | f           | f        | f         | f       | md531f95351422eab63b8b270c140f60c2a | 2018-05-08 12:00:00-04 | 
         u8       |    24591 | t           | t        | t         | f       | md5b9f930ae0484417a1883fd3f7cdb490e | infinity               | 
        (10 rows)
        
        
        –Rename the user from nijam to nijamutheen
        db2=# \du                                
                                     List of roles
         Role name |                   Attributes                   | Member of 
        -----------+------------------------------------------------+-----------
         nijam     |                                                | {}
         postgres  | Superuser, Create role, Create DB, Replication | {}
         rep       | Replication                                   +| {}
                   | 1 connection                                   | 
         u1        | Create role, Create DB                         | {}
         u2        |                                                | {}
         u3        |                                                | {}
         u4        |                                                | {}
         u5        |                                                | {}
         u8        | Superuser, Create role, Create DB             +| {}
                   | Password valid until infinity                  | 
         u9        | Password valid until 2018-05-08 12:00:00-04    | {}
        db2=# alter user nijam rename to nijamutheen;
        ALTER ROLE
        db2=# \du
                                      List of roles
          Role name  |                   Attributes                   | Member of 
        -------------+------------------------------------------------+-----------
         nijamutheen |                                                | {}
         postgres    | Superuser, Create role, Create DB, Replication | {}
         rep         | Replication                                   +| {}
                     | 1 connection                                   | 
         u1          | Create role, Create DB                         | {}
         u2          |                                                | {}
         u3          |                                                | {}
         u4          |                                                | {}
         u5          |                                                | {}
         u8          | Superuser, Create role, Create DB             +| {}
                     | Password valid until infinity                  | 
         u9          | Password valid until 2018-05-08 12:00:00-04    | {}
        Prev PostgreSQL Group/role
        Next PostgreSQL Schema

        Leave A Reply Cancel reply

        You must be logged in to post a comment.

        Login with:

        Login with Google Login with Twitter Login with LinkedIn Login with Microsoft


        ADVERTISEMENT

        Latest Courses

        PostgreSQL Database

        PostgreSQL Database

        $600.00 $500.00
        Greenplum Database

        Greenplum Database

        $2,000.00 $1,500.00

        Preview Course

        Free

        Latest Posts

        PostgreSQL Patching version 9, 10,11
        10Oct2019
        Tools for PostgreSQL
        16Sep2019
        Postgres user creation and restrict DDL & database access
        13Sep2019

        Recent Forum Topics

        • Are you going to take your first ste
        • How to start working on an application?
        • please let me know pre requirements to increase work_mem
        • how to copy some data in one table to another table in postgres
        • postgres script for finding queries more than 1 hours ?

        2ndquadrant.in

        (+91) 8838953252

        ITsupport@rayafeel.com

        Company

        • About Us
        • Contact
        • Our Team
        • Blog

        COURSES

        • List Of Course
        • Become An Instructor
        • Events
        • Postgres Support Blog

        Support

        • DBA Support
        • Consultancy Services
        • Postgres Migration Blogs
        • Forum

        Recommend

        • Groups
        • Login
        • FAQs
        • SignUp

        IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

        • Privacy
        • Terms

        Become An Instructor?

        Join thousand of instructors and earn money hassle free!

        Get Started Now

        Login with:

        Login with Google Login with Twitter Login with LinkedIn Login with Microsoft

        Login with your site account

        Lost your password?

        Not a member yet? Register now

        Register a new account

        Are you a member? Login now

        Modal title

        Message modal