Re: \describe*
> > >What I propose is in fact a server command, >which at least three of > >the other popular RDBMSs already have. > Well to actually implement it, it would probably be a client command, because that's what \d* are. We would most likely want them implemented the same, to avoid needless complexity. I think people are more ok with \describe (with the backslash), which seems like what you're suggesting anyway. I read Vik's "hard pass" as being on having DESCRIBE which looks like an SQL command but would actually be implemented on the client. This seems simpler at first but could cause deep confusion later. But \describe gives a hint that it's different with the \, so it might be fine. Overall I agree with your idea: the \d* commands are cryptic and a longhand form would help people learning. Best, Ryan
Best way to select a random row from a derived table
Hello hackers and postgressers, I am aware of 2 ways to select a random row from a table: 1) select * from table_name order by random() limit 1; -- terribly inefficient 2) select * from table_name tablesample system_rows(1) limit 1; -- only works on tables, not views or subqueries Is there an option that is reasonably efficient and can be used on views and subqueries? Thanks! Ryan
Re: Is it valid to have logical replication between 2 databases on the same postgres server?
Thanks all! I'll try creating the replication slot manually.
Is it valid to have logical replication between 2 databases on the same postgres server?
Hello hackers, I'm experimenting with Logical Replication. (https://www.postgresql.org/docs/10/static/logical-replication.html) What I'm trying to do may be impossible l but just wanted to ask here. I'm trying to do logical replication from one database to another within the same server. Ultimately I want to do it between servers but was just trying this for connection simplicity (same host etc). *Details:* I have a postgres server running (11devel) with a database, call it db1. I created a new database on the same server, call it db2. I also created a test table, call it table1: CREATE TABLE table1 (id serial primary key, name text, tags text[]); On both databases I ran: ALTER SYSTEM SET wal_level = logical; (not sure if that's redundant because it's the same server?) On db1 I ran: CREATE PUBLICATION test_pub FOR TABLE table1; On db2 I ran: CREATE SUBSCRIPTION test_sub CONNECTION 'host=127.0.0.1 dbname=db1 user=xx password=xx' PUBLICATION test_pub; It just hangs. I'm imagining that this is because WAL is at the server level, not the db level, and it's impossible for the same server to logically replicate to itself, even though it's 2 separate databases. Am I right that that's the problem? Could someone help me get pointed in the right direction? Thanks a lot! Best, Ryan
Re: Add default role 'pg_access_server_files'
Ok great. Thanks Michael and Stephen for the explanations.
Re: Add default role 'pg_access_server_files'
Just circling back on this.
I did have a question that came up about the behavior of the server as it is
without the patch. I logged into psql with my superuser "postgres":
postgres=# select pg_read_file('/Users/postgres/temp');
ERROR: absolute path not allowed
I had not tried this before with my unpatched build of postgres. (In
retrospect of course I should have). I expected my superuser to be able to
perform this task, but it seems that for security reasons we presently don't
allow access to absolute path names (except in the data dir and log dir) - not
even for a superuser. Is that correct? In that case the security implications
of this patch would need more consideration.
Stephen, looking at the patch, I see that in src/backend/utils/adt/genfile.c
you've made some changes to the function convert_and_check_filename(). These
changes, I believe, loosen the security checks in ways other than just adding
the granularity of a new role which can be GRANTed to non superusers:
+ /*
+* Members of the 'pg_access_server_files' role are allowed to access
any
+* files on the server as the PG user, so no need to do any further
checks
+* here.
+*/
+ if (is_member_of_role(GetUserId(), DEFAULT_ROLE_ACCESS_SERVER_FILES))
+ return filename;
+
+ /* User isn't a member of the default role, so check if it's allowable
*/
if (is_absolute_path(filename))
{
As you can see, you've added a short-circuit "return" statement for anyone who
has the DEFAULT_ROLE_ACCESS_SERVER_FILES. Prior to this patch, even a
Superuser would be subject to the following is_absolute_path() logic. But with
it, the return statement short-circuits the is_absolute_path() check.
Is this an intended behavior of the patch - to allow file access to absolute
paths where previously it was impossible? Or, was the intention just to add
granularity via the new role? I had assumed the latter.
Best regards,
Ryan
The new status of this patch is: Waiting on Author
Re: Add default role 'pg_access_server_files'
The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: tested, passed Spec compliant: not tested Documentation:tested, passed I ran make installcheck-world and all tests passed except one that is a known issue with the way I have my environment setup (ecpg tests unrelated to this patch). Manual tests I ran to see if it Implements the Feature: 1) confirmed that superuser can call pg_read_file() to read files in or out of data directory 2) confirmed that "tester" can call pg_read_file() only if given EXECUTE privilege 3) confirmed that "tester" can only call pg_read_file() on a file OUTSIDE of the data directory iff I "grant pg_access_server_files to tester;" Documentation seems reasonable. I believe this patch to be Ready for Committer. The new status of this patch is: Ready for Committer
Re: Add default role 'pg_access_server_files'
(Duplicated to make sure it's in the commitfest Thread, didn't seem to get in
there when I replied to the email)
Oops! I made a mistake, which clearly showed up in my last email: I forgot to
psql back in as "tester".
Now I get the right behavior:
$ psql postgres tester
postgres=> select pg_read_file('/Users/postgres/temp');
ERROR: absolute path not allowed
Thanks for bearing with me. So far so good on this feature, going to run the
tests too.
Best,
Ryan
Re: Add default role 'pg_access_server_files'
Oops! I made a mistake, which clearly showed up in my last email: I forgot
to psql back in as "tester".
Now I get the right behavior:
$ psql postgres tester
psql (9.4.5, server 11devel)
Type "help" for help.
postgres=> select pg_read_file('/Users/postgres/temp');
ERROR: absolute path not allowed
Thanks for bearing with me. So far so good on this feature, going to run
the tests too.
Best,
Ryan
Re: Add default role 'pg_access_server_files'
Hi Stephen,
I have another question then based on what you said earlier today, and some
testing I did using your patch.
TLDR: I created a role "tester" and was (as expected) not able to perform
pg_read_file() on files outside the data directory.
But then I granted EXECUTE on that function for that role, and then I was able
to (which is not what I expected).
Here's what I did (I apologize if this is too verbose):
* I successfully applied your patch to HEAD, and built Postgres from source:
make clean
configure (with options including a specific --prefix)
make
make install
* Then I went into the "install_dir/bin" and did the following to setup a data
directory:
$ ./initdb ~/sql-data/2018-01-06
The files belonging to this database system will be owned by user
"postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory /Users/postgres/sql-data/2018-01-06 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
./pg_ctl -D /Users/postgres/sql-data/2018-01-06 -l logfile start
* Then I started the database:
$ ./pg_ctl -D /Users/postgres/sql-data/2018-01-06 -l logfile start
waiting for server to start done
server started
* I went into the database and tried a pg_read_file:
$ psql postgres
psql (9.4.5, server 11devel)
Type "help" for help.
postgres=# select pg_read_file('/Users/postgres/temp');
pg_read_file
---
here is the file content
(1 row)
* Of course that worked as superuser, so created a new role:
postgres=# create role tester;
CREATE ROLE
postgres=# \q
postgres=# alter role tester with login;
ALTER ROLE
postgres=# \q
$ psql postgres tester
psql (9.4.5, server 11devel)
Type "help" for help.
postgres=> select pg_read_file('/Users/postgres/temp');
ERROR: permission denied for function pg_read_file
postgres=> \q
* My current understanding at this point is that EXECUTE permissions would only
allow "tester" to pg_read_file() on files in the data directory. I try
GRANTing EXECUTE:
$ psql postgres
psql (9.4.5, server 11devel)
Type "help" for help.
postgres=# grant execute on function pg_read_file(text) to tester;
GRANT
postgres=# select pg_read_file('/Users/postgres/temp');
pg_read_file
---
here is the file content
(1 row)
Is this expected behavior? I thought I would need to GRANT that new
"pg_access_server_files" role to "tester" in order to do this. I may have
misunderstood how your new feature works, just doublechecking.
Regards,
Ryan
Re: Add default role 'pg_access_server_files'
Stephen, so far I've read thru your patch and familiarized myself with some of
the auth functionality in pg_authid.h and src/backend/utils/adt/acl.c
The only question I have so far about your patch is the last several hunks of
the diff, which remove superuser checks without adding anything immediately
obvious in their place:
...
@@ -195,11 +205,6 @@ pg_read_file(PG_FUNCTION_ARGS)
char *filename;
text *result;
- if (!superuser())
- ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-(errmsg("must be superuser to read files";
-
/* handle optional arguments */
if (PG_NARGS() >= 3)
{
@@ -236,11 +241,6 @@ pg_read_binary_file(PG_FUNCTION_ARGS)
char *filename;
bytea *result;
- if (!superuser())
- ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-(errmsg("must be superuser to read files";
-
/* handle optional arguments */
if (PG_NARGS() >= 3)
{
@@ -313,11 +313,6 @@ pg_stat_file(PG_FUNCTION_ARGS)
TupleDesc tupdesc;
boolmissing_ok = false;
- if (!superuser())
- ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-(errmsg("must be superuser to get file information";
-
/* check the optional argument */
if (PG_NARGS() == 2)
missing_ok = PG_GETARG_BOOL(1);
...
I wanted to ask if you have reason to believe that these checks were not
necessary (and therefore can be deleted instead of replaced by
is_member_of_role() checks like you did elsewhere). I still have limited
understanding of the overall code, so really just asking because it's the first
thing that jumped out.
Best,
Ryan
Re: Challenges preventing us moving to 64 bit transaction id (XID)?
Thanks for this contribution! I think it's a hard but important problem to upgrade these xids. Unfortunately, I've confirmed that this patch 0001-64bit-guc-relopt-3.patch doesn't apply correctly on my computer. Here's what I did: I did a "git pull" to the current HEAD, which is 6271fceb8a4f07dafe9d67dcf7e849b319bb2647 Then I attempted to apply the patch, here's what I saw: $ git apply patches/0001-64bit-guc-relopt-3.patch error: src/backend/access/common/reloptions.c: already exists in working directory error: src/backend/utils/misc/guc.c: already exists in working directory error: src/include/access/reloptions.h: already exists in working directory error: src/include/utils/guc.h: already exists in working directory error: src/include/utils/guc_tables.h: already exists in working directory Alexander, what is the process you're using to create the patch? I've heard someone (maybe Tom Lane?) say that he sometimes uses "patch" directly instead of "git" to create the patch, with better results. I forget the exact command. For now I'm setting this to Waiting on Author, until we have a patch that applies via "git apply". Thanks! Ryan The new status of this patch is: Waiting on Author
Re: Challenges preventing us moving to 64 bit transaction id (XID)?
Since the Patch Tester (http://commitfest.cputube.org/) says this Patch will not apply correctly, I am tempted to change the status to Waiting on Author. However, I'm new to the CommitFest process, so I'm leaving the status as-is for now and asking Stephen Frost whether he agrees. I haven't tried to apply the patch myself yet, but happy to do so if e.g. we think the Patch Tester can't be taken on face value, or if I need to find specific feedback about why the patch didn't apply to help the Author. Best regards, Ryan
Re: January CommitFest is underway!
> BTW, for those who aren't already aware of it, I'd like to point out > this very handy resource: > > http://commitfest.cputube.org > > Wow, that makes it way easier to see what's going on! Thanks Tom.
Re: CFM for January commitfest?
> > Yes, I have interest to help. I do not have the experience of how to do > the > > whole, but I believe that if I help you (you as CFM) in the future I can > > assume this activity when necessary. Hi Stephen. Thanks for being the CFM! I'm also interested in helping. Last commitfest I reviewed several patches, but like Gerdan I lack context to know how best to help. I think I have an average an hour a day to devote to this, which is not a ton, but feel free to call on me to help in any way I can. Best, Ryan
Re: CFM for January commitfest?
> Now that the January fest has nominally started, we need somebody > to act as CF manager. Any volunteers? > > What are the responsibilities? How many hours per week are typically given for this role? Best, Ryan
Re: proposal: alternative psql commands quit and exit
> > There isn't going to be that much appetite for this just so that people > can use PostgreSQL without reading our documentation: or the output of > typing "help" at the psql prompt that says "type \q to quit". > > Agree with this. The whole reason people here are reluctant to add "quit" in the first place is that it could become a slippery slope where people now want every popular MySQL/Oracle command cloned, just so they can avoid reading docs and never understand the design/power/elegance of Postgres. For example, while I think "quit" is a good idea, it'd be a shame if it caused people to never find out about \ commands. > I'll agree that exiting the program is a special case that merits consideration Yes, I think we can find the right balance here. We don't need to suddenly start implementing SHOW TABLES, as attractive as that is to people who know MySQL.
Re: proposal: alternative psql commands quit and exit
I am +1 on doing this too, unless we can imagine it clashing with any SQL queries or breaking scripts (which I can't). Helping people migrate to postgres w minimal frustration is important enough imho to be worth breaking this \ rule unless we can forsee real actual compatibility problems. We already have "help", so it's not like we have literally 0 commands that don't start with \. Best, Ryan
Re: PGLister: how to subscribe to digests instead of getting every email?
Ok Stephen, thanks for the clarification. On Tue, Nov 14, 2017 at 2:27 PM, Stephen Frost wrote: > Greetings, > > * Ryan Murphy (ryanfmur...@gmail.com) wrote: > > Sorry, this is off topic, but since we've switched the mailing list to > > PGLister my inbox is getting every single message as a separate email. I > > used to get a digest once a day. Is there a way for me to change my > > subscription settings to something like that? > > As mentioned at https://wiki.postgresql.org/wiki/PGLister_Announce the > digest option is no longer supported and we don't currently have any > plans to add a digest option. > > Filtering messages into another mailbox/folder should be reasonably > straight-forward with most mail clients these days, to avoid having your > inbox end up with them all. > > Thanks! > > Stephen >
PGLister: how to subscribe to digests instead of getting every email?
Sorry, this is off topic, but since we've switched the mailing list to PGLister my inbox is getting every single message as a separate email. I used to get a digest once a day. Is there a way for me to change my subscription settings to something like that? Best, Ryan
