switching between users in postgres


SET SESSION AUTHORIZATION

SET [ SESSION | LOCAL ] SESSION AUTHORIZATION username
SET [ SESSION | LOCAL ] SESSION AUTHORIZATION DEFAULT
RESET SESSION AUTHORIZATION

 This command sets the session user identifier and the current user identifier of the current SQL session to be username. The user name may be written as either an identifier or a string literal. Using this command, it is possible, for example, to temporarily become an unprivileged user and later switch back to being a superuser.
Example:

 C:\WINDOWS\system32>psql -U postgres
psql (10.11)
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=# select current_user,session_user;
 current_user | session_user
--------------+--------------
 postgres     | postgres
(1 row)


postgres=# set session authorization 'ATEST';
SET

postgres=> select current_user,session_user;
 current_user | session_user
--------------+--------------
 ATEST        | ATEST
(1 row)

Comments

Post a Comment

Popular posts from this blog

Oracle Alter session like in postgres

If you are looking for looking for alter session set curent_schema=ABCD;  kind of statement in postgres we have  Example : dvdrental=# create user test password 'test'; CREATE ROLE                         If you want to inherit permissions of another role you can do : dvdrental=# alter role test  CREATEROLE CREATEDB; ALTER ROLE dvdrental=# SELECT SESSION_USER, CURRENT_USER;  session_user | current_user --------------+--------------  postgres     | postgres (1 row) If you want to change user to another user without reconnecting (like sudo in Linux) : dvdrental=# SET SESSION AUTHORIZATION 'test'; SET dvdrental=> SELECT SESSION_USER, CURRENT_USER;  session_user | current_user --------------+--------------  test         | test (1 row) dvdrental=>
Read more

Connecting to PostgresSQL database

After logging into postgres user, from cmdline type psql. bash-4.1$ psql could not change directory to "/var/lib/mysql/TESTDB" psql (8.4.18) Type "help" for help. Some basic cmds: postgres=# show databases;   -- This list databases postgres=# List databases      -- This list databases -- Listing database in postgres ##     \l will give you basic information of databases in instance postgres-# \l                                   List of databases    Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges  -----------+----------+----------+-------------+-------------+-----------------------  postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |  template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres                                                              : postgres=CTc/postgres  template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres                                    
Read more