Posts

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 
Post a Comment
Read more

version in PostgreSQL

postgres=# select version(); version ------------------------------------------------------------------------------------------------------------------------ ----------  PostgreSQL 11.3 (Debian 11.3-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 2017051 6, 64-bit (1 row)
Post a Comment
Read more

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=>
Post a Comment
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                                    
Post a Comment
Read more

Starting and shuting down Postgres server

Here are simple steps on how to start or stop PostgreSQL server. ---------------------------------------------------- -- SHUTDOWN POSTGRES ---------------------------------------------------- /etc/rc.d/init.d/postgresql stop [root@OEL1 local]# su postgres bash-4.1$ pg_ctl stop pg_ctl: no database directory specified and environment variable PGDATA unset Try "pg_ctl --help" for more information. bash-4.1$ pwd /usr/local bash-4.1$ cd bash-4.1$ . .bash_profile [postgres@OEL1:~]$ pg_ctl stop waiting for server to shut down.... done server stopped [postgres@OEL1:~]$ ps -ef|grep postgres root      2772  2737  0 10:32 pts/1    00:00:00 su postgres postgres  2773  2772  0 10:32 pts/1    00:00:00 bash postgres  2810  2773  0 10:33 pts/1    00:00:00 ps -ef postgres  2811  2773  0 10:33 pts/1    00:00:00 grep postgres ---------------------------------------------------- -- STARTUP POSTGRES ---------------------------------------------------- /etc/rc.d/init.
Post a Comment
Read more