[LINUX] About the difference between PostgreSQL su and sudo


Now that I've become an engineer and have come into contact with Linux and PostgreSQL, I'll keep what I've learned as a record. The environment is CentOS 7.2. Use PostgreSQL.

postgres user and psql command

Use the psql command to work with the DB. The psql command is a command for operating the DB from the terminal. However, not all users can use this command. (It seems possible if you change the setting.) When operating the DB, switch to the postgres user (or switch to a user who can use the psql command) instead of the root user or general user, and execute the psqk command to operate the DB. The su and sudo commands are used when switching between users.

About the su command

The su command is substitute user, super user, switch user, etc. I looked it up, but I didn't know what it stands for. It means to switch users.

su - postgres

Switching to postgres user.

Regarding the presence or absence of hyphens

Note the presence or absence of "-". If there is a "-", the environment will be initialized as if you logged in directly. That is, the current directory will be the new user's home directory and all environment variables will be initialized. If there is no "-", the current environment is left as it is and only the user is switched. (Nakajima Nokazu "LinuC Textbook LinuC Level 1 Version 10.0 Compatible" p425)

It seems that.

su -

If you omit the user name, you will be switched to the root user and asked for a password.

su - postgres
#Switch user to postgres
psql test
#Access the test server with the psql command
#You can list the tables in the test server by using the \ mark (or backslash) d.
¥d table
#You can also list the columns in that table by entering the table name after \ d.

After switching to the postgres user with the su command, specify the target server with the psql command, You will be writing SQL statements such as SELECT. If you switch users, it will remain the same, so you can return to the original user with ctrl + d.

About the sudo command

In the case of the sudo command, you can write SQL statements in one line from sudo.

sudo -u postgres psql table -c "SELECT~";

Switch postgres user with u option from sudo command, specify DB called table from psql command, Since you type the command in succession, you can enter the SQL statement by enclosing it in c option and double quotation marks.

sudo -u postgres psql table -f /var/test.sql

Also, if the SQL statement is put together in a file, it can be executed by changing the c option to the f option. In the above case, the test.sql file under / var is being executed.

However, since the sudo command has to switch users each time and write SQL statements, It may be better to switch once with the su command when writing SQL statements many times.


Described the difference between su and sudo for switching to the postgres user. I think it's deeper, but I'll learn from now on.

Recommended Posts

About the difference between PostgreSQL su and sudo
About the difference between "==" and "is" in python
About the relationship between Git and GitHub
What is the difference between `pip` and` conda`?
What is the difference between Unix and Linux?
Can BERT tell the difference between "candy (candy)" and "candy (rain)"?
What is the difference between usleep, nanosleep and clock_nanosleep?
How to use argparse and the difference between optparse
Difference between process and job
Difference between "categorical_crossentropy" and "sparse_categorical_crossentropy"
Difference between regression and classification
Difference between MicroPython and CPython
Difference between ps a and ps -a
Difference between return and print-Python
What is the difference between a symbolic link and a hard link?
Understand the difference between cumulative assignment to variables and cumulative assignment to objects
The difference between foreground and background processes understood by the principle
Difference between Ruby and Python split
Difference between list () and [] in Python
Difference between SQLAlchemy filter () and filter_by ()
Difference between == and is in python
(Note) Difference between gateway and default gateway
Difference between Numpy randint and Random randint
Difference between sort and sorted (memorial)
Difference between python2 series and python3 series dict.keys ()
[Python] Difference between function and method
Difference between SQLAlchemy flush () and commit ()
Python --Difference between exec and eval
[Python] Difference between randrange () and randint ()
[Python] Difference between sorted and sorted (Colaboratory)
I investigated the behavior of the difference between hard links and symbolic links
Linux (for users and su / sudo commands)
[Xg boost] Difference between softmax and softprob
difference between statements (statements) and expressions (expressions) in Python
[Introduction to Python] What is the difference between a list and a tuple?
[Django ORM] Difference between values () and only ()
Difference between PHP and Python finally and exit
Difference between @classmethod and @staticmethod in Python
Difference between append and + = in Python list
Difference between nonlocal and global in Python
Difference between linear regression, Ridge regression and Lasso regression
[Python] Difference between class method and static method
Difference between docker-compose env_file and .env file
The subtle relationship between Gentoo and pip
[python] Difference between rand and randn output
speed difference between wsgi, Bottle and Flask
Difference between numpy.ndarray and list (dimension, size)
About _ and __
Difference between ls -l and cat command
Difference and compatibility verification between keras and tf.keras # 1
About the behavior of copy, deepcopy and numpy.copy
The answer of "1/2" is different between python2 and 3
[python] Difference between variables and self. Variables in class
Python hand play (interoperability between CSV and PostgreSQL)
About the * (asterisk) argument of python (and itertools.starmap)
Bayesian modeling-estimation of the difference between the two groups-
About the test
I had Spotify analyze the difference between the Beatles song and my own song and plotted it
About the queue
[Introduction to Infectious Disease Models] What is the difference between the April epidemic and this epidemic? .. .. ‼
Investigating the relationship between ice cream spending and temperature